Multi-Table Joins

Outer Joins

Outer joins extend result sets beyond matching rows by preserving unmatched records from one or both sides.

• LEFT JOIN: Returns all rows from the left table and matched rows from the right; unmatched right-side entries appear as NULL.
• RIGHT JOIN: Returns all rows from the right table and matched rows from the left; unmatched left-side entries appear as NULL.

-- Retrieve student names, addresses, and associated grade names, including grades without students
SELECT s.name, s.home_address, g.level_name
FROM grade g
LEFT JOIN student s ON s.grade_id = g.id;

-- Retrieve same data but include all students—even those without assigned grades
SELECT s.name, s.home_address, g.level_name
FROM student s
RIGHT JOIN grade g ON s.grade_id = g.id;

Self-Joins with Hierarchical Data

A self-join links a table to itself using foreign key relationships—commonly used for tree-like structures (e.g., categories and subcategories).

Given a category table:

+----+------+----------------+
| id | pid  | name           |
+----+------+----------------+
| 1  | NULL | Curriculum     |
| 2  | 1    | Graphic Design |
| 3  | 1    | Software Dev   |
| 4  | 3    | Database Fund. |
| 5  | 2    | Photoshop      |
+----+------+----------------+

To list parent-child category pairs:

SELECT parent.name AS parent_category,
       child.name AS subcategory
FROM category parent
INNER JOIN category child ON parent.id = child.pid;

Subqueries

Subqueries embed one SELECT statement inside another, enabling conditional filtering based on dynamic results. They must be enclosed in parentheses and may appear in WHERE, FROM, or SELECT clauses.

Key operators include IN, NOT IN, =, <>, and comparison logic.

Example 1: Fetch names and addresses of male students enrolled in the "Freshman" cohort.

SELECT name, home_address
FROM student
WHERE grade_id = (
    SELECT id FROM grade WHERE level_name = 'Freshman'
)
AND gender = 'M';

Example 2: Compute the average score for students in "Freshman" taking "Calculus I".

SELECT AVG(r.score)
FROM result r
WHERE r.subject_id = (
    SELECT id FROM subject WHERE title = 'Calculus I'
)
AND r.student_id IN (
    SELECT id FROM student WHERE grade_id = (
        SELECT id FROM grade WHERE level_name = 'Freshman'
    )
);

Aggregate Functions

Aggregate functions operate across groups of rows and return a single scalar value per group.

All aggregates except COUNT() skip NULLs automatically.

COUNT(*) vs COUNT(1) vs COUNT(column)

• COUNT(*): Counts all rows—including those with NULLs in any column. Optimizer may use metadata or index statistics.
• COUNT(1): Also counts all rows. No semantic difference from COUNT(*); performance varies slightly by engine and indexing strategy.
• COUNT(column): Counts only rows where column IS NOT NULL.

Efficiency ranking depends on storage engine and index availability:

• With a primary key: COUNT(pk_column) often performs best.
• Without indexes: COUNT(*) and COUNT(1) are typically equivalent and faster than COUNT(column).

Numeric Functions

Generate a random integer between 0 and 99999:

SELECT FLOOR(RAND() * 100000);

String Functions

Date and Time Functions

Calculate age in years for a student named 'Alice':

SELECT FLOOR(DATEDIFF(NOW(), birth_date) / 365.25) AS age
FROM student
WHERE name = 'Alice';

Conditional Logic Functions

Examples:

-- Flag email presence
SELECT name, IF(email IS NULL, 'Missing', 'Provided') AS email_status
FROM student;

-- Provide fallback for missing emails
SELECT name, IFNULL(email, 'Not supplied') AS contact_email
FROM student;

-- Apply custom multipliers to scores by student ID
SELECT student_id,
       score,
       CASE student_id
           WHEN 1000 THEN score * 1.5
           WHEN 1001 THEN score * 1.3
           WHEN 1002 THEN score * 1.1
           ELSE score
       END AS adjusted_score
FROM result
WHERE subject_id = (
    SELECT id FROM subject WHERE title = 'Calculus I'
);