Perform Complex JOINS in SQL

Table of Contents

1. Introduction
2. Demo: Queries on hierarchical data with SELF JOINS

• 2.1 SELF JOIN concept
• 2.2 Setting up the database and table
• 2.3 Inserting example data
• 2.4 Query 1 — INNER JOIN: employees with a manager
• 2.5 Query 2 — LEFT JOIN: all employees, including without manager
• 2.6 Request 3 — GROUP_CONCAT: group employees by manager
• 2.7 Cleaning

3. Demo: Generation of combinations with CROSS JOINS

• 3.1 CROSS JOIN concept
• 3.2 Setting up the database and tables
• 3.3 Query 1 — All product–promotion combinations
• 3.4 Query 2 — Price calculation after discount
• 3.5 Cleaning

4. Demo: Merging incomplete data with FULL OUTER JOINS

• 4.1 FULL OUTER JOIN concept
• 4.2 Setting up the database and tables
• 4.3 Simulation of FULL OUTER JOIN in MySQL (UNION of LEFT JOIN + RIGHT JOIN)
• 4.4 FULL OUTER JOIN native in SQL Server and PostgreSQL
• 4.5 Cleaning

5. Demo: Simplifying queries with the USING clause

• 5.1 Concept of the USING clause
• 5.2 Setting up the database and tables
• 5.3 Query 1 — JOIN with ON condition (traditional syntax)
• 5.4 Query 2 — JOIN with USING clause (simplified syntax)
• 5.5 Query 3 — JOIN on three tables with the USING clause
• 5.6 Cleaning

6. Comparison of SQL JOINS between SQL platforms

• 6.1 JOIN vs INNER JOIN
• 6.2 The USING clause: support depending on the platforms
• 6.3 FULL OUTER JOIN: support depending on platforms

7. Summary and good practices
8. Demo files

1. Introduction

This course covers the execution of complex JOINs in SQL. It is aimed at people who already master the fundamentals of SQL JOINs and wish to deepen their knowledge with advanced techniques and their practical applications in the context of real database operations.

The course is taught by Pinal Dave, a recognized SQL expert, and covers four main techniques:

The course concludes with a comparison of the behaviors of these JOINs between SQL Server, PostgreSQL and MySQL, highlighting the differences in syntax and compatibility between these platforms.

2. Demo: Queries on hierarchical data with SELF JOINS

2.1 Concept of SELF JOIN

A SELF JOIN is a join of a table with itself. Unlike ordinary JOINs which combine columns from two different tables, the SELF JOIN exploits a self-referential relationship within the same table.

The classic use case is the representation of a company hierarchy: each employee has a manager_id column which points to the employee_id of another record in the same table. This makes it possible to model structures such as:

• A CEO without a manager
• Managers who report to the CEO
• Team leads who report to managers

The SELF JOIN requires using table aliases to distinguish between two references to the same table in the query.

2.2 Setting up the database and table

We first create a dedicated database company_db, which allows all operations to be confined to this specific environment. Then, we create the employees table with a self-referential foreign key (manager_id → employee_id).

-- Créer une nouvelle base de données
CREATE DATABASE company_db;
USE company_db;

-- Créer la table des employés
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    position VARCHAR(50),
    manager_id INT, -- Référence l'employee_id du manager
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

Key points of the structure:

• employee_id: primary key uniquely identifying each employee.
• employee_name: employee name.
• position: employee title or position.
• manager_id: self-referential foreign key that points to the employee_id of the employee’s direct manager. If the value is NULL, the employee does not have a manager (case of the CEO).

2.3 Inserting sample data

The inserted data simulates a complete company organization chart with three hierarchical levels.

-- Insérer des données représentant une organisation
INSERT INTO employees (employee_id, employee_name, position, manager_id) VALUES
(1, 'Alice',   'CEO',       NULL),  -- Alice est PDG (aucun manager)
(2, 'Bob',     'Manager',   1),     -- Bob rapporte à Alice
(3, 'Charlie', 'Manager',   1),     -- Charlie rapporte à Alice
(4, 'David',   'Team Lead', 2),     -- David rapporte à Bob
(5, 'Eva',     'Team Lead', 2),     -- Eva rapporte à Bob
(6, 'Frank',   'Team Lead', 3),     -- Frank rapporte à Charlie
(7, 'Grace',   'Team Lead', 3);     -- Grace rapporte à Charlie

