Chapter 9. Working with Multiple Tables and Queries

This chapter covers how to bring together multiple tables by either joining them or using union operators, and also how to work with multiple queries using common table expressions.

Table 9-1 includes descriptions and code examples of the three concepts covered in this chapter.

Table 9-1. Working with multiple tables and queries
Concept Description Code Example

Joining Tables

Combine the columns of two tables based on matching rows.

 
SELECT c.id, l.city
FROM customers c
  INNER JOIN loc l
  ON c.lid = l.id;

Union Operators

Combine the rows of two tables based on matching columns.

 
SELECT name, city
FROM employees;
UNION
SELECT name, city
FROM customers;

Common Table Expressions

Temporarily save the output of a query, for another query to reference it. Also includes recursive and hierarchical queries.

 
WITH my_cte AS (
  SELECT name,
    SUM(order_id)
    AS num_orders
  FROM customers
  GROUP BY name)

SELECT MAX(num_orders)
FROM my_cte;

Joining Tables

In SQL, joining means combining data from multiple tables together within a single query. The following two tables list the state a person lives in and the pets they own:

-- states            -- pets
+------+-------+    +------+------+
| name | state |    | name | pet  |
+------+-------+    +------+------+
| Ada  | AZ    |    | Deb  | dog  |
| Deb  | DE    |    | Deb  | duck |
+------+-------+    | Pat  | pig  |
                    +------+------+

Use the JOIN clause to join the two tables into one table:

SELECT *
FROM states s INNER JOIN ...

Get SQL Pocket Guide, 4th Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial