Sql-server – Are these two queries technically equal to each other

sql serversubquery

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.

Best Answer

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)

Related Question