Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Ran it in my query browser. I was am getting same results for both. But still need a confirmation.

SELECT 
    cs.RSCode
    , CustomerName
    , em.EmailId 
FROM 
    Customer_STN cs
    JOIN EmployeeMaster em ON cs.RSCode = em.UserName
    LEFT OUTER JOIN Confirmed_Attendance ca ON cs.RSCode = ca.rsCode
WHERE
    isConfirmed=1 
AND
    dateOfConfirmation = '2012-08-25'

Second query:

SELECT 
    cs.RSCode
    , CustomerName
    , em.EmailId 
FROM 
    Customer_STN cs
JOIN 
    EmployeeMaster em ON cs.RSCode = em.UserName
WHERE 
    RSCode NOT IN (
        SELECT 
             rsCode 
        FROM 
             Confirmed_Attendance 
        WHERE 
             dateOfConfirmation = '2012-08-25' 
        AND 
             isConfirmed = 1
)

EDIT: Added the WHERE part as Rob noticed. Not so similar any more.

share|improve this question
5  
You mean apart from the obvious thing that there's no filter on dateOfConfirmation or isConfirmed in the first query? –  Rob Farley Sep 27 '12 at 7:13
2  
The question could also be improved by providing DDL (CREATE TABLE and CREATE INDEX at a minimum) for the tables involved. –  Paul White Sep 27 '12 at 7:19
1  
@RobFarley Crap, I missed that. Thanks for pointing that out. –  theTuxRacer Sep 27 '12 at 7:34
3  
The WHERE you have added turns the outer join back into an inner join. You would need the rewrite in @ypercube's answer with the filter in the ON (and a filter in the where clause to bring back only null extended (unmatched) rows). Table definitions (including column nullability) would definitely be useful here as well. –  Martin Smith Sep 27 '12 at 7:56
3  
Also please get in the habit of affixing proper aliases to all of your column names. In the first query, while CustomerName is pretty obvious, how do I figure out what table isConfirmed and dateOfConfirmation come from? This can be very important to identify quickly especially since it really does matter for outer joins. –  Aaron Bertrand Sep 27 '12 at 12:25

2 Answers 2

No. But almost.

There are four things that joins do. I wrote about this at: JOIN simplification in SQL Server

Your "NOT IN" (which you should be careful of, regarding NULLs - try using NOT EXISTS) won't duplicate any rows. A LEFT JOIN can. But consider the use of Unique Indexes/Constraints/PKs, which can help the Query Optimizer treat these as identical.

On the whole, use NOT EXISTS rather than LEFT JOIN, because NOT EXISTS will get treated as an Anti Semi-Join, which is slightly better than what a LEFT JOIN / NOT NULL does. I show this at: Joins without JOIN

So... based on my assumption of what your queries were meant to be, the answer is:

Almost. (And how 'almost' depends on the metadata involved)

share|improve this answer
2  
So... my answer was based on the assumption that your first query was supposed to be the one that ypercube answered with. –  Rob Farley Sep 27 '12 at 7:41

No, they are not equivalent.

If Confirmed_Attendance.rsCode is not nullable, then your second query is equivalent to this one:

SELECT 
    cs.RSCode
    , CustomerName
    , em.EmailId 
FROM 
    Customer_STN cs
    JOIN EmployeeMaster em ON cs.RSCode = em.UserName
    LEFT OUTER JOIN Confirmed_Attendance ca ON cs.RSCode = ca.rsCode
                                           AND ca.dateOfConfirmation = '2012-08-25' 
                                           AND ca.isConfirmed = 1
WHERE
    ca.rsCode IS NULL ;

Without these additions, your first query may produce multiple rows (if ca.rsCode is not unique) or more rows than the second (if there are data with ca.dateOfConfirmation = '2012-08-25' AND ca.isConfirmed = 1, it will show the related results while the 2nd query will not)

share|improve this answer
1  
Logically equivalent, yes. But they'll produce different plans, as per my bit.ly/LJvNE post. –  Rob Farley Sep 27 '12 at 7:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.