difference betwwn inner join and intersect in sql

  • 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.

  • 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/

  • 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/

  • 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] 😉

  • 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.

  • 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)

  • 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.

  • 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

  • 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

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

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