SQL Tips by Namwar Rizvi

August 15, 2007

Difference between INNER JOIN and INTERSECT

Suppose you have two tables and you want the distinct rows which are common in both the tables what will you do to get them? Most of us will answer that they will use INNER JOIN to get the common rows of two tables, right? Wrong!!!!!!
This is a big misconception that INNER JOIN will always return all the common rows between two tables. In reality, INNER JOIN treats two NULLS as two different values rather than a same value so if you are joining a row based on a NULLable column there is a chance that if both tables have NULLs in that column then INNER JOIN will ignore those rows because

Two NULLs are not same in TSQL

To correctly retrieve all common rows between two tables, SQL Server 2005 has introduced the INTERSECT keyword. INTERSECT treats two NULLs as a same value and it returns all rows which are common in both the tables.
Run the following example and you will see the result yourself:

–Create two table variables for testing
Declare @m_table1 table (id int, firstName varchar(50))
Declare @m_table2 table (id int, firstName varchar(50))

–Fill the tables with NULLs
Insert into @m_table1 values (1,NULL)
Insert into @m_table2 values (1,NULL)

—Getting all common rows by Inner Join
Select t1.*
from @m_table1 t1
inner join @m_table2 t2
On
t1.id=t2.id and t1.firstName=t2.firstName

—Getting all common rows by INTERSECT
Select * from @m_table1
INTERSECT
Select
* from @m_table2

Advertisements

9 Comments »

  1. explanation is very good,useful

    Thanks

    Comment by deepika — March 6, 2010 @ 6:29 AM | Reply

  2. Thanks.. its clear…

    Comment by Eby N David — October 12, 2010 @ 8:11 PM | Reply

  3. Thanks man! that was very helpful!
    Kind regards!

    Comment by Juan Manuel — August 8, 2012 @ 9:04 PM | Reply

  4. Thanks preety good and clear explanation

    Comment by Prajakta — April 9, 2013 @ 6:45 PM | Reply

  5. Without considering NULL difference, performance wise which one is better?

    Comment by Prajakta — April 9, 2013 @ 6:50 PM | Reply

  6. Great explanation, thanks!
    Since I read in http://blog.sqlauthority.com/2008/08/03/sql-server-2005-difference-between-intersect-and-inner-join-intersect-vs-inner-join/ that “INNER JOIN can simulate with INTERSECT when used with DISTINCT”, which you proved wrong, I wonder if you can give me another good insight: when we are dealing with tables without null values, and I use intersect OR distinct/inner join with all the columns, which one is more efficient?

    Comment by Wagner Cateb — June 5, 2013 @ 7:38 PM | Reply

  7. so when do we use inner join exactly?

    Comment by Ridhima — October 26, 2013 @ 1:37 PM | Reply

  8. Great Explanation

    Comment by mahendra — December 27, 2013 @ 9:33 AM | Reply

  9. so which one is good from performance point of view?

    Comment by mikumar — November 4, 2014 @ 9:52 AM | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: