Solved

Intersect Vs. Join query showing different results.

Posted on 2011-02-14
2
866 Views
Last Modified: 2013-12-07
I have two tables table_a and table_b.

If i do a intersect query. it shows me say 100 records matched.
However, if i do a join it shows me 0 rows matched.

How is this possible. Since intersect and a join are the same thing?


select count(*)
from
(select table_a.col1,
      table_a.col2,
      table_a.col3,
      table_a.col4,
      table_a.col5
from table_a
INTERSECT
select table_b.col1,
      table_b.col2,
      table_b.col3,
      table_b.col4,
      table_b.col5
from table_b);
*******************
rows returned: 100
*******************


select table_a.*, table_b.*
from table_a, table_b
where table_a.col1 = table_b.col1
and  table_a.col2 = table_b.col2
and  table_a.col3 = table_b.col3
and  table_a.col4 = table_b.col4
and  table_a.col5 = table_b.col5

*******************
rows returned: 0
*******************
0
Comment
Question by:gram77
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 34887194
INTERSECT allows to match on NULL values, while JOIN does NOT match on NULL values.


see this script:
with data1 as ( select 1 a, null b from dual union all select 2, 2 from dual)
, data2 as ( select 1 a, null b from dual union all select 2, 2 from dual)
select * from data1
intersect 
select * from data2
;

with data1 as ( select 1 a, null b from dual union all select 2, 2 from dual)
, data2 as ( select 1 a, null b from dual union all select 2, 2 from dual)
select * from data1
join data2
  on data1.a = data2.a
 and data1.b = data2.b
;

Open in new window

0
 

Author Comment

by:gram77
ID: 34887206
angelll:
ic, thx a lot
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question