SQL Server Performance

Join question

Discussion in 'General Developer Questions' started by Jaybee from his castle, Nov 6, 2006.

  1. Hi all,

    What join can I use to retrieve rows from table A that do NOT exist in table B?

    Furthermore, the tables are on two separate (SQL) servers - I presume I'd have to add server A as a linked server in Server B?

    Thanks for your help,



    Jaybee.


    France.

    The finest wines.

    The tastiest cuisine.

    The most delicious women.
  2. Adriaan New Member

    You need an outer join, with IS NULL criteria for the outer column:

    SELECT A.*
    FROM A LEFT JOIN B ON A.key = B.key
    WHERE B.key IS NULL

    You can also use NOT EXISTS:

    SELECT A.*
    FROM A
    WHERE NOT EXISTS
    (SELECT B.* FROM B WHERE B.key = A.key)

    You only have to add the other server if it is not already registered.
    You can also use OPENDATASOURCE ad-hoc, instead of a registered server.
  3. ranjitjain New Member

    Hi,
    You can use select column1 from table1 where column1 NOT IN(select column1 from table2)

    Else you can use LEFT JOIN
    which will return all matching rows and non-mathing from left table1

    select t1.column1 from table1 t1 LEFT JOIN table2 t2 ON
    t1.column1=t2.column1

    Also,
    You need to add other server as link server using sp_addlinkedserver
  4. That's cool stuff there guys, but what's the syntax if I need to run comparisons on multiple columns in multiple tables?

    Secondly, I may need to insert the unmatched rows into the database, any idea how to use the query results as input for the insert?


    Thanks,



    Jaybee.





    France.

    The finest wines.

    The tastiest cuisine.

    The most delicious women.
  5. FrankKalis Moderator

    Combine the NOT EXISTS with the INSERT INTO...SELECT ...
    What exactly do you mean with "run comparisons on multiple columns in multiple tables"?

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  6. Roji. P. Thomas New Member

  7. Adriaan New Member

    Not sure why you're bringing up EXCEPT here - AFAIK, there's still no UPSERT operation in SQL 2005 so you still have to do an UPDATE and an INSERT.

    For the record, I find the LEFT JOIN with IS NULL a perfectly clear bit of syntax. If you need to support NULL in the lookup column, then the EXISTS syntax works perfectly fine.
  8. Roji. P. Thomas New Member

    I dont know what is the significance of UPSERT here!

    The natural relational operator for returning the rows that are in the first set, but not in the second is EXCEPT aka MINUS.

    Roji. P. Thomas
    http://toponewithties.blogspot.com
  9. Adriaan New Member

    Roji, Jaybee's second posting here asks about combining updates and insertions. I was a little confused why you brought up the SQL 2005 syntax ...

    In response to Jaybee's question of comparing multiple columns:

    In the LEFT JOIN syntax, you just include all columns that you need to compare in the ON clause. In the WHERE clause, you put one of those columns, which cannot be nullable, with the IS NULL criteria.

    In the NOT EXISTS syntax, you add all columns for the comparison to the WHERE clause of the subquery.
  10. Roji. P. Thomas New Member

    OK. Sorry for the confusion.

    Anyways, the "UPSERT" effect can be achieved by mixing INTERSECT and EXCEPT, as in

    UPDATE Destination FROM (Source INTERSECT Destination)

    INSERT INTO Destination FROM (Source EXCEPT Destination)



    Roji. P. Thomas
    http://toponewithties.blogspot.com
  11. Adriaan New Member

    In SQL 2005, is there any performance benefit from using INTERSECT and EXCEPT, compared to an outer join or EXISTS?
  12. Roji. P. Thomas New Member

  13. Adriaan New Member

    Then why did they add INTERSECT and EXCEPT to T-SQL?[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  14. FrankKalis Moderator

    Hm, to increase compliance with the standard and the basic relational operations? [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  15. Adriaan New Member

    Well, if it's such a standard and basic relational operation, then why is this the first I've heard of it on S-S-P.com?[<img src='/community/emoticons/emotion-4.gif' alt=':p' />]
  16. Roji. P. Thomas New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />Then why did they add INTERSECT and EXCEPT to T-SQL?[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />May be because everybody else has it [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />Anyways, regardless of the performance implications, it makes the syntax more easier to write and understand.<br /><br />Roji. P. Thomas<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />
  17. FrankKalis Moderator

    It is indeed a basic relational operation. Codd himself wrote about them in 1972 in his paper ""Relational Completeness of Data Base Sublanguages" (unless I am not completely mistaken [<img src='/community/emoticons/emotion-1.gif' alt=':)' />])<br /><br />Now this is a braindump now, but IIRC he defined the following operations:<br /><br />- Projection <br />- Union, intersection, and difference (that's what we've got in question here)<br />- Cartesian product <br />- O-join and natural join <br />- Cartesian product <br />- Division <br />- Projection <br />- O-restriction <br />- Factoring <br /><br /><br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  18. Roji. P. Thomas New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />It is indeed a basic relational operation. Codd himself wrote about them in 1972 in his paper ""Relational Completeness of Data Base Sublanguages" (unless I am not completely mistaken [<img src='/community/emoticons/emotion-1.gif' alt=':)' />])<br /><br />Now this is a braindump now, but IIRC he defined the following operations:<br /><br />- Projection <br />- Union, intersection, and difference (that's what we've got in question here)<br />- Cartesian product <br />- O-join and natural join <br />- Cartesian product <br />- Division <br />- Projection <br />- O-restriction <br />- Factoring <br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />I havent read Codd. But I guess only the following operations are considered as relational operators. <br /><br />1. UNION <br />2. INTERSECT <br />3. DIFFERENCE (EXCEPT, MINUS)<br />4. DIVIDE <br />5. PRODUCT <br />6. SELECT <br />7. PROJECT <br />8. JOIN <br /><br /><br />Roji. P. Thomas<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />
  19. FrankKalis Moderator

    Well, I guess these terms are more familiar, Probably I got this O-thingy from something written by C. J. Date. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Check out this interesting paper:<br /<a target="_blank" href=http://www.informatik.uni-bonn.de/III/lehre/vorlesungen/Informationssysteme/WS06/materialien/Codd72a.pdf>http://www.informatik.uni-bonn.de/III/lehre/vorlesungen/Informationssysteme/WS06/materialien/Codd72a.pdf</a> Chapter 2.3<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  20. Roji. P. Thomas New Member

  21. FrankKalis Moderator

    Allow me to let C. J. Date speak for me? [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /<a target="_blank" href=http://www.intelligententerprise.com/db_area/archives/1999/990501/online.jhtml>http://www.intelligententerprise.com/db_area/archives/1999/990501/online.jhtml</a><br /><br />Finally found the URL again where I eventually copied the relational operations I've mentioned to my Outlook notices.<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  22. Roji. P. Thomas New Member

    Thanks Frank.<br /><br />EDIT : You guys force me to learn [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Roji. P. Thomas<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />

Share This Page