Mastering Multi-Table Queries in MySQL: Joins, Subqueries, and Set Operations

1. INNER/LEFT/RIGHT JOIN Deep Dive

In MySQL, JOIN combines rows from two or more tables based on a condition. Types:

INNER JOIN – returns rows with matching values in both tables.

LEFT JOIN – returns all rows from the left table and matching rows from the right table; unmatched right rows are NULL.

RIGHT JOIN – returns all rows from the right table and matching rows from the left table; unmatched left rows are NULL.

FULL JOIN – returns all rows from both tables.

SELF JOIN – joins a table with itself.

1.1 JOIN Basics

-- Create example tables
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    dept_id INT
);
CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(50)
);
-- Insert example data
INSERT INTO departments VALUES
(1, '技术部'), (2, '市场部'), (3, '人事部');
INSERT INTO users VALUES
(1, '张三', 1),
(2, '李四', 2),
(3, '王五', 1),
(4, '赵六', NULL);

1.2 INNER JOIN

-- Basic INNER JOIN
SELECT u.name, d.dept_name
FROM users u
INNER JOIN departments d ON u.dept_id = d.id;

-- Equivalent traditional syntax
SELECT u.name, d.dept_name
FROM users u, departments d
WHERE u.dept_id = d.id;

1.3 LEFT JOIN

-- LEFT JOIN returns all left‑table rows; unmatched right rows are NULL
SELECT u.name, d.dept_name
FROM users u
LEFT JOIN departments d ON u.dept_id = d.id;

-- Find users without a department
SELECT u.name
FROM users u
LEFT JOIN departments d ON u.dept_id = d.id
WHERE d.id IS NULL;

1.4 RIGHT JOIN

-- RIGHT JOIN returns all right‑table rows; unmatched left rows are NULL
SELECT u.name, d.dept_name
FROM users u
RIGHT JOIN departments d ON u.dept_id = d.id;

-- Find departments without users
SELECT d.dept_name
FROM users u
RIGHT JOIN departments d ON u.dept_id = d.id
WHERE u.id IS NULL;

1.5 Multi‑Table JOIN Example

CREATE TABLE salaries (
    user_id INT,
    salary DECIMAL(10,2),
    month DATE
);
-- Three‑table join
SELECT u.name, d.dept_name, s.salary
FROM users u
INNER JOIN departments d ON u.dept_id = d.id
INNER JOIN salaries s ON u.id = s.user_id
WHERE s.month = '2024-01-01';

2. Subqueries: Correlated and Non‑Correlated

2.1 Non‑Correlated Subqueries

-- In WHERE clause
SELECT name FROM users
WHERE dept_id IN (
    SELECT id FROM departments
    WHERE dept_name LIKE '%技术%'
);

-- In SELECT clause
SELECT name,
       (SELECT dept_name FROM departments WHERE id = users.dept_id) AS dept_name
FROM users;

-- In FROM clause (derived table)
SELECT * FROM (
    SELECT dept_id, COUNT(*) AS user_count
    FROM users
    GROUP BY dept_id
) AS dept_stats
WHERE user_count > 1;

2.2 Correlated Subqueries

-- Find the highest‑paid user per department
SELECT s1.user_id, s1.salary
FROM salaries s1
WHERE s1.salary = (
    SELECT MAX(s2.salary)
    FROM salaries s2
    WHERE s2.user_id = s1.user_id
);

-- EXISTS example
SELECT d.dept_name
FROM departments d
WHERE EXISTS (
    SELECT 1 FROM users u WHERE u.dept_id = d.id
);

-- NOT EXISTS example
SELECT d.dept_name
FROM departments d
WHERE NOT EXISTS (
    SELECT 1 FROM users u WHERE u.dept_id = d.id
);

3. UNION and UNION ALL

3.1 UNION Basics

-- UNION (removes duplicates)
SELECT name FROM users WHERE dept_id = 1
UNION
SELECT name FROM users WHERE dept_id = 2;

-- UNION ALL (keeps duplicates)
SELECT name FROM users WHERE dept_id = 1
UNION ALL
SELECT name FROM users WHERE dept_id = 1;

-- Multi‑table UNION
SELECT name, '用户' AS type FROM users
UNION
SELECT dept_name, '部门' AS type FROM departments;

3.2 UNION Usage Rules

-- Column count and data types must match
SELECT id, name FROM users
UNION
SELECT id, dept_name FROM departments;

-- ORDER BY sorts the whole result set
SELECT name FROM users WHERE dept_id = 1
UNION
SELECT name FROM users WHERE dept_id = 2
ORDER BY name DESC;

4. Query Performance Comparison

4.1 Create Test Tables and Large Data

-- Create test tables
CREATE TABLE test_a (
    id INT PRIMARY KEY,
    value VARCHAR(100),
    INDEX idx_value(value)
);
CREATE TABLE test_b (
    id INT PRIMARY KEY,
    a_id INT,
    data VARCHAR(100),
    INDEX idx_a_id(a_id)
);
-- Insert 1,000,000 rows (omitted)

4.2 JOIN vs Subquery Performance

Case 1: JOIN is usually faster

-- JOIN version
EXPLAIN ANALYZE
SELECT a.value, b.data
FROM test_a a
INNER JOIN test_b b ON a.id = b.a_id
WHERE a.value LIKE 'test%';

-- Subquery version (slower)
EXPLAIN ANALYZE
SELECT (
    SELECT value FROM test_a WHERE id = b.a_id
) AS value,
    b.data
FROM test_b b
WHERE EXISTS (
    SELECT 1 FROM test_a a WHERE a.id = b.a_id AND a.value LIKE 'test%'
);

Case 2: EXISTS vs IN

-- EXISTS (correlated subquery)
EXPLAIN ANALYZE
SELECT a.value
FROM test_a a
WHERE EXISTS (
    SELECT 1 FROM test_b b WHERE b.a_id = a.id
);

-- IN (non‑correlated subquery)
EXPLAIN ANALYZE
SELECT a.value
FROM test_a a
WHERE a.id IN (
    SELECT a_id FROM test_b
);

4.3 UNION Performance Tips

-- Inefficient: complex query in each UNION branch
SELECT * FROM (
    SELECT * FROM users WHERE name LIKE '张%'
) AS t1
UNION
SELECT * FROM (
    SELECT * FROM users WHERE dept_id = 1
) AS t2;

-- Efficient: simplify each branch
SELECT * FROM users WHERE name LIKE '张%'
UNION
SELECT * FROM users WHERE dept_id = 1;

4.4 Additional Optimization Techniques

Index optimization:

-- Create indexes on join columns
CREATE INDEX idx_users_dept ON users(dept_id);
CREATE INDEX idx_departments_id ON departments(id);

Avoid functions in join conditions:

-- Bad
SELECT * FROM users u
JOIN departments d ON UPPER(u.name) = UPPER(d.dept_name);

-- Good
SELECT * FROM users u
JOIN departments d ON u.name = d.dept_name;

Limit result sets:

SELECT u.name, d.dept_name
FROM users u
LEFT JOIN departments d ON u.dept_id = d.id
LIMIT 100;

4.5 Performance Comparison Chart

Other Recommendations

Practice first: run the queries in a test environment.

Analyze execution plans: use EXPLAIN and EXPLAIN ANALYZE to understand query execution.

Progress gradually: start with simple queries and increase complexity step by step.

Performance testing: benchmark with realistic data volumes to see actual costs.

Version features: be aware of differences between MySQL versions (e.g., 5.7 vs 8.0).