lesson 26 Joins & sets · advanced

Combine sets with UNION

Combine query results with or without duplicates.

the idea

UNION stacks the rows of two queries with the same number of columns and removes duplicates. UNION ALL keeps every row, which is what you need when each row is an event to be counted.

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

   Stack two role lists with UNION. Run this. UNION combines author and reviewer rows and removes duplicate developer ids.the pattern

   example.sql Run

   sql›

   SELECT author_id AS developer_id, 'author' AS role
   FROM pull_requests
   WHERE repository_id = 1
   UNION
   SELECT reviewer_id, 'reviewer'
   FROM reviews
   JOIN pull_requests USING (pull_request_id)
   WHERE repository_id = 1;

2. 2

   See UNION versus UNION ALL counts. Run this. UNION ALL keeps every row; UNION drops duplicates so totals differ.

   step-2.sql Run

   sql›

   SELECT
     (
       SELECT COUNT(*)
       FROM (
         SELECT author_id
         FROM pull_requests
         UNION
         SELECT reviewer_id
         FROM reviews
       )
     ) AS union_rows,
     (
       SELECT COUNT(*)
       FROM (
         SELECT author_id
         FROM pull_requests
         UNION ALL
         SELECT reviewer_id
         FROM reviews
       )
     ) AS union_all_rows;

common mistake

UNION removes duplicate rows, so counting contributions after UNION undercounts anyone with two identical rows.