Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
2.2k views
in Technique[技术] by (71.8m points)

sql - how to get the difference between 2 tables in 2 different schema's in Oracle?

I am using Oracle 11G.

I have a table called Tickets in the schema called 'tickets1" I have the same table structure in another schema called 'tickets2'

I want to find out

  1. What data is the same in both tables?
  2. What data is different?
CREATE TABLE tickets(
    ticket_number NUMBER NOT NULL,    
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL,
    PRIMARY KEY(ticket_number)
);

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

For same data, use INTERSECT

SELECT * FROM TICKETS1.TICKETS
INTERSECT
SELECT * FROM TICKETS2.TICKETS

For difference of data, use MINUS and UNION as follows

SELECT * FROM
(SELECT * FROM TICKETS1.TICKETS
MINUS
SELECT * FROM TICKETS2.TICKETS)
UNION
(SELECT * FROM TICKETS2.TICKETS
MINUS
SELECT * FROM TICKETS1.TICKETS)

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...