|
|
|
|
|
|
|
|
|
|
|
|
|
|
| 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: 258
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
|
|
|
|
|
|
|
|
| Re: What is the difference between MINUS and LEFT outer join [message #687756 is a reply to message #687749] |
Wed, 24 May 2023 08:06 |
Solomon Yakobson
Messages: 3228
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
MINUS and LEFT JOIN aren't equivalent even if table has just one column:
with t1 as (select null col1 from dual),
t2 as (select null col1 from dual)
select * from t1
minus
select * from t2
/
no rows selected
SQL>
with t1 as (select null col1 from dual),
t2 as (select null col1 from dual)
select *
from t1
left join
t2
on t2.col1 = t1.col1
where t2.col1 is null
/
COL1 COL1
---- ----
SQL>
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
