Chapter 12 · 7 levels

Combining result sets

Stack queries with UNION ALL and UNION, compare customer lists with INTERSECT and EXCEPT, and build one labelled report from several sources.

Start

Joins put tables side by side. Set operations put query results on top of each other. You use them whenever the answer lives in several places with the same shape: orders, returns and sessions in one monthly table, two campaign audiences merged into one list, last year’s buyers compared with this year’s.

This chapter covers UNION ALL (stack everything), UNION (stack and remove duplicates), INTERSECT (rows in both results) and EXCEPT (rows in the first but not the second), plus the rules every set query has to follow. The boss level is a yearly customer status report: new, retained, reactivated and lapsed customers, and what each group spent.

Levels

  1. One activity table with UNION ALL

    from Kevin Brooks, Operations Manager

    union all, set operations, label column, long format

    Code

  2. Merge two audiences with UNION

    from Jordan Reyes, Head of Marketing

    union, union all, distinct, set operations

    Code

  3. Customers who came back with INTERSECT

    from Megan Collins, Head of Sales

    intersect, set operations, retention, exists

    Code

  4. Lapsed customers with EXCEPT

    from Lauren Hayes, Email Marketing Manager

    except, set operations, churn, not exists

    Code

  5. Debug: refunds that went missing

    from Brian Mitchell, Finance Analyst

    union, union all, debugging, refunds

    Code

  6. The rules of a UNION

    from Tyler Morgan, Junior Data Engineer

    union all, set operations, column names, order by

    Quiz

  7. Boss: the yearly customer status report

    from Catherine Hale, Chief Executive Officer

    intersect, except, union all, retention, cte, join

    Boss · 3 steps

Cookie settings

Pick what may run on this site. You can change this any time from "Cookie settings" at the bottom of every page.