Join conditions and Where conditions in Left Join

join;

STUDENT table contains a list of students in my school Database

LIBRARY_BOOK table contains the information of each Book that is with currently with any student.

each student is allowed only 1 book at a time.

I want to know

Library Book with each Class 7 Student.

I am using Microsoft SQL Server 2008 R2

I have few queries which looks almost similar but don't behave in same way.

Query 1 :

Select *
FROM STUDENT ST
LEFT JOIN LIBRARY_BOOK LB
ON ST.Student_Id = LB._Student_ID
WHERE ST.Class = 7

Query 2 :

Select *
FROM STUDENT ST
LEFT JOIN LIBRARY_BOOK LB
ON ST.Student_Id = LB._Student_ID
AND ST.Class = 7

Query 3 :

Select *
FROM STUDENT ST
LEFT JOIN LIBRARY_BOOK LB
ON ST.Student_Id = LB._Student_ID
AND ST.Class = 7
WHERE ST.Class = 7

Can someone explain the difference between these queries in terms of both Output and Performance.

Best Answer

Query 1 will show you All records from student with a Class value of 7, and then any related Library_Book records if they exist.

Query 2 will show you all records from student, and related Library_Book records if they exist and if the matching row in student has a Class of 7.

Query 3 will show you All records from student with a Class value of 7, and related Library_Book records if they exist.

I would expect query 1 and query 3 will give you the same results, and query 2 to give you more rows.

Performance is impossible to talk about without knowing your table structure, distribution of data, indexes, and RDBMS. I would not expect them to be very different though since your filters are all the same.

Related Question