Home » SQL & PL/SQL » SQL & PL/SQL » What is the difference between MINUS and LEFT outer join (Oracle 10g, Windows)
What is the difference between MINUS and LEFT outer join [message #562074] Fri, 27 July 2012 15:31 Go to next message
nathb
Messages: 77
Registered: March 2011
Member
Howdy my good folks,

What is the fundamental difference between MINUS keyword and LEFT outer join in Oracle.

I am confused because, I can achive same results using either one of them.

Please help.
thanks
nath
Re: What is the difference between MINUS and LEFT outer join [message #562075 is a reply to message #562074] Fri, 27 July 2012 15:37 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

>I can achive same results using either one of them.
please post reproducible test case that shows above is true.
Re: What is the difference between MINUS and LEFT outer join [message #562079 is a reply to message #562075] Fri, 27 July 2012 15:51 Go to previous messageGo to next message
nathb
Messages: 77
Registered: March 2011
Member
I tried googling..but no good explanation. So I thought I will ask here. But if you think this is not a valid question, please delete it.
Re: What is the difference between MINUS and LEFT outer join [message #562081 is a reply to message #562079] Fri, 27 July 2012 16:09 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
If you give examples of your code, perhaps it can be explained. There are often several equivalent SQL statements.
Re: What is the difference between MINUS and LEFT outer join [message #562082 is a reply to message #562081] Fri, 27 July 2012 16:17 Go to previous messageGo to next message
nathb
Messages: 77
Registered: March 2011
Member
Using MINUS:

select col1 from Table1 where as_of_Date = '31-dec-2010'
minus
select col1 Table2

Using outer join:

select table1.col1, table2,col2
from qtable1, table2
where table1.col1 = table2.col1(+)
and table1.as_of_date = '31-DEC-2010'
and table2.deal_number is null
Re: What is the difference between MINUS and LEFT outer join [message #562084 is a reply to message #562082] Fri, 27 July 2012 16:37 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Those two selects aren't at all equivalent.
Not even a little bit.
Is table2 empty by any chance?
Re: What is the difference between MINUS and LEFT outer join [message #562085 is a reply to message #562084] Fri, 27 July 2012 16:40 Go to previous messageGo to next message
nathb
Messages: 77
Registered: March 2011
Member
so the minus in the first query will check only on column 'Col1' in the table1 and table2?
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 Go to previous messageGo to next message
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 #562089 is a reply to message #562087] Fri, 27 July 2012 17:39 Go to previous messageGo to next message
nathb
Messages: 77
Registered: March 2011
Member
Thank you so much for your explanation.

Appreciate greatly.
Re: What is the difference between MINUS and LEFT outer join [message #687749 is a reply to message #562087] Tue, 23 May 2023 13:51 Go to previous messageGo to next message
Kartik10
Messages: 1
Registered: May 2023
Junior Member
Nice Explanation.
So what I understood is join will try to find intersection considering only one particular column on which join is applied.
Whereas minus will do the same considering all the columns, present in select statement.

Please correct me if I am wrong.
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 Go to previous message
Solomon Yakobson
Messages: 3269
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>
While:

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>
SY.
Previous Topic: check all the distinct value match
Next Topic: Query to split date count into multiple rows and level that over weeks .
Goto Forum:
  


Current Time: Thu Mar 28 19:12:03 CDT 2024