Chapter 3: Working with Multiple Tables – Joins and Relationships

Overview

In real-world database applications, data is typically spread across multiple tables, which are linked through relationships. This chapter explores how to effectively query multiple tables using SQL joins and relationships. You will learn about different types of joins, how to combine query results using UNION, and more advanced join techniques like self joins and cross joins.

At the end of this chapter, you'll work on an interactive exercise where you'll apply these concepts by querying multiple tables from a sample database.

3.1 Understanding Relationships in Databases

Relational databases organize data across multiple tables that are connected through relationships. The three main types of relationships are:

• One-to-One (1:1) – Each record in Table A corresponds to only one record in Table B.
• One-to-Many (1:M) – Each record in Table A can be linked to multiple records in Table B, but each record in Table B is linked to only one record in Table A.
• Many-to-Many (M:M) – Multiple records in Table A can relate to multiple records in Table B through a junction table.

Example:

• Customers (CustomerID is the Primary Key)
• Orders (CustomerID is a Foreign Key referencing Customers)

3.2 Types of SQL Joins (INNER, LEFT, RIGHT, FULL)

SQL joins allow you to retrieve data from multiple tables based on common keys. The main types of joins include:

1. INNER JOIN

SELECT Customers.CustomerID, Customers.Name, Orders.OrderID
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
        

2. LEFT JOIN (or LEFT OUTER JOIN)

SELECT Customers.CustomerID, Customers.Name, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
        

3. RIGHT JOIN (or RIGHT OUTER JOIN)

SELECT Customers.CustomerID, Customers.Name, Orders.OrderID
FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
        

4. FULL JOIN (or FULL OUTER JOIN)

SELECT Customers.CustomerID, Customers.Name, Orders.OrderID
FROM Customers
FULL JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
        

3.3 Using UNION and UNION ALL

1. UNION

SELECT Name FROM Customers
UNION
SELECT Name FROM Employees;
        

2. UNION ALL

SELECT Name FROM Customers
UNION ALL
SELECT Name FROM Employees;
        

3.4 Self Joins and Cross Joins

1. Self Join

SELECT E1.Name AS Employee, E2.Name AS Manager
FROM Employees E1
INNER JOIN Employees E2 ON E1.ManagerID = E2.EmployeeID;
        

2. Cross Join

SELECT Products.Name, Categories.Name
FROM Products
CROSS JOIN Categories;
        

3.5 Interactive Exercise: Querying Multiple Tables

Using the sample database below, write SQL queries to:

• Retrieve all orders along with customer names.
• Find customers who have never placed an order.
• List employees and their managers.
• Show all product categories, even those without products.

Sample Database

CREATE DATABASE ShopDB;
USE ShopDB;

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100),
    Email VARCHAR(100)
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    ManagerID INT,
    FOREIGN KEY (ManagerID) REFERENCES Employees(EmployeeID)
);

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Name VARCHAR(100),
    CategoryID INT
);

CREATE TABLE Categories (
    CategoryID INT PRIMARY KEY,
    Name VARCHAR(100)
);
        

Sample Data

-- Insert data into Customers table
INSERT INTO Customers (CustomerID, Name, Email) VALUES
(1, 'Alice Johnson', 'alice@example.com'),
(2, 'Bob Smith', 'bob@example.com'),
(3, 'Charlie Brown', 'charlie@example.com'),
(4, 'David Wilson', 'david@example.com'),
(5, 'Emma Davis', 'emma@example.com');

-- Insert data into Orders table
INSERT INTO Orders (OrderID, CustomerID, OrderDate) VALUES
(101, 1, '2023-01-15'),
(102, 2, '2023-02-10'),
(103, 1, '2023-03-05'),
(104, 4, '2023-04-20');