Skip to the content
Start

INTERSECT and EXCEPT

SQL · Unit 10: WITH and sets

"The Lighthouse" and "The Blue House" are the library's two most borrowed books, 1 and 3. The librarian wants to suggest a third to anyone who has already read both. Who has borrowed one and also the other?

INTERSECT is written where UNION would go, and it returns the rows that are in both results. Ann and Dylan borrowed "The Lighthouse." Luke and Ann borrowed "The Blue House." INTERSECT removes repeats too. UNION, INTERSECT, and EXCEPT are called set operators.

SELECT member
FROM loans
WHERE book_id = 1
INTERSECT
SELECT member
FROM loans
WHERE book_id = 3;

Returns

member
Ann

The rest of the explanation is in the lesson, which is part of the full plan.

Exercises in this lesson

You do them in the app, which checks them on the spot and explains why.

  1. 1. Predict the output

    Someone tries the same question with an AND. Book 1 has been borrowed by Ann and Dylan, and book 3 by Luke and Ann. What does this query return?

  2. 2. Put the lines in order

    Erin Reilly wrote books 1 and 2, and Tom Vance wrote 3 and 4. Which members have borrowed something by both authors? Put the query in order. Two lines don't belong.

  3. 3. Predict the output

    The same two lists, with their places traded. Book 3 has been borrowed by Luke and Ann, and book 1 by Ann and Dylan. What does this query return?

  4. 4. Find the bug

    Which books have never been borrowed? With LEFT JOIN there were two, 5 and 8, which the librarian sends to storage. This version takes the numbers of all the books and subtracts the numbers of the borrowed ones. It runs and returns nothing. Which line has the problem?

  5. 5. Multiple choice

    The bottom list, the author_ids from books, has a NULL, the one from "Short Stories." Why doesn't it bother EXCEPT, when it does bother NOT IN?

  6. 6. Complete the code

    To see the name, the EXCEPT goes in a step and the step is joined with authors. Fill in the blanks: what goes in the FROM and what goes in the JOIN.

  7. 7. Complete the code

    Which books does Ann still have left to read? From all the library's titles you take away the ones she has already borrowed. The second half needs a JOIN, because loans only has the book's number. Fill in the blanks.

  8. 8. Multiple choice

    You want the readers of "The Lighthouse" or "The Blue House" who have a book out right now, and you want the query to say the same thing in any engine. How do you write it?

  9. 9. Put the lines in order

    A new book by Tom Vance, the author of books 3 and 4, arrives at the library. The librarian wants to lend it right away to someone who has read that author and has no book out. Put the query in order. Two lines don't belong.

  10. 10. Write the code

    The new Tom Vance book should also be shown to whoever doesn't know the author yet. Write a query with one column, the names of the members who have borrowed something by Erin Reilly, who wrote books 1 and 2, and nothing by Tom Vance, who wrote 3 and 4. Sort by name, from Z to A.

Do this lesson

It opens in your browser. This lesson is part of the full plan; the first unit of each course is free.

See all lessons →