Post reply

Join multiple select statements

  • September 13, 2010 at 10:18 pm

    #238063

    Hi All ,

    How can i join these queries ?

    select * from Table1 WHERE column1 NOT IN ( SELECT column1 FROM Table2)

    select * from Table1 WHERE column1 NOT IN ( SELECT column1 FROM Table3)

    select * from Table1 WHERE column1 NOT IN ( SELECT column1 FROM Table4)

  • September 13, 2010 at 11:10 pm

    #1220441

    Hi there,

    Is this what you're trying to do?

    select * from Table1 WHERE column1 NOT IN (

    SELECT column1 FROM Table2

    UNION ALL

    SELECT column1 FROM Table3

    UNION ALL

    SELECT column1 FROM Table4

    )

    Hope this helps.. 🙂

  • September 14, 2010 at 12:11 am

    #1220459

    Or this?

    select * from Table1

    WHERE column1 NOT IN ( SELECT column1 FROM Table2)

    And column1 NOT IN ( SELECT column1 FROM Table3)

    And column1 NOT IN ( SELECT column1 FROM Table4)

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • September 14, 2010 at 2:01 am

    #1220509

    Ya , thanks

    That will do.

  • September 14, 2010 at 8:30 am

    #1220730

    You're welcome.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • September 14, 2010 at 10:33 pm

    #1221118

    Hello,

    You might want to try this method and compare the query plans and times of the LEFT OUTER JOIN with the previous ones and see which are more efficient. Also, only select the columns that you need from Table1.

    This method will join all of the rows that you need from Table2, 3 and 4. Then it will join the rows that match using the left outer join and the rows that don't match are denoted by the IS NULL.

    Now if the data from column1 in Table2, 3 and 4 are not unique and there is a great number on non unique rows then we can minimize the joining rows by wrapping the derived table in a GROUP BY statement.

    Depending on what the data looks like there are a few more options to tune this query, although this should give good performance.

    [font="Courier New"]

    SELECT

    t1.*

    FROM

    Table1 AS t1

    LEFT OUTER JOIN

    (

    SELECT

    t2.column1 AS column1

    FROM

    Table2 AS t2

    UNION ALL

    SELECT

    t3.column1 AS column1

    FROM

    Table3 AS t3

    UNION ALL

    SELECT

    t4.column1 AS column1

    FROM

    Table4 AS t4

    ) AS n

    ON n.column1 = t1.column1

    WHERE

    n.column1 IS NULL

    [/font]

    -Jeff

Viewing 6 posts - 1 through 5 (of 5 total)

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