Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Slow Script Expand / Collapse
Author
Message
Posted Sunday, October 6, 2013 11:14 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:51 PM
Points: 36,959, Visits: 31,469
homebrew01 (10/6/2013)


What if you want to include non-matches against T3 ? Would you add to the WHERE clause ?

and T3.COL1 is NULL 
and T3.COL2 is NULL
and T3.COL3 is NULL
and T3.COL4 is NULL



I guess it depends on what you are actually trying to do but if any of the t3 columns are NULL, they won't match any of the t2 columns even if they are also NULL so the result is that the rows won't exist and the WHERE NOT EXISTS will still work.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1501930
Posted Monday, October 7, 2013 1:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:50 AM
Points: 7,191, Visits: 13,645
homebrew01 (10/6/2013)
ChrisM@Work (10/1/2013)
This I reckon is the correct way to write your query:
select T2.COL1, T2.COL2, T1.COL1, T1.COL2, T1.COL3
from T1
join T2
on T1.COL4 = T2.COL3
where T2.COL4 != 'data1'
and T1.COL1 is not NULL
and T1.COL5 is not NULL
and T1.COL6 = 'data2'
AND NOT EXISTS (SELECT 1 FROM T3
WHERE T3.COL1 = T2.COL1
AND T3.COL2 = T2.COL2
AND T3.COL3 = T1.COL1
AND T3.COL4 = T1.COL2)

Check the results against the SELECT part of your original.


So in this case, it will select from T1 and T2, only if not in T3 ?
Yes, that's correct.



What if you want to include non-matches against T3 ? Would you add to the WHERE clause ?
Isn't this the same as "not in T3"?


and T3.COL1 is NULL 
and T3.COL2 is NULL
and T3.COL3 is NULL
and T3.COL4 is NULL



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1502006
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse