Multi-table queries retrieve data from multiple tables in a single SQL statement. While single-table queries use the basic syntax SELECT field_list FROM table_name, multi-table queries simply separate table names with commas.

For example, to query from both employee and department tables:

SELECT * FROM employees, departments;

This query produces a result set containing all possible combinations between the two tables. If the employees table has 17 rows and the departments table has 5 rows, the result will contain 85 records (17 × 5 = 85). This phenomenon is known as the Cartesian product.

Understanding Cartesian Products

In set theory, a Cartesian product represents all possible combinations between two sets (Table A and Table B). In database queries, this means every row from the first table is combined with every row from the second table.

To eliminate无效的笛卡尔积 (invalid Cartesian product), you must add join conditions that relate the tables:

SELECT * FROM employees, departments 
WHERE employees.department_id = departments.id;

Classification of Multi-Table Queries

Multi-table queries are categorized into three main types:

1. Join Queries
   • Inner Join: Retrieves data from the intersection of tables A and B
2. Outer Queries
   • Left Outer Join: Retrieves all data from the left table (including intersection)
   • Right Outer Join: Retrieves all data from the right table (including intersection)
3. Subqueries: Nested queries within the main query

Inner Join

Inner join queries retrieve only the intersecting data between two or more tables. There are two syntax variants:

Implicit Inner Join

SELECT field_list 
FROM table1, table2 
WHERE condition;

Explicit Inner Join

SELECT field_list 
FROM table1 [INNER] JOIN table2 ON condition;

Practical Example: Retrieve employee names along with their department names.

-- Implicit syntax
SELECT employees.name, departments.name
FROM employees, departments
WHERE employees.department_id = departments.id;

-- Explicit syntax
SELECT employees.name, departments.name
FROM employees INNER JOIN departments
ON employees.department_id = departments.id;

Using Table Aliases

Aliases simplify multi-table queries and improve readability:

SELECT e.name, d.name
FROM employees e INNER JOIN departments d
ON e.department_id = d.id;

Important: Once you assign an alias to a table, you cannot reference columns using the original table name. All column references must use the alias.

Outer Joins

Outer joins retrieve all data from one table along with matching data from the other table.

Left Outer Join

SELECT field_list 
FROM table1 LEFT [OUTER] JOIN table2 ON condition;

This retrieves all records from the left table, including records that have no match in the right table.

Right Outer Join

SELECT field_list 
FROM table1 RIGHT [OUTER] JOIN table2 ON condition;

This retrieves all records from the right table, including records that have no match in the left table.

Practical Example: Retrieve all employee names and their corresponding department names, including employees without department assignments:

SELECT e.name, d.name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.id;

Note: Left and right outer joins are interchangeable by simply changing the order of tables in the query. Left outer joins are more commonly used in practice.

Subqueries

A subquery is a nested SELECT statement embedded within another SQL statement. The outer statement can be INSERT, UPDATE, DELETE, or SELECT.

SELECT * FROM table1 
WHERE column1 = (SELECT column1 FROM table2);

Subqueries are classified based on their return values:

1. Scalar Subquery: Returns a single value (one row, one column)
2. Column Subquery: Returns a single column (multiple rows)
3. Row Subquery: Returns a single row (multiple columns)
4. Table Subquery: Returns multiple rows and columns (acts as a temporary table)

Subqueries can appear in WHERE, FROM, or SELECT clauses.

Scalar Subqueries

A scalar subquery returns a single value such as a number, string, or date.

Example 1: Find all employees in the "Sales" department

-- Step 1: Find the department ID for "Sales"
SELECT id FROM departments WHERE name = 'Sales';  -- Result: 5

-- Step 2: Find employees in department 5
SELECT * FROM employees WHERE department_id = 5;

-- Combined query
SELECT * FROM employees 
WHERE department_id = (
    SELECT id FROM departments WHERE name = 'Sales'
);

Example 2: Find all employees hired after "John Smith"

-- Step 1: Find John Smith's hire date
SELECT hire_date FROM employees WHERE name = 'John Smith';  -- Result: 2015-03-15

-- Step 2: Find employees hired after this date
SELECT * FROM employees WHERE hire_date > '2015-03-15';

-- Combined query
SELECT * FROM employees 
WHERE hire_date > (
    SELECT hire_date FROM employees WHERE name = 'John Smith'
);

Column Subqueries

A column subquery returns one column with multiple rows. Common operators include IN and NOT IN.

Example: Find all employees in the "Sales" and "Marketing" departments

-- Step 1: Find department IDs for Sales and Marketing
SELECT id FROM departments 
WHERE name = 'Sales' OR name = 'Marketing';  -- Results: 5, 3

-- Step 2: Find employees in these departments
SELECT * FROM employees WHERE department_id IN (5, 3);

-- Combined query
SELECT * FROM employees 
WHERE department_id IN (
    SELECT id FROM departments 
    WHERE name = 'Sales' OR name = 'Marketing'
);

Row Subqueries

A row subquery returns a single row with multiple columns. Common operators include =, <>, IN, and NOT IN.

Example: Find employees who have the same hire date and position as "Alice Johnson"

-- Step 1: Find Alice Johnson's hire date and position
SELECT hire_date, position FROM employees WHERE name = 'Alice Johnson';  
-- Result: 2018-06-20, 3

-- Step 2: Find matching employees
SELECT * FROM employees 
WHERE (hire_date, position) = ('2018-06-20', 3);

-- Combined query
SELECT * FROM employees 
WHERE (hire_date, position) = (
    SELECT hire_date, position FROM employees WHERE name = 'Alice Johnson'
);

Table Subqueries

A table subquery returns multiple rows and columns, functioning as a temporary table within the FROM clause.

Example: Find all employees hired after "2019-01-01" along with their department information

-- Get employees hired after 2019
SELECT * FROM employees WHERE hire_date > '2019-01-01';

-- Join with departments using subquery as temporary table
SELECT e.*, d.* 
FROM (
    SELECT * FROM employees WHERE hire_date > '2019-01-01'
) e 
LEFT JOIN departments d ON e.department_id = d.id;