Hierarchy representation:

Alice (CEO)
├── Bob (Manager)
│   ├── David (Team Lead)
│   └── Eva (Team Lead)
└── Charlie (Manager)
    ├── Frank (Team Lead)
    └── Grace (Team Lead)

2.4 Query 1 — INNER JOIN: employees with a manager

This query uses an INNER JOIN (or simply JOIN) to display only employees who have a manager. Employees without a manager (like Alice, whose manager_id is NULL) are excluded from the result.

The employees table is referenced twice, with aliases e1 (for employee) and m (for manager). The join condition e1.manager_id = m.employee_id establishes the hierarchical relationship.

-- 1. Afficher uniquement les employés qui ont un manager
--    (exclut les employés sans manager)
SELECT e1.employee_name AS 'Employee',
       e1.position      AS 'Employee Position',
       m.employee_name  AS 'Manager',
       m.position       AS 'Manager Position'
FROM employees e1
JOIN employees m ON e1.manager_id = m.employee_id;

Expected result:

Alice is excluded because her manager_id is NULL — the INNER JOIN only returns rows with a match in both “instances” of the table.

2.5 Query 2 — LEFT JOIN: all employees, including without managers

This query uses a LEFT JOIN to include all employees, even those without managers. The COALESCE function is used to replace NULL values ​​with readable labels.

-- 2. Afficher les employés, leurs postes, et leurs managers
--    (inclut tous les employés, même sans manager)
SELECT e1.employee_name                            AS 'Employee',
       e1.position                                  AS 'Employee Position',
       COALESCE(m.employee_name, 'No Manager')      AS 'Manager',
       COALESCE(m.position,      'N/A')              AS 'Manager Position'
FROM employees e1
LEFT JOIN employees m ON e1.manager_id = m.employee_id;

Expected result:

COALESCE(value, 'replacement') returns the first non-NULL value. Here, when m.employee_name is NULL (no manager), it returns 'No Manager'.

2.6 Query 3 — GROUP_CONCAT: group employees by manager

This query demonstrates an advanced use of SELF JOIN combined with the GROUP_CONCAT aggregation function to obtain, for each manager, the list of their direct reports as a comma-separated character string.

-- 3. Regrouper les employés par manager et afficher la liste
--    des employés sous forme de chaîne séparée par des virgules
SELECT m.employee_name AS 'Manager',
       GROUP_CONCAT(e1.employee_name SEPARATOR ', ') AS 'Employees'
FROM employees e1
JOIN employees m ON e1.manager_id = m.employee_id
GROUP BY m.employee_name;

Expected result:

GROUP_CONCAT is a MySQL function that concatenates the values ​​of a group into a single string. It is useful for obtaining an overview of the organizational structure.

2.7 Cleaning

-- Supprimer la table employees
DROP TABLE IF EXISTS employees;

-- Supprimer la base de données
DROP DATABASE IF EXISTS company_db;

3. Demo: Generation of combinations with CROSS JOINS

3.1 CROSS JOIN concept

A CROSS JOIN produces the Cartesian product of two tables: each row of the first table is combined with each row of the second table. The result contains n × m rows, where n is the number of rows in the first table and m is that in the second.

The CROSS JOIN does not have a join condition (ON or USING). It is particularly useful for:

• Generate all possible combinations between two sets (e.g.: products × promotions)
• Create dynamic pricing models where each item must be associated with each possible discount
• Generate marketing reports or comprehensive sales scenarios

3.2 Setting up the database and tables

We simulate an e-commerce context with two tables: products (products) and promotions (promotions).

-- Créer une nouvelle base de données
CREATE DATABASE ecommerce_db;
USE ecommerce_db;

-- Créer la table des produits
CREATE TABLE products (
    product_id   INT PRIMARY KEY,
    product_name VARCHAR(100),
    price        DECIMAL(10, 2)
);

