lesson 27 Joins & sets · intermediate

INTERSECT & EXCEPT

Find values shared by two results or present in only one.

the idea

INTERSECT returns rows present in both queries; EXCEPT returns rows from the first query that are absent from the second. Both compare whole rows and remove duplicates, so each developer appears once.

Dataset: Developer Platform

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Find reviewers who never authored. Run this. EXCEPT returns reviewer ids that do not appear as pull request authors.the pattern

   example.sql Run

   sql›

   SELECT reviewer_id
   FROM reviews
   EXCEPT
   SELECT author_id
   FROM pull_requests;

2. 2

   Measure overlap between roles. Run this. Subqueries count distinct authors, reviewers, and developers in both roles.

   step-2.sql Run

   sql›

   SELECT
     (
       SELECT COUNT(DISTINCT author_id)
       FROM pull_requests
     ) AS authors,
     (
       SELECT COUNT(DISTINCT reviewer_id)
       FROM reviews
     ) AS reviewers,
     (
       SELECT COUNT(*)
       FROM (
         SELECT author_id
         FROM pull_requests
         INTERSECT
         SELECT reviewer_id
         FROM reviews
       )
     ) AS both_roles;

common mistake

EXCEPT is not symmetric: authors EXCEPT reviewers is a different set from reviewers EXCEPT authors.