September 13, 2010 at 10:18 pm
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
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
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
Ya , thanks
That will do.
September 14, 2010 at 8:30 am
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
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