Practice: Multi-Table Query Comprehensive

Q: Q: Can SELECT after GROUP BY only contain aggregate functions and grouped columns?

A: Yes. Non-aggregated columns in SELECT must all appear in GROUP BY, otherwise the database will throw an error. This is a SQL standard requirement.

📋 Project Requirements

This section uses an e-commerce order management system scenario to comprehensively apply the multi-table query skills learned previously.

Database Table Structure

We use a unified 4-table database:

SQL

-- Departments table
CREATE TABLE departments (
    department_id   INT PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL,
    city            VARCHAR(50),
    budget          DECIMAL(12,2)
);

-- Employees table
CREATE TABLE employees (
    employee_id   INT PRIMARY KEY,
    first_name    VARCHAR(50) NOT NULL,
    last_name     VARCHAR(50) NOT NULL,
    email         VARCHAR(100),
    salary        DECIMAL(10,2),
    department_id INT,
    hire_date     DATE,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

-- Orders table
CREATE TABLE orders (
    order_id     INT PRIMARY KEY,
    customer_id  INT,
    employee_id  INT,
    order_date   DATE,
    total_amount DECIMAL(12,2),
    status       VARCHAR(20),
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

-- Products table
CREATE TABLE products (
    product_id   INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price        DECIMAL(10,2),
    stock        INT,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Test Data

SQL

-- Department data
INSERT INTO departments VALUES (1, 'Technology', 'Beijing', 500000);
INSERT INTO departments VALUES (2, 'Sales', 'Shanghai', 300000);
INSERT INTO departments VALUES (3, 'Marketing', 'Guangzhou', 200000);
INSERT INTO departments VALUES (4, 'Finance', 'Shenzhen', 150000);

-- Employee data
INSERT INTO employees VALUES (1, 'Zhang', 'Wei', 'zhangwei@co.com', 12000, 1, '2023-03-15');
INSERT INTO employees VALUES (2, 'Li', 'Na', 'lina@co.com', 9500, 1, '2023-06-20');
INSERT INTO employees VALUES (3, 'Wang', 'Qiang', 'wangqiang@co.com', 8000, 2, '2022-11-10');
INSERT INTO employees VALUES (4, 'Zhao', 'Min', 'zhaomin@co.com', 11000, 2, '2024-01-05');
INSERT INTO employees VALUES (5, 'Liu', 'Yang', 'liuyang@co.com', 7500, 3, '2024-04-18');
INSERT INTO employees VALUES (6, 'Chen', 'Jing', 'chenjing@co.com', 9000, NULL, '2025-02-28');

-- Order data
INSERT INTO orders VALUES (1001, 101, 3, '2026-05-10', 2500.00, 'completed');
INSERT INTO orders VALUES (1002, 102, 3, '2026-05-15', 1800.00, 'completed');
INSERT INTO orders VALUES (1003, 103, 4, '2026-06-01', 3200.00, 'shipped');
INSERT INTO orders VALUES (1004, 101, 1, '2026-06-05', 950.00, 'pending');
INSERT INTO orders VALUES (1005, 104, 4, '2026-06-10', 4100.00, 'completed');
INSERT INTO orders VALUES (1006, 105, 3, '2026-06-15', 2800.00, 'cancelled');

-- Product data
INSERT INTO products VALUES (1, 'Laptop', 6999.00, 50, 1);
INSERT INTO products VALUES (2, 'Wireless Mouse', 129.00, 200, 1);
INSERT INTO products VALUES (3, 'Office Desk', 899.00, 30, 2);
INSERT INTO products VALUES (4, 'Projector', 3500.00, 15, 3);
INSERT INTO products VALUES (5, 'Printer', 2200.00, 25, 1);

🏗️ Query Practice

Query 1: Employee Order Performance Report

Requirement: View each employee's order count and total sales, including employees with no orders.

SQL

SELECT
    e.employee_id,
    CONCAT(e.first_name, e.last_name) AS name,
    d.department_name AS department,
    COUNT(o.order_id) AS order_count,
    COALESCE(SUM(o.total_amount), 0) AS total_sales
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
LEFT JOIN orders o ON e.employee_id = o.employee_id
GROUP BY e.employee_id, e.first_name, e.last_name, d.department_name
ORDER BY total_sales DESC;

Result:

TEXT

employee_id | name     | department | order_count | total_sales
-----------+----------+------------+-------------+------------
         4 | Zhao Min | Sales      |           2 |     7300.00
         3 | Wang Qiang| Sales      |           3 |     7100.00
         1 | Zhang Wei | Technology |           1 |      950.00
         2 | Li Na    | Technology |           0 |        0.00
         5 | Liu Yang | Marketing  |           0 |        0.00
         6 | Chen Jing| NULL       |           0 |        0.00

Code Explanation:

LEFT JOIN departments — Join the departments table to get department names, retaining employees without a department
LEFT JOIN orders — Join the orders table, retaining employees with no orders
COALESCE(SUM(...), 0) — Converts NULL to 0 to avoid displaying blanks
GROUP BY — Groups by employee, aggregating order count and total amount

Query 2: Department Sales Rankings

Requirement: Calculate total sales by department and rank them.

SQL

SELECT
    d.department_name AS department,
    d.city AS city,
    COUNT(DISTINCT e.employee_id) AS employee_count,
    COUNT(o.order_id) AS total_orders,
    COALESCE(SUM(o.total_amount), 0) AS total_sales,
    RANK() OVER (ORDER BY COALESCE(SUM(o.total_amount), 0) DESC) AS ranking
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
LEFT JOIN orders o ON e.employee_id = o.employee_id
GROUP BY d.department_id, d.department_name, d.city
ORDER BY total_sales DESC;

Code Explanation:

LEFT JOIN chain: departments → employees → orders
COUNT(DISTINCT e.employee_id) — Deduplicates employee count to avoid counting duplicates from multiple orders
RANK() — Window function that ranks by total sales
COALESCE — Handles departments with no orders, displaying 0

Query 3: High-Salary Employees and Their Order Details

Requirement: Find employees whose salary is above the company average, along with their order information.

SQL

SELECT
    e.employee_id,
    CONCAT(e.first_name, e.last_name) AS name,
    e.salary AS salary,
    o.order_id,
    o.order_date AS order_date,
    o.total_amount AS order_amount,
    o.status AS order_status
FROM employees e
INNER JOIN orders o ON e.employee_id = o.employee_id
WHERE e.salary > (
    SELECT AVG(salary) FROM employees
)
ORDER BY e.salary DESC, o.order_date;

Code Explanation:

Subquery (SELECT AVG(salary) FROM employees) — Calculates company average salary
INNER JOIN — Retains only high-salary employees who have orders
WHERE e.salary > (...) — Uses subquery result as filter condition
ORDER BY sorts by salary descending first, then by date ascending

Query 4: Cross Query — Orders Without Corresponding Department Products

Requirement: Find orders where the employee's department has no products.

SQL

SELECT
    o.order_id,
    CONCAT(e.first_name, e.last_name) AS employee_name,
    d.department_name AS department,
    o.total_amount AS order_amount
FROM orders o
INNER JOIN employees e ON o.employee_id = e.employee_id
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE e.department_id IS NOT NULL
  AND e.department_id NOT IN (
      SELECT DISTINCT department_id FROM products WHERE department_id IS NOT NULL
  );

Code Explanation:

INNER JOIN employees — Joins orders and employees
LEFT JOIN departments — Gets department names
NOT IN (subquery) — Excludes departments that have products
Combines JOIN and subquery for mixed filtering

Query 5: Use UNION to Merge Multi-Dimensional Data

Requirement: Generate a comprehensive directory merging "employees with orders" and "all departments."

SQL

-- Employees with completed orders
SELECT
    CONCAT(first_name, last_name) AS name,
    'Employee' AS type,
    CAST(salary AS VARCHAR) AS details
FROM employees
WHERE employee_id IN (
    SELECT DISTINCT employee_id FROM orders WHERE status = 'completed'
)
UNION ALL
-- All departments
SELECT
    department_name,
    'Department',
    CONCAT('Budget: ', CAST(budget AS VARCHAR))
FROM departments
ORDER BY type, name;

Code Explanation:

First SELECT — Uses subquery to filter employees with completed orders
Second SELECT — Gets all departments
UNION ALL — Merges two result sets, retaining all rows
Adds a constant column "type" to distinguish data sources

Query 6: Comprehensive Analysis — Order Status Distribution and Employee Performance

Requirement: Calculate order status distribution for each employee and find employees who have handled "completed" orders.

SQL

SELECT
    CONCAT(e.first_name, e.last_name) AS employee_name,
    d.department_name AS department,
    SUM(CASE WHEN o.status = 'completed' THEN 1 ELSE 0 END) AS completed,
    SUM(CASE WHEN o.status = 'shipped' THEN 1 ELSE 0 END) AS shipped,
    SUM(CASE WHEN o.status = 'pending' THEN 1 ELSE 0 END) AS pending,
    SUM(CASE WHEN o.status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
    COUNT(o.order_id) AS total_orders
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
LEFT JOIN orders o ON e.employee_id = o.employee_id
WHERE e.employee_id IN (
    SELECT DISTINCT employee_id FROM orders
)
GROUP BY e.employee_id, e.first_name, e.last_name, d.department_name
ORDER BY total_orders DESC;

Code Explanation:

CASE WHEN conditional aggregation — Converts row data into columns (pivot)
LEFT JOIN retains all associated data
Subquery IN (...) filters out employees with no orders
GROUP BY groups and aggregates by employee

❓ FAQ

Q: When should I use INNER JOIN vs LEFT JOIN? A: If you need to retain all records from the left table (even when the right table has no match), use LEFT JOIN. If you only need records that match in both tables, use INNER JOIN. In practice, report queries typically use LEFT JOIN, while data validation uses INNER JOIN.

Q: Which performs better, subquery or JOIN? A: Modern database optimizers typically convert subqueries to JOINs for execution. For simple associations, JOIN is more intuitive; for existence checks ("does it exist"), EXISTS or IN subqueries are more semantic. Use EXPLAIN to verify performance differences.

Q: How do I debug complex multi-table queries? A: Build incrementally: start with the innermost subquery, verify the result is correct, then wrap it layer by layer. You can also use CTEs (WITH clause) to break complex queries into multiple readable temporary result sets.

Q: Can SELECT after GROUP BY only contain aggregate functions and grouped columns? A: Yes. Non-aggregated columns in SELECT must all appear in GROUP BY, otherwise the database will throw an error. This is a SQL standard requirement.

📖 Summary

Skill	Application in This Section
INNER JOIN	Associate tables with matching relationships
LEFT JOIN	Retain all records from the left table
Subquery (WHERE IN)	Used as filter condition
Subquery (scalar)	Returns a single value for comparison
UNION ALL	Merge result sets from different dimensions
CASE WHEN	Conditional aggregation (pivot)
Aggregate Functions	COUNT, SUM, AVG, COALESCE
Window Functions	RANK() for ranking

Key Practice Points:

Build complex queries incrementally from simple to complex
Ensure single-table queries are correct before adding multi-table joins
Use COALESCE to handle NULL values
Use EXPLAIN to analyze query performance

📝 Exercises

Write a query: Find the department information of the employee with the highest salary, including department name, city, and budget.
Write a query: Count the number of departments and total employees per city, using LEFT JOIN to ensure cities without employees are also displayed.
Write a query: Find employees who have never processed an order, implementing it both with subquery (NOT IN) and LEFT JOIN ... IS NULL, and compare whether the results are consistent.
Write a query: Divide employees into three groups by salary—"High (>10000)", "Medium (6000-10000)", "Low (<6000)"—and count the number of people and average salary for each group.

Next Lesson

Next we'll learn Aggregate Functions — master COUNT, SUM, AVG, MAX, MIN, as well as advanced usage of GROUP BY and HAVING.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

Practice: Multi-Table Query Comprehensive

Practice: Multi-Table Query Comprehensive

📋 Project Requirements

Database Table Structure

Test Data

🏗️ Query Practice

Query 1: Employee Order Performance Report

Query 2: Department Sales Rankings

Query 3: High-Salary Employees and Their Order Details

Query 4: Cross Query — Orders Without Corresponding Department Products

Query 5: Use UNION to Merge Multi-Dimensional Data

Query 6: Comprehensive Analysis — Order Status Distribution and Employee Performance

❓ FAQ

📖 Summary

📝 Exercises

Next Lesson

🙏 帮我们做得更好