Are these two queries technically equal to each other?

Question

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.

Answer 1

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)

Answer 2

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)