Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

JOINs in MS SQL Server

Simple JOINs recap
Various kinds of JOINs
Filtering with LEFT JOIN and RIGHT JOIN
15. ON and WHERE conditions
Summary

Instruction

Great! There is one more caveat when using LEFT JOIN and its friends. There is a subtle difference between the conditions in the ON and WHERE clauses for these JOINs. The ON condition is evaluated when the tables are joined. The WHERE condition is applied after the rows have been joined. Take a look at this example:

SELECT
  Course.Name,
  Lecturer.Name
FROM Course
LEFT JOIN Lecturer
  ON Course.LecturerId = Lecturer.Id
WHERE Lecturer.Id = 3

The query will list all courses but only those that are taught by the lecturer with Id = 3. Other courses will not have lecturer information listed, even if there is a lecturer assigned to them.

SELECT
  *
FROM Subject
LEFT JOIN Lecturer
  ON Subject.LecturerId = Lecturer.Id
  AND Lecturer.Id = 3

Note that for INNER JOINs conditions in the ON and WHERE clauses are effectively the same.

Exercise

Show the title of each book and the name of its author — but only if the author was born in the 20th century.

Otherwise, the author's name field should be NULL.

Stuck? Here's a hint!

Type: 

SELECT
  Book.Title,
  Author.Name
FROM Book
LEFT JOIN Author
  ON Book.AuthorId = Author.Id
  AND Author.BirthYear BETWEEN 1901 AND 2000