Multi-table queries, also known as join queries, retrieve data from two or more related tables simultaneously. This requires the tables to be logically connected through common fields, such as a shared department ID, which may or may not be a formal foreign key.

Understanding Cartesian Product Issues

A common error when querying multiple tables with out proper join conditions is the Cartesian product, where every row from one table is combined with every row from another. This results in a number of rows equal to the product of the row counts. For example:

SELECT employee_last_name, department_name FROM staff, departments;

This query combines all 107 staff rows with all 27 department rows, producing 2889 rows, most of wich are meaningless combinations.

To prevent this, a WHERE clause must specify the join condition:

SELECT employee_last_name, department_name
FROM staff, departments
WHERE staff.dept_code = departments.dept_code;

Classification of Multi-Table Queries

1. Equi-Join vs. Non-Equi-Join An equi-join uses equality in the join condition. When column names are identical across tables, table aliases or prefixes are necessary.

SELECT s.emp_id, s.last_name, s.dept_code,
       d.dept_code, d.location_id
FROM staff s, departments d
WHERE s.dept_code = d.dept_code;

A non-equi-join uses other operators, such as BETWEEN.

SELECT e.emp_name, e.salary, g.grade
FROM employees e, salary_grades g
WHERE e.salary BETWEEN g.min_salary AND g.max_salary;

2. Self-Join vs. Non-Self-Join A self-join connects a table to itself using aliases, often to model hierarchical relationships like employee-manager.

SELECT w.emp_name, ' reports to ', m.emp_name
FROM personnel w, personnel m
WHERE w.manager_id = m.emp_id;

3. Inner Join vs. Outer Join An inner join returns only matching rows. An outer join returns all rows from one table (the primary) with matching rows from the other; unmatched rows from the primary are included with NULLs from the secondary.

• Left Outer Join: All rows from the left table.
• Right Outer Join: All rows from the right table.
• Full Outer Join: All rows from both tables (MySQL emulates via UNION).

SQL99 Syntax for Joins

SQL99 uses explicit JOIN...ON syntax, improving readability, especially for complex queries.

Inner Join:

SELECT s.emp_id, s.last_name, s.dept_code,
       d.dept_code, d.location_id
FROM staff s INNER JOIN departments d
ON s.dept_code = d.dept_code;

Left Outer Join:

SELECT s.last_name, s.dept_code, d.dept_name
FROM staff s LEFT OUTER JOIN departments d
ON s.dept_code = d.dept_code;

Right Outer Join:

SELECT s.last_name, s.dept_code, d.dept_name
FROM staff s RIGHT OUTER JOIN departments d
ON s.dept_code = d.dept_code;

Full Outer Join (Emulated in MySQL):

SELECT emp_id, last_name, dept_name
FROM staff s LEFT JOIN departments d ON s.dept_code = d.dept_code
UNION ALL
SELECT emp_id, last_name, dept_name
FROM staff s RIGHT JOIN departments d ON s.dept_code = d.dept_code
WHERE s.dept_code IS NULL;

Using UNION and UNION ALL

The UNION operator combines result sets, removing duplicates. UNION ALL includes all rows, including duplicates, and is more efficient when duplicates are not an issue.

SELECT id, name FROM china_male WHERE gender='M'
UNION ALL
SELECT id, name FROM usa_male WHERE gender='male';

Special SQL99 Join Features

NATURAL JOIN: Automatically joins tables on columns with identical names. Use with caution as it joins on all common columns.

SELECT emp_id, last_name, dept_name
FROM staff NATURAL JOIN departments;

USING Clause: Simplifies syntax when the join column has the same name in both tables.

SELECT emp_id, last_name, dept_name
FROM staff JOIN departments USING (dept_code);

Single-Row Functions

MySQL's built-in functions operate on individual data items per row.

Numeric Functions:

SELECT ABS(-15), CEIL(3.14), FLOOR(3.14), ROUND(15.193, 1), TRUNCATE(15.79, 1);

String Functions:

