What is the difference between MINUS and LEFT JOIN in SQL Server?

This question seems to be very easy and you will start answering once you listen the question. But, be careful while answering very easy questions. Interviewer might test you by asking this kind of tricky questions. Don’t think that the questions were asked by the interviewer are always correct. Not all interviewers, but some interviewers try to confuse us by asking questions which are not related to SQL Server. Actually this question asked by my interviewer in my previous interview. I gave the answer which was in his mind and now I’m working for that organization. 

Interviewer: What is the difference between MINUS and LEFT JOIN in SQL Server?

Me: Frankly speaking and I’m sorry to say that there is no keyword called MINUS in SQL Server. MINUS is the keyword from Oracle and EXCEPT is the keyword in SQL Server which is equivalent to MINUS. EXCEPT works like A-B in sets. That means, if we are doing A-B on the result sets A and B, it just returns all the values of A which are not common between A and B. On the other hand, Left Join returns all the values in A and all the values which are common between A and B.

(A LEFT JOIN B) = (A EXCEPT B) + (A INNER JOIN B)

Interviewer: Wow… This is what I’m expecting. Great man :)

Me: Thank you :)

Happy Reading :)

Leave a Reply

Your email address will not be published. Required fields are marked *