Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Advantages of INTERSECT over INNER JOIN Expand / Collapse
Author
Message
Posted Monday, January 13, 2014 9:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 4, 2014 2:07 PM
Points: 105, Visits: 496
These two T-SQL statements return the same results.

If Microsoft deemed it necessary to add the EXCEPT command, then what are its advantages over an INNER JOIN


-- LIST ONLY PRODUCTS THAT ARE ON A WORK ORDER

USE AdventureWorks2008R2;
GO
SELECT ProductID
FROM Production.Product
INTERSECT
SELECT ProductID
FROM Production.WorkOrder ;

USE AdventureWorks2008R2;
GO
SELECT DISTINCT Production.WorkOrder.ProductID
FROM Production.Product
INNER JOIN Production.WorkOrder ON Production.WorkOrder.ProductID = Production.Product.ProductID
Post #1530354
Posted Monday, January 13, 2014 9:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:30 AM
Points: 12,939, Visits: 12,351
j-1064772 (1/13/2014)
These two T-SQL statements return the same results.

If Microsoft deemed it necessary to add the EXCEPT command, then what are its advantages over an INNER JOIN


-- LIST ONLY PRODUCTS THAT ARE ON A WORK ORDER

USE AdventureWorks2008R2;
GO
SELECT ProductID
FROM Production.Product
INTERSECT
SELECT ProductID
FROM Production.WorkOrder ;

USE AdventureWorks2008R2;
GO
SELECT DISTINCT Production.WorkOrder.ProductID
FROM Production.Product
INNER JOIN Production.WorkOrder ON Production.WorkOrder.ProductID = Production.Product.ProductID


INTERSECT and INNER JOIN are similar but NOT the same. In your actual question you said EXCEPT instead of INTERSECT which is what I assume you meant.

Here is a decent explanation.

http://blog.sqlauthority.com/2008/08/03/sql-server-2005-difference-between-intersect-and-inner-join-intersect-vs-inner-join/


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1530357
Posted Monday, January 13, 2014 9:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 4, 2014 2:07 PM
Points: 105, Visits: 496
You are right, I really meant INTERSECT only.

EXCEPT was a mistake. (Also, SELECT ProductID FROM Product WHERE NOT ProductID IN (SELECT ProductID FROM WorkOrder) also does yield the same results as EXCEPT).

Thanks for you suggested link.

Regards
Post #1530361
Posted Monday, January 13, 2014 10:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:30 AM
Points: 12,939, Visits: 12,351
j-1064772 (1/13/2014)
You are right, I really meant INTERSECT only.

EXCEPT was a mistake. (Also, SELECT ProductID FROM Product WHERE NOT ProductID IN (SELECT ProductID FROM WorkOrder) also does yield the same results as EXCEPT).

Thanks for you suggested link.

Regards


I find it easier to read using EXCEPT. Also, using NOT IN will return an empty result set if the subquery contains a NULL.

Try these two and see what I mean.

SELECT ProductID 
FROM Product
WHERE ProductID NOT IN
(
SELECT ProductID FROM WorkOrder
UNION ALL
SELECT NULL
)

SELECT ProductID
FROM Product
EXCEPT
(
SELECT ProductID FROM WorkOrder
UNION ALL
SELECT NULL
)


The differences are subtle but very important to understand.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1530372
Posted Monday, January 13, 2014 10:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 4, 2014 2:07 PM
Points: 105, Visits: 496
Now THIS was the exact answer I was looking for.

The article you referred to did not really go much beyond what I started with.

Thanks a million for drawing my attention to the issue of a NULL - I had not seen it coming.

Super !
Post #1530375
Posted Monday, January 13, 2014 10:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:30 AM
Points: 12,939, Visits: 12,351
You are quite welcome. That my explanation provided some insight.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1530383
Posted Monday, January 13, 2014 11:21 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, September 11, 2014 8:23 AM
Points: 625, Visits: 2,128
If Microsoft deemed it necessary to add the INTERSECT command, then what are its advantages over an INNER JOIN



Imagine that instead of selecting one column from each table you are selecting twenty. You CAN achieve similar results by joining on ALL the columns, but that would be a longer, harder to read query. Even not considering the NULL issue Sean described, intersect can be far more convenient and make it much clearer what the query is trying to achieve.
Post #1530394
Posted Monday, January 13, 2014 11:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 4, 2014 2:07 PM
Points: 105, Visits: 496
Thank you Nevyn
Post #1530396
Posted Monday, January 13, 2014 5:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 35,218, Visits: 31,677
j-1064772 (1/13/2014)
Now THIS was the exact answer I was looking for.

The article you referred to did not really go much beyond what I started with.

Thanks a million for drawing my attention to the issue of a NULL - I had not seen it coming.

Super !


INTERSECT and EXCEPT (and, IIRC, UNION and UNION ALL) all treat NULLs as NULL = NULL, as well.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1530506
Posted Tuesday, January 14, 2014 9:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 4, 2014 2:07 PM
Points: 105, Visits: 496
Thank you all for your inputs, I now understand what INTERSECT and EXCEPT bring to the party.

I am however still trying to understand why the following occurs:

I checked that NOT IN fails (no record returned because of the extra null) as opposed to IN which does work.

WHERE ProductID IN (1, 2, 3, 4, NULL) translates as
WHERE ProductID = 1 OR ProductID = 2 OR ProductID = 3 OR ProductID = 4 OR ProductID = NULL

WHERE ProductID NOT IN (1, 2, 3, 4, NULL) translates as
WHERE NOT (ProductID = 1 OR ProductID = 2 OR ProductID = 3 OR ProductID = 4 OR ProductID = NULL)

Using Boolean algebra the last expression should yield the same results
WHERE (Product != 1) AND (Product != 2) AND (Product != 3) AND (Product != 4) AND (ProductID != NULL)

The last part AND (ProductID != NULL) would explain why no record would be returned since
<anything> = NULL always returns false.

Assuming of course that this is indeed the way SQL Server evaluates logical expressions.

If not, then I am still at a loss as to why the
NOT IN fails.

By the way, I enjoyed the crystal-clear way to include a null in a results set without having to actually put one in the table as done by Mr. Lange.





Post #1530749
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse