Professional Web Applications Themes

Better way to do this query? - Microsoft SQL / MS SQL Server

Is it better to do: SELECT * FROM TableA WHERE KeyField IN ( SELECT KeyField FROM TableB WHERE ConditionalField = ConditionalValue) or SELECT TableA.* FROM TableA INNER JOIN TableB ON (TableA.KeyField = TableB.KeyField AND TableB.ConditionalField = ConditionalValue) And with a related subject, is there a better alternative to this query: SELECT * FROM TableA WHERE KeyFieldA IN ( SELECT KeyField FROM TableB WHERE ConditionalField = ConditionalValue) OR KeyFieldB IN ( SELECT KeyField FROM TableB WHERE ConditionalField = ConditionalValue)...

  1. #1

    Default Better way to do this query?

    Is it better to do:

    SELECT *
    FROM TableA
    WHERE KeyField IN (
    SELECT KeyField FROM TableB
    WHERE ConditionalField = ConditionalValue)

    or

    SELECT TableA.*
    FROM TableA
    INNER JOIN TableB
    ON (TableA.KeyField = TableB.KeyField
    AND TableB.ConditionalField = ConditionalValue)

    And with a related subject, is there a better alternative to this query:

    SELECT *
    FROM TableA
    WHERE KeyFieldA IN (
    SELECT KeyField FROM TableB
    WHERE ConditionalField = ConditionalValue)
    OR KeyFieldB IN (
    SELECT KeyField FROM TableB
    WHERE ConditionalField = ConditionalValue)



    Ian Guest

  2. #2

    Default Re: Better way to do this query?

    i was hoping to get a general statement about which might be better.

    For example, it would be better to join to a derived table, than use a
    correlated sub-query.

    Can a similar statement be made for this query in it's two forms?


    "praveen" <stph.net> wrote in message
    news:phx.gbl... 
    >
    >[/ref]


    Ian Guest

  3. #3

    Default Re: Better way to do this query?


    It depends on data size and index structure. You should run both in Query
    yzer and see it by yourself - and don't forget that data size can
    increase in future !

     


    Alex Guest

  4. #4

    Default Re: Better way to do this query?

    Its always better to join than using subquery as shown.Without knowing the
    table definition , sample data here is one guess that can be made

    This is an alternate query

    select a.*
    from TableA a
    Join TableB b on (
    (
    a.KeyFieldA = b.KeyField
    and
    b.ConditionalField = ConditionalValue
    )

    or

    (
    a.KeyFieldB = b.KeyField
    and
    b.ConditionalField = ConditionalValue
    )

    )

    instead of

    SELECT *
    FROM TableA
    WHERE KeyFieldA IN (
    SELECT KeyField FROM TableB
    WHERE ConditionalField = ConditionalValue)
    OR KeyFieldB IN (
    SELECT KeyField FROM TableB
    WHERE ConditionalField = ConditionalValue)

    HTH
    Srinivas Sampangi

    "Ian Boyd" <com> wrote in message
    news:eLmTy$phx.gbl... 


    sampangi Guest

  5. #5

    Default Re: Better way to do this query?

    I'm not so sure that your query will go better than first one for any tables
    with any record count... Did you made research on it ?

    "Join is always better than subquery" is not a total truth for me too, sorry
     [/ref]


    Alex Guest

  6. #6

    Default Re: Better way to do this query?

    Sorry without DDL ,Sample data its difficult to research on this.It may not
    be true for a case that you are considering but most of the cases that I
    have seen using the Join was much better than using Select with an In
    clause..In any case table definitions , sample data would give us correct
    picture..

    HTH,
    Srinivas Sampangi
    "Alex Cieszinski" <com> wrote in message
    news:phx.gbl... 
    tables 
    sorry [/ref]
    the [/ref][/ref]
    query: [/ref]
    >
    >[/ref]


    sampangi Guest

  7. #7

    Default Re: Better way to do this query?

    "sampangi" <com> wrote in message
    news:phx.gbl... 

    i thought so.
     
     

    Thank you. i wasn't looking for a full investigation, carefully yzing
    indexes, DDL, selectivity, row size.

    i wanted an overall, sweeping statement.

    A join is better than a correlated sub-query. If someone wants to argue this
    point, then i don't value their opinion on this matter.


    Ian Guest

  8. #8

    Default Re: Better way to do this query?


    "Gert-Jan Strik" <nl> wrote in message
    news:nl... 

    Very concise. Thank you
     


    Ian Guest

  9. #9

    Default Re: Better way to do this query?

    Bob Barrows wrote: 
    >>
    >> Very concise. Thank you[/ref]
    >
    > I thought you wouldn't value the opinion of anyone who argued with the
    > sweeping statement ...[/ref]

    oops - forgot the smiley ;-)


    Bob Guest

  10. #10

    Default Re: Better way to do this query?

    I agree that for most cases JOIN would be a better option than IN, of
    course. But not for all really - that's all that I wanted to say. Actually,
    I discovered it for myself not many time ago that's why I decide to mention
    about it :)

    "sampangi" <com> wrote in message
    news:phx.gbl... 
    not 


    Alex Guest

Similar Threads

  1. Creating data query from mySQL stored query!?
    By johnegbert in forum Coldfusion Database Access
    Replies: 2
    Last Post: August 5th, 09:28 PM
  2. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  3. Replies: 1
    Last Post: July 2nd, 09:09 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139