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
14. Filtering with JOINS – continued
Summary

Instruction

Great! You can use this technique to filter rows with no matching rows.

Suppose we want to get a list of lecturers that still do not have any course assigned:

SELECT
  Course.Name
  Lecturer.Name
FROM Lecturer
LEFT JOIN Course
  ON Course.LecturerId = Lecturer.Id
WHERE LecturerId IS NULL

Notice that we added WHERE LecturerId IS NULL line.

First, our database LEFT JOINs the table. Then, we simply inform the database that we'd like to select only those rows where for which the value of LecturerId IS NULL (i.e. there is no matching course for a given lecturer). Note that the LecturerId is a column in the right table, Course. This value will be missing if there is no matching course.

Exercise

Show the Title and PublishYear for books that do not have any reviews in the book_review table.

Stuck? Here's a hint!

Type:

SELECT
  Title,
  PublishYear
FROM Book
LEFT JOIN BookReview
  ON Book.Id = BookReview.BookId
WHERE BookReview.BookId IS NULL