UNION

SQL · Unit 10: WITH and sets

Full plan
SQL
10 exercises

The neighborhood library is having an anniversary and throwing a party. It's inviting its authors and its members. Who gets an invitation? The names are in two tables. The authors are in authors. The members are in loans, where each row is a book that went out, with the member who borrowed it, the day it went out, and the day it came back.

UNION puts one query's result below another's. It doesn't match anything up, the way a JOIN would. It stacks. The column takes its heading from the first SELECT. Ann has borrowed three books and shows up once, because UNION removes repeated rows.

SELECT book_id, member, borrowed,
  returned
FROM loans;
SELECT name
FROM authors
UNION
SELECT member
FROM loans;

Returns

book_id	member	borrowed	returned
1	Ann	2026-01-02	2026-01-09
3	Luke	2026-01-03	2026-01-05
1	Dylan	2026-01-10	NULL
4	Ann	2026-01-10	2026-01-14
6	Hannah	2026-01-12	NULL
3	Ann	2026-01-13	NULL
7	Luke	2026-01-08	2026-01-15
2	Brian	2025-12-20	2026-01-04

Returns

name
Ann
Brian
Dylan
Erin Reilly
Hannah
Luke
Martha Sloan
Nora Page
Patrick Lyle
Tom Vance

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. Predict the output
Dylan, Hannah, and Ann each have a book they haven't returned. "The Lighthouse," which is book 1, has been borrowed by Ann and Dylan. What does this query return?
2. Put the lines in order
The library is celebrating Ireland this month and is holding a reading. It's inviting its Irish authors and, to fill the room, the members who have a book at home right now. Put in order the query that gives all those names in one column with the heading name, from Z to A. Two lines don't belong.
3. Find the bug
The guest list has two columns, the name and the country. Nobody knows the members' country, and their row has to say unknown. Here SQL answers that the SELECTs on the two sides of the UNION don't have the same number of columns. Which line has the problem?
4. Put the lines in order
The publisher of "The Lighthouse," book 1, wants a panel with the authors from outside Ireland and the members who have borrowed the book. Put in order the query that gives each one's name and role, authors first and then members, and within each role from Z to A. Two lines don't belong.
5. Predict the output
"The Lighthouse" is book 1, and Ann and Dylan have borrowed it. "The Blue House" is book 3, and Luke and Ann have borrowed it. What does this query return?
6. Find the bug
This query should give the log for "The Lighthouse," book 1: each day it went out or came back, and the member who stopped by the front desk. It runs, and in the last row the day says Ann. Which line has the problem?
7. Complete the code
Who stops by the front desk the most? Each loan is one visit, and each return is another. The two lists are stacked in a step, and the query below counts each member's rows. Fill in the blank with what goes between the two lists.
8. Multiple choice
On which days has the front desk seen any activity? Three loans still haven't been returned, and their returned is NULL. UNION removes repeats the same way DISTINCT does. How many empty rows does the result have?
9. Find the case that fails
The front desk log has one row per visit, with the day and the member. Today it has 13, eight loans and five returns. Someone wrote it with a plain UNION. Which of these changes makes it lose a visit?
10. Write the code
The log for "The Blue House," book 3. Write a query with two columns, in this order: the day and what happened that day, which is the text out when the book was borrowed and back when it was returned. One row for each time it went out and one for each return that has already happened, from the earliest day to the latest.

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 →