SELECT CONCAT('Hello', ' ', 'World'), LENGTH('MySQL'), UPPER('mysql'),
       SUBSTR('Database', 1, 4), REPLACE('foo bar', 'foo', 'new');

Date and Time Functions:

SELECT CURDATE(), CURTIME(), NOW(),
       DATE_ADD(CURDATE(), INTERVAL 7 DAY),
       DATEDIFF('2023-12-31', CURDATE());

Control Flow Functions:

SELECT emp_name, salary,
       CASE
           WHEN salary > 10000 THEN 'High'
           WHEN salary > 5000 THEN 'Medium'
           ELSE 'Standard'
       END AS salary_level
FROM employees;

Aggregate Functions

Aggregate functions summarize data across multiple rows, returning a single value. They are often used with GROUP BY.

SELECT dept_code, AVG(salary), MAX(salary), COUNT(*)
FROM employees
GROUP BY dept_code
HAVING AVG(salary) > 7500;

Common aggregate functions include AVG(), SUM(), MAX(), MIN(), and COUNT(). Note that COUNT(*) includes rows with NULLs, while COUNT(column) does not.

Subqueries

A subquery is a SELECT statement nested inside another query.

Single-Row Subquery: Returns one row. Use with single-row comparison operators (=, >, <, etc.).

SELECT emp_name, salary
FROM employees
WHERE salary > (SELECT salary FROM employees WHERE emp_id = 101);

Multiple-Row Subquery: Returns multiple rows. Use with operators like IN, ANY, ALL.

SELECT emp_id, emp_name, job_title
FROM employees
WHERE dept_code IN (SELECT dept_code FROM departments WHERE location_id = 1700);

Correlated Subquery: Executed once for each row processed by the outer query because it references a column from the outer query.

SELECT e1.emp_name, e1.salary, e1.dept_code
FROM employees e1
WHERE salary > (SELECT AVG(salary)
                FROM employees e2
                WHERE e2.dept_code = e1.dept_code);

EXISTS / NOT EXISTS: Used to test for the existence of rows returned by a subquery.

SELECT dept_code, dept_name
FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_code = d.dept_code);

Creating and Managing Tables

Creating a Database and Table:

CREATE DATABASE IF NOT EXISTS company_db CHARACTER SET utf8mb4;
USE company_db;

CREATE TABLE IF NOT EXISTS project_teams (
    team_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    team_name VARCHAR(50) NOT NULL,
    manager_id INT,
    start_date DATE,
    budget DECIMAL(12,2),
    notes TEXT
);

Altering a Table:

ALTER TABLE project_teams
ADD COLUMN status ENUM('Active', 'On-Hold', 'Completed') DEFAULT 'Active',
CHANGE COLUMN budget project_budget DECIMAL(14,2),
MODIFY COLUMN team_name VARCHAR(75);

Dropping and Truncating Tables: DROP TABLE removes the table structure and data. TRUNCATE TABLE removes all data but keeps the structure, which is faster than DELETE but cannnot be rolled back.

DROP TABLE IF EXISTS old_backup;
TRUNCATE TABLE temp_data;

Inserting Data:

INSERT INTO project_teams (team_name, manager_id, start_date)
VALUES ('Development Alpha', 105, '2023-11-01'),
       ('Research Beta', 108, '2023-11-15');

Inserting from a query:

INSERT INTO high_performers (emp_id, emp_name, salary)
SELECT emp_id, last_name, salary FROM employees WHERE rating = 'A';

Updating Data:

UPDATE project_teams
SET project_budget = project_budget * 1.1,
    status = 'Active'
WHERE status = 'On-Hold' AND start_date < '2023-06-01';

Deleting Data:

DELETE FROM audit_log WHERE log_date < '2022-01-01';

MySQL 8.0 Generated Columns: A column whose value is automatically calculated from an expression involving other columns.

CREATE TABLE order_details (
    order_id INT,
    unit_price DECIMAL(10,2),
    quantity INT,
    total_price DECIMAL(10,2) GENERATED ALWAYS AS (unit_price * quantity) STORED
);