|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: What is the difference between MINUS and LEFT outer join [message #562087 is a reply to message #562085] |
Fri, 27 July 2012 17:05   |
 |
matthewmorris68
Messages: 129
Registered: May 2012
Location: Orlando, FL
|
Senior Member |
|
|
As John indicated, it is possible to have two SQL statements that will return the same data. That doesn't mean they are doing the same thing or that they will return the same data given slightly different conditions. I set up an example case:
CREATE TABLE table_A (
col1 NUMBER,
col2 VARCHAR2(1)
);
CREATE TABLE table_B (
col1 NUMBER,
col2 VARCHAR2(1)
);
INSERT INTO table_A VALUES (1, 'a');
INSERT INTO table_A VALUES (2, 'b');
INSERT INTO table_A VALUES (3, 'c');
INSERT INTO table_B VALUES (2, 'B');
INSERT INTO table_B VALUES (3, 'C');
INSERT INTO table_B VALUES (4, 'D');
If you perform a LEFT JOIN on table A and B by column 1, you'll get the following results:
SELECT a.col1, a.col2
FROM table_A a
LEFT JOIN table_B b
ON a.col1 = b.col1;
COL1 COL2
---- ----
2 b
3 c
1 a
If you perform a MINUS operation of col1 and col2 (A minus B), you'll get the same results (albeit a different order since I didn't use ORDER BY in both queries):
SELECT a.col1, a.col2
FROM table_A a
MINUS
SELECT b.col1, b.col2
FROM table_B b
COL1 COL2
---- ----
1 a
2 b
3 c
The reason the same results are being returned are for entirely different reasons. Basically all three rows from table_A are being returned in both cases. In the first, the LEFT join specifically indicates return all rows that exist in A, regardless of whether they exist in B. In the second query, the MINUS operation only removes rows where the data in B exactly matches the data in A. Since that never happens, all rows from A get returned.
If we swap the query to be Table_B minus Table_A, then the results are completely different:
SELECT b.col1, b.col2
FROM table_B b
MINUS
SELECT a.col1, a.col2
FROM table_A a;
COL1 COL2
---- ----
2 B
3 C
4 D
Alternately, if I were to insert a new row in B that matched a row in A and then re-ran the first two queries, they would produce different results:
INSERT INTO table_B VALUES (1, 'a');
SELECT a.col1, a.col2
FROM table_A a
LEFT JOIN table_B b
ON a.col1 = b.col1;
COL1 COL2
---- ----
2 b
3 c
1 a
SELECT a.col1, a.col2
FROM table_A a
MINUS
SELECT b.col1, b.col2
FROM table_B b
COL1 COL2
---- ----
2 b
3 c
|
|
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