-- Insérer les données des produits
INSERT INTO products (product_id, product_name, price) VALUES
(1, 'Laptop',     1000.00),
(2, 'Smartphone',  600.00),
(3, 'Tablet',      300.00);

-- Créer la table des promotions
CREATE TABLE promotions (
    promotion_id          INT PRIMARY KEY,
    promotion_description VARCHAR(100),
    discount_percentage   DECIMAL(5, 2)
);

-- Insérer les données des promotions
INSERT INTO promotions (promotion_id, promotion_description, discount_percentage) VALUES
(1, 'Back to School', 10.00),
(2, 'Holiday Sale',   20.00),
(3, 'Clearance',      50.00);

3.3 Query 1 — All product–promotion combinations

The CROSS JOIN here returns 9 rows (3 products × 3 promotions), covering all possible combinations.

-- Générer toutes les combinaisons produit-promotion avec CROSS JOIN
SELECT p.product_name         AS 'Product',
       p.price                AS 'Price',
       pr.promotion_description AS 'Promotion',
       pr.discount_percentage  AS 'Discount (%)'
FROM products p
CROSS JOIN promotions pr;

Expected result (9 lines):

3.4 Query 2 — Calculation of price after discount

This query goes further by calculating the final price after applying the discount for each product–promotion combination. The formula used is:

$$\text{Discounted price} = \text{Original price} - \left(\text{Original price} \times \frac{\text{Discount}}{100}\right)$$

-- Générer toutes les combinaisons et calculer le prix final après remise
SELECT p.product_name               AS 'Product',
       p.price                      AS 'Original Price',
       pr.promotion_description     AS 'Promotion',
       pr.discount_percentage       AS 'Discount (%)',
       (p.price - (p.price * pr.discount_percentage / 100)) AS 'Discounted Price'
FROM products p
CROSS JOIN promotions pr;

Example of results (a few lines):

This type of query is particularly useful in dynamic pricing models, where discounts change frequently and should be reflected instantly in displayed prices.

3.5 Cleaning

-- Supprimer les tables
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS promotions;

-- Supprimer la base de données
DROP DATABASE IF EXISTS ecommerce_db;

4. Demo: Merging incomplete data with FULL OUTER JOINS

4.1 Concept of FULL OUTER JOIN

A FULL OUTER JOIN returns all rows from both tables, whether or not there is a match between them. When a match does not exist on one side, the columns on that side are populated with NULL values.

This type of JOIN is particularly powerful for working with incomplete datasets, for example:

• Customers who have not yet placed an order
• Orders that do not yet have an associated customer (orphan data)
• Reconciling two systems that do not always share the same records

Behavior:

4.2 Setting up the database and tables

-- Créer une nouvelle base de données
CREATE DATABASE customer_order_db;
USE customer_order_db;

-- Créer la table des clients
CREATE TABLE customers (
    customer_id   INT PRIMARY KEY,
    customer_name VARCHAR(100)
);

-- Insérer les données des clients
-- Michael et Sarah n'ont pas encore passé de commande
INSERT INTO customers (customer_id, customer_name) VALUES
(1, 'John Doe'),
(2, 'Jane Smith'),
(3, 'Michael Brown'),  -- Michael n'a pas encore passé de commande
(4, 'Sarah Johnson');  -- Sarah n'a pas encore passé de commande

