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
13. Filtering with JOINs
Summary

Instruction

Good job! As you remember, you can combine INNER JOIN with WHERE without problems.

However, using WHERE with a LEFT JOIN, RIGHT JOIN or FULL JOIN can sometimes be tricky. Let's see an example. Suppose you want to select all courses and show only those lecturers who were employed after 2004. Here's the query: 

SELECT
  Course.Name,
  Lecturer.Name
FROM Course
LEFT JOIN Lecturer
  ON Course.LecturerId = Lecturer.Id
WHERE Lecturer.EmpYear > 2004

Imagine that a particular course (e.g. "SQL Joins") doesn't have a lecturer assigned yet. This course will NOT show up in the result of the query. Why? Since the course doesn't have a lecturer, the column Lecturer.EmpYear is NULL and the condition Lecturer.EmpYear > 2004 is not true.

If you use a condition on the left table the LEFT JOIN can yield the same result as an INNER JOIN. If you want to keep the rows with no lecturer in the result, you must explicitly include empty (NULL) values:

SELECT
  Course.Name,
  Lecturer.Name
FROM Course
LEFT JOIN Lecturer
  ON Course.LecturerId = Lecturer.Id
WHERE Lecturer.EmpYear > 2004
  OR Lecturer.EmpYear IS NULL

Exercise

Show all books with their Movie adaptations. Select each book's title, the name of its publishing house, the title of its adaptation, and the type of the adaptation. Keep the rows with no adaptation in the result.

Stuck? Here's a hint!

Type:

SELECT
  Book.Title,
  PublishingHouse,
  Adaptation.Title,
  Adaptation.Type
FROM Book
LEFT JOIN Adaptation
  ON Book.Id = Adaptation.BookId
WHERE Type = 'movie'
  OR Type IS NULL;