Multitable Queries with SQL Joins

"SQL joins are essential for combining information from multiple tables, allowing you to answer complex questions and gain deeper insights into your data. Mastering various join types like inner, outer, and self joins is crucial for building powerful SQL queries."- Gemini 2024

Combining Multiple Tables

Joins combine rows from different tables based on related columns.
The resulting dataset from a join contains columns from both tables.
Unions combine result sets (rows and columns) from different SELECT statements, stacking them vertically.
The resulting dataset from a union has the same column structure as the underlying SELECT statements.

A Fictitious Game

Consider these 2 tables from a fictitious game. Each player in the Character table is initially equipped with 0-1 items from the Equipment table

Equipment

SELECT * FROM Equipment;

Characters

SELECT * FROM Characters;

Primary Join Types in SQL

Example JOIN Queries


        SELECT * from Equipment INNER JOIN Characters on Equipment.id = Characters.equip;


        SELECT * from Equipment LEFT JOIN Characters on Equipment.id = Characters.equip;


        SELECT * from Equipment RIGHT JOIN Characters on Equipment.id = Characters.equip;

Returning Select Fields


SELECT Characters.class, Equipment.name
FROM Characters
JOIN Equipment on Characters.equip = Equipment.id;


        Select Equipment.name, Characters.name from Equipment CROSS JOIN Characters

A Cross Join forms a Cartesian Product.
This image is truncated.

All the Joins

Image source

Example Union


SELECT Name FROM Equipment
UNION
SELECT Name FROM Characters
ORDER BY Name;

Useful Resources

Example Setup Code

The examples on this page were created using the following MySQL script to create and populate example queries at onecompiler.com

-- Create demonstration tables

CREATE TABLE if not exists Equipment (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    kind VARCHAR(50),
    damage INT,
    armor INT
);

CREATE TABLE if not exists Characters (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    class VARCHAR(50),
    level INT,
    equip INT,
    FOREIGN KEY (equip) REFERENCES Equipment(id)
);

-- Populate tables with some fake data

INSERT INTO Equipment (id, name, kind, damage, armor)
VALUES
    (101, 'Sword', 'Weapon', 25, 0),
    (102, 'Bow', 'Weapon', 20, 0),
    (103, 'Staff', 'Weapon', 30, 0),
    (104, 'Dagger', 'Weapon', 15, 0),
    (105, 'Mace', 'Weapon', 22, 0),
    (106, 'Leather Armor', 'Armor', 0, 10),
    (107, 'Chainmail', 'Armor', 0, 15),
    (108, 'Robe', 'Armor', 0, 1),
    (109, 'Leather Gloves', 'Accessory', 0, 5),
    (110, 'Healing Potion', 'Consumable', 0, 0);
    
INSERT INTO Characters (id, name, class, level, equip)
VALUES
    (1, 'Arken', 'Warrior', 10, 101),
    (2, 'Rofl', 'Archer', 8, 102),
    (3, 'Malf', 'Mage', 12, 110),
    (4, 'Rando', 'Rogue', 9, 109),
    (5, 'Hest', 'Cleric', 7, NULL),
    (6, 'Malve', 'Warrior', 4, 104),
    (7, 'Arrow', 'Archer', 15, 102);