Practical 14 - Table Joins - CSU357P - Shoolini U

Practical 14: Table Joins in SQL

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;