14. Table Joins in SQL
Up until this point, all queries have been limited to selecting data from a single table at a time using the SELECT
statement. One of the most powerful features of SQL and relational database systems is the Join. Joins allow linking data from two or more tables into a single query result using a single SELECT
statement.
14.1. Introduction to Joins
A Join in a SQL SELECT
statement is recognized when more than one table appears after the FROM
keyword.
SELECT "list-of-columns"
FROM table1, table2
WHERE "search-condition(s)";
14.2. Flat Table vs. Relational Tables
Working with a single table (flat table) can lead to redundant data. For instance, if all customer purchases are recorded in one table, redundant data will be inserted for each purchase:
Customer Purchases Data
id | first | last | address | city | state | zip | date | item | price |
---|---|---|---|---|---|---|---|---|---|
10982 | Wolfgang | Schultz | 300 N. 1st Ave | Yuma | AZ | 85002 | 032299 | snowboard | 45.00 |
10982 | Wolfgang | Schultz | 300 N. 1st Ave | Yuma | AZ | 85002 | 082899 | snow shovel | 35.00 |
10982 | Wolfgang | Schultz | 300 N. 1st Ave | Yuma | AZ | 85002 | 091199 | gloves | 15.00 |
10982 | Wolfgang | Schultz | 300 N. 1st Ave | Yuma | AZ | 85002 | 100999 | lantern | 35.00 |
10982 | Wolfgang | Schultz | 300 N. 1st Ave | Yuma | AZ | 85002 | 022900 | tent | 85.00 |
An ideal database should have separate tables for customers and purchases to eliminate redundant data:
14.2.1 Customer_info Table
id | first | last | address | city | state | zip |
---|
14.2.2 Purchases Table
id | date | item | price |
---|
14.3. Performing Joins
To select data from both tables, use a join on the common column customer_number
:
SELECT customer_info.firstname, customer_info.lastname, purchases.item
FROM customer_info, purchases
WHERE customer_info.customer_number = purchases.customer_number;
This is an Inner Join (Equijoin), the most common type of join.
Each column name is preceded by the table name to avoid confusion, especially if column names are identical in both tables.
14.4. ANSI SQL-92 Syntax
Another syntax specification for an Inner Join:
SELECT customer_info.firstname, customer_info.lastname, purchases.item
FROM customer_info INNER JOIN purchases
ON customer_info.customer_number = purchases.customer_number;
Example with employee data:
SELECT employee_info.employeeid, employee_info.lastname, employee_sales.comission
FROM employee_info, employee_sales
WHERE employee_info.employeeid = employee_sales.employeeid;
14.5. Exercises
14.5.1. Exercise 1
Write a query using a join to determine which items were ordered by each of the customers in the customers table. Select the customerid, firstname, lastname, order_date, item, and price for everything each customer purchased in the items_ordered table.
SELECT customers.customerid, customers.firstname, customers.lastname,
items_ordered.order_date, items_ordered.item, items_ordered.price
FROM customers, items_ordered
WHERE customers.customerid = items_ordered.customerid;
15.5.2. Exercise 2
Repeat exercise #1, but display the results sorted by state in descending order.
SELECT customers.customerid, customers.firstname, customers.state, items_ordered.item
FROM customers, items_ordered
WHERE customers.customerid = items_ordered.customerid
ORDER BY customers.state DESC;