1

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.

Improve this question
0

1 Answer 1

Reset to default
7

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.

Improve this answer
8
  • In the first query, I am joining Entire table Student with Library and then fetching the class 7 results. on the other hand in query 3, I am joining only class 7 portion of Student with Library. So wont it be a relevant improvement in performance.
    – Pratyush Dhanuka
    Jan 20, 2015 at 6:13
  • @PratyushDhanuka Query 3 had redundant filters, so the performance would be the same. If you filter on the same criteria twice I would expect any RDBMS to optimize one out. The extra filter in the JOIN condition is not on the JOINed table, and already exists in the WHERE clause so it doesn't actually matter.
    – JNK
    Jan 20, 2015 at 13:18
  • Its true that query 1 and query 3 will give same output but the join condition is not redundant. You see the difference in query 3 I am only joining with class 7 records then filtering class 7 records. Where as in Query 1, I am joining entire table then filtering only class 7, here records from other classes will also be joined with Library table which I don't want as I have no need for result from other class but 7. So again I believe query 3 will have better perf. You can assume student table is indexed with student id, and have equal distribution of data over each class and clarify.
    – Pratyush Dhanuka
    Jan 20, 2015 at 14:06
  • 3
    @PratyushDhanuka You said it yourself: If query 1 and query 3 always give same output, then the join condition is redundant. Nothing else is needed.
    – ypercubeᵀᴹ
    Jan 20, 2015 at 14:15
  • 1
    @PratyushDhanuka In query 1 you are not "joining the entire table then filtering". I mean, you wrote that code, but the optimizer isn't (necessarily) doing things in that order, and it will most certainly disregard the redundant join condition. You can see this clearly by comparing query 1 and query 3 execution plans. Remember that SQL is declarative, you tell the optimizer what you want, and it will get you the best way to do it
    – Lamak
    Jan 20, 2015 at 14:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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