Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Intersect Vs. Join query showing different results.

Posted on 2011-02-14
2
Medium Priority
?
868 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
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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 …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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 syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

783 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