SQL Server Performance

Which query is best

Discussion in 'General Developer Questions' started by rajeev_id, Apr 22, 2004.

  1. rajeev_id New Member

    use pubs

    --1)
    SELECT a.au_fname,a.au_lname,a.city FROM authors AS a
    JOIN publishers AS p
    ON a.city =p.city

    --2)
    SELECT au_fname,au_lname,city FROM authors
    where city in(select city from publishers)

    --Both the query return same output but which one is best and why?

    Rajeev Kumar Srivastava
    --ALWAYS BE POSITIVE!--
  2. Luis Martin Moderator

    I'm not developer, but see execution plan for both and you will find witch is better deppending on index in each table

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  3. gaurav_bindlish New Member

    Using Exists may also be considered.
    SELECT a.au_fname,a.au_lname,a.city
    FROM authors a
    WHERE EXISTS(SELECT * FROM publishers p WHERE p.city=a.city)
    However for this case I would suggest using JOIN.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

Share This Page