difference betwwn inner join and intersect in sql

Question

difference betwwn inner join and intersect in sql

sej2008

SSCarpal Tunnel

Points: 4495
More actions
March 25, 2013 at 2:56 am

#275543

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.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Andrew G SSChampion Points: 12809 More actions · Answer 1

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/

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 2

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.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

vinu512 SSCoach Points: 15729 More actions · Answer 3

Here is a good article with examples about the differences between Intersect and Inner Join.

difference-between-intersect-and-inner-join[/url]

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

santhoshkr2020 SSC Journeyman Points: 89 More actions · Answer 4

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.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

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

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

johnwalker10 SSCrazy Eights Points: 9124 More actions · Answer 6

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.

Alan Burstein SSC Guru Points: 61136 More actions · Answer 7

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.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Luis Cazares SSC Guru Points: 183698 More actions · Answer 8

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.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2