-- Créer la table des commandes
CREATE TABLE orders (
    order_id     INT PRIMARY KEY,
    customer_id  INT,           -- Clé étrangère vers les clients
    order_amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Insérer les données des commandes
-- La dernière commande n'a pas de client associé (données incomplètes)
INSERT INTO orders (order_id, customer_id, order_amount) VALUES
(1, 1,    150.00),   -- Commande de John
(2, 1,    200.00),   -- Autre commande de John
(3, 2,    350.00),   -- Commande de Jane
(4, NULL, 500.00);   -- Commande sans client (données incomplètes)

Data status:

• John Doe: 2 commands (IDs 1 and 2)
• Jane Smith: 1 order (ID 3)
• Michael Brown: 0 orders
• Sarah Johnson: 0 orders
• Order ID 4: without associated customer (customer_id = NULL)

4.3 Simulation of FULL OUTER JOIN in MySQL (UNION of LEFT JOIN + RIGHT JOIN)

MySQL does not natively support FULL OUTER JOIN. We simulate it by combining a LEFT JOIN and a RIGHT JOIN with the UNION operator.

• The LEFT JOIN ensures that all customers are included, even those without orders.
• The RIGHT JOIN ensures that all orders are included, even those without an associated customer.
• The UNION operator merges the two results eliminating duplicates.

-- Simuler un FULL OUTER JOIN avec UNION de LEFT JOIN et RIGHT JOIN (MySQL)
SELECT c.customer_name AS 'Customer',
       o.order_id      AS 'Order ID',
       o.order_amount  AS 'Order Amount'
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id

UNION

SELECT c.customer_name AS 'Customer',
       o.order_id      AS 'Order ID',
       o.order_amount  AS 'Order Amount'
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

Expected result:

Result includes Michael Brown and Sarah Johnson (without orders) as well as order ID 4 (without customers). No data is lost during the merge.

4.4 Native FULL OUTER JOIN in SQL Server and PostgreSQL

For SQL Server and PostgreSQL users, a direct FULL OUTER JOIN can be used. The syntax is simpler and achieves the same result as the MySQL simulation above.

-- SQL Server et PostgreSQL : utiliser FULL OUTER JOIN nativement
SELECT c.customer_name AS 'Customer',
       o.order_id      AS 'Order ID',
       o.order_amount  AS 'Order Amount'
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;

The native FULL OUTER JOIN is highly effective in systems that support it for merging incomplete datasets.

4.5 Cleaning

-- Supprimer les tables (dans l'ordre inverse des dépendances)
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;

-- Supprimer la base de données
DROP DATABASE IF EXISTS customer_order_db;

5. Demo: Simplifying queries with the USING clause

5.1 Concept of the USING clause

The USING clause is an alternative syntax to the ON clause for specifying join conditions. It is available in MySQL and PostgreSQL (but not in SQL Server).

The USING clause can be used when:

1. The two tables share a column with the same name serving as a join key.
2. We want to write more concise and readable queries, particularly when joining several tables.

Syntactic comparison:

-- Avec ON (syntaxe traditionnelle)
SELECT *
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id;

-- Avec USING (syntaxe simplifiée)
SELECT *
FROM customers
JOIN orders USING (customer_id);

Warning: The USING clause only works if the join columns have exactly the same name in both tables. If the names differ, use ON.

5.2 Setting up the database and tables

-- Créer une nouvelle base de données
CREATE DATABASE using_clause_demo_db;
USE using_clause_demo_db;

-- Créer la table des clients
CREATE TABLE customers (
    customer_id   INT PRIMARY KEY,
    customer_name VARCHAR(100)
);

-- Insérer les données des clients
INSERT INTO customers (customer_id, customer_name) VALUES
(1, 'John Doe'),
(2, 'Jane Smith'),
(3, 'Michael Brown');

-- Créer la table des commandes
-- customer_id est la colonne commune (même nom dans les deux tables)
CREATE TABLE orders (
    order_id     INT PRIMARY KEY,
    customer_id  INT,   -- Colonne commune
    order_amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Insérer les données des commandes
INSERT INTO orders (order_id, customer_id, order_amount) VALUES
(1, 1, 150.00),
(2, 1, 200.00),
(3, 2, 350.00);
-- Michael Brown n'a pas de commande

5.3 Query 1 — JOIN with ON condition (traditional syntax)

The traditional method with ON is self-explanatory but can become verbose, especially with many tables.

-- JOIN avec la condition ON (syntaxe traditionnelle)
SELECT customers.customer_name,
       orders.order_id,
       orders.order_amount
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id;

Expected result:

5.4 Query 2 — JOIN with USING clause (simplified syntax)

The same query rewritten with USING: the join column customer_id is specified only once. The result is identical.

-- JOIN avec la clause USING (syntaxe simplifiée)
SELECT customer_name,
       order_id,
       order_amount
FROM customers
JOIN orders USING (customer_id);

With USING, it is no longer necessary to prefix columns with the table name in the join clause. The query is more clean and readable.

5.5 Query 3 — JOIN on three tables with USING clause

We add a third table products to demonstrate how USING simplifies multi-table joins.

-- Créer la table des produits
-- order_id est la colonne commune avec la table orders
CREATE TABLE products (
    product_id   INT PRIMARY KEY,
    product_name VARCHAR(100),
    order_id     INT,   -- Colonne commune avec la table orders
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

-- Insérer les données des produits
INSERT INTO products (product_id, product_name, order_id) VALUES
(1, 'Laptop',     1),
(2, 'Smartphone', 2),
(3, 'Tablet',     3);

-- JOIN sur trois tables avec USING
SELECT customer_name,
       order_id,
       product_name,
       order_amount
FROM customers
JOIN orders   USING (customer_id)
JOIN products USING (order_id);

Expected result:

The USING clause significantly reduces syntactic complexity when multiple tables share columns with the same name.

5.6 Cleaning

-- Supprimer les tables (dans l'ordre inverse des dépendances)
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;

-- Supprimer la base de données
DROP DATABASE IF EXISTS using_clause_demo_db;

6. Comparison of SQL JOINS between SQL platforms

This section compares the behavior and syntax of advanced JOINs between the three major SQL platforms: SQL Server, PostgreSQL and MySQL.

6.1 JOIN vs INNER JOIN

All three platforms treat JOIN and INNER JOIN as identical. The two terms are interchangeable and produce the same result.

-- Ces deux requêtes sont équivalentes sur toutes les plateformes
SELECT * FROM a JOIN b ON a.id = b.id;
SELECT * FROM a INNER JOIN b ON a.id = b.id;

6.2 The USING clause: support depending on the platforms

The USING clause simplifies join syntax when columns share the same name between tables. However, it is not supported by SQL Server.

On SQL Server, USING syntax must be changed to ON:

-- PostgreSQL / MySQL (avec USING)
SELECT customer_name, order_id, order_amount
FROM customers
JOIN orders USING (customer_id);

-- SQL Server (avec ON — obligatoire)
SELECT c.customer_name, o.order_id, o.order_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

6.3 FULL OUTER JOIN: support depending on the platforms

The FULL OUTER JOIN is natively supported by SQL Server and PostgreSQL. On the other hand, MySQL does not support it directly and requires simulation.

Summary of syntaxes according to platform:

-- SQL Server et PostgreSQL : FULL OUTER JOIN natif
SELECT c.customer_name, o.order_id, o.order_amount
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;

-- MySQL : simulation avec UNION de LEFT JOIN + RIGHT JOIN
SELECT c.customer_name, o.order_id, o.order_amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
UNION
SELECT c.customer_name, o.order_id, o.order_amount
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

Compatibility summary table:

7. Summary and best practices

Types of JOIN covered in this course

Best practices

1. Always use aliases during a SELF JOIN to distinguish the two roles in the same table (eg: e1 for the employee, m for the manager).

2. Be careful with CROSS JOINs on large tables: the Cartesian product can generate a very large number of rows (e.g.: 1,000 rows × 1,000 rows = 1,000,000 rows). Use a WHERE filter if necessary.

3. Use COALESCE to replace NULL values ​​in the results of LEFT JOIN, RIGHT JOIN and FULL OUTER JOIN to improve the readability of the results.

4. Check the target platform before using the USING or FULL OUTER JOIN clause:

• USING is not available in SQL Server.
• FULL OUTER JOIN must be simulated in MySQL.

5. Name columns with aliases (AS) in the results to improve readability, especially during SELF JOINs where columns with the same name appear multiple times.

6. Use GROUP_CONCAT (MySQL) or STRING_AGG (SQL Server/PostgreSQL) to aggregate related values ​​into a single readable string in hierarchical reports.

7. Always clean demo databases and tables after testing (DROP TABLE IF EXISTS, DROP DATABASE IF EXISTS) to avoid conflicts in subsequent runs.

8. Demo files

The demo SQL files are located in the 01/demos/ directory:

The training slides are available in: 01/perform-complex-joins-in-sql-slides.pdf

Search Terms

perform · complex · joins · sql · fundamentals · databases · join · query · clause · outer · cleaning · concept · database · setting · tables · data · employees · platforms · combinations · cross · depending · inner · left · manager