SQL Pocket Guide, 4th Edition

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 ...