Solved

Intersect Vs. Join query showing different results.

Posted on 2011-02-14
2
864 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

Independent Software Vendors: 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

Suggested Solutions

Title # Comments Views Activity
PL SQL Developer 7 69
SQL Workhours Count beetween Workhours 3 39
Error in creating a view. 8 32
Cannot connect to Oracle database, python not recognizing cx_Oracle 2 29
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

756 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