March 25, 2013 at 2:56 am
I would like to know when to use inner join/outer join and intersect/except as
they probably are doing the same job if not mistaken.
what is difference while working with joins and using these keyword intersect/except.
March 25, 2013 at 3:39 am
They are not the same but you can get the same result.
Intersect does all columns, inner join only the specified cols.http://blog.sqlauthority.com/2008/08/03/sql-server-2005-difference-between-intersect-and-inner-join-intersect-vs-inner-join/
March 25, 2013 at 3:40 am
No. They are not doing the same job. They are for entirely different purposes.
Try looking up in Google or Books Online. You will easily get information on these topics.
If you still have any doubts after reading the articles, you can revert back and we will be happy to assist.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 3, 2013 at 4:17 am
August 19, 2016 at 9:40 am
One of the main difference I noticed is ,Using INTERSECT we need to have same column order for the tables to be compared. Result set provides distinct records whereas INNERJOIN spits out records matching in both the tables. We can make INNERJOIN to provide same result set like INTERSECT by using DISTINCT.
August 19, 2016 at 10:03 am
There's another huge difference than many overlook. All such operators (Intersect, Except, Union, etc) use NULL = NULL regardless of any server setting to the contrary for "normal" criteria. EXCEPT is great for comparing full snapshots of data from files at the column level.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 25, 2016 at 12:26 am
Inner join:
1. It is a join statement
2. It is used to display columns from all the tables involved based on the common columns present in them which matches a certain condition.
3. Any of the columns among the tables can be used in select clause
Intersect:
1. It is a not a join statement
2. It displays the values in the all columns included which are common in all the statements used in an intersect statement.
3. It should include same number of columns with corresponding data type in all the statements.
August 25, 2016 at 5:22 am
Joins are one of the four table operators in MS SQL Server (the others are APPLY, PIVOT and UNPIVOT). Joins are used for joining tables (real or derived).
INTERSECT, EXCEPT, UNION and UNION ALL are set operators and are used to do something to two or more sets of data.
Two important things to understand are:
1. Table operators are processed before set operators
2. With the exception of UNION ALL, set operators remove duplicates.
This second point is very important because if you work with me and do this:
SELECT DISTINCT col1 FROM table1
(UNION | EXCEPT | INTERSECT)
SELECT DISTINCT col1 FROM table2
I get upset and shoot you with a pork chop gun.
-- Itzik Ben-Gan 2001
August 25, 2016 at 6:48 am
An easy way to understand it, is that JOINs add columns while UNION/EXCEPT/INTERSECT only handle rows.
Of course, if you see a Venn diagram, they'll be the same. The difference come on how they handle things.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply