Mastering SQL Joins: A Practical Guide for beginners.

#datascience #sql

Introduction

SQL joins allow you to combine data from multiple tables based on relationships between them. In this article, we’ll explore different types of joins using this two schemas:

City Hospital Schema
Nairobi Academy Schema

What is a SQL Join?

A join retrieves data from two or more tables using a related column.

Basic Syntax

SELECT columns
FROM table1
JOIN table2
ON table1.column = table2.column;

1. INNER JOIN
Returns only records that exist in both tables.

Example (City Hospital schema)

SELECT p.full_name AS patient_name,
    d.full_name AS doctor_name,
    a.appointment_date,
    a.diagnosis
FROM appointments a
INNER JOIN patients p 
    ON a.patient_id = p.patient_id
INNER JOIN doctors d 
    ON a.doctor_id = d.doctor_id;

Only matching records appear while missing relationships are excluded

2. LEFT JOIN
Returns all records from the left table and matches from the right.
Example

SELECT 
    p.full_name,a.appointment_date,a.diagnosis
FROM patients p
LEFT JOIN appointments a 
    ON p.patient_id = a.patient_id;

All patients are shown while missing appointments appear as NULL
This shows all primary records, even if incomplete

3. RIGHT JOIN
Returns all records from the right table.
Example

SELECT d.full_name,a.appointment_date,a.diagnosis
FROM appointments a
RIGHT JOIN doctors d 
    ON a.doctor_id = d.doctor_id;

All doctors appear while missing appointments show NULL
The Focus on the right-side table.

4. FULL OUTER JOIN
Returns all records from both tables, matched or not.

SELECT 
    p.full_name,
    a.appointment_date
FROM patients p
FULL OUTER JOIN appointments a 
    ON p.patient_id = a.patient_id;

Combines LEFT + RIGHT JOIN behavior
You will see everything, including gaps.

5. CROSS JOIN
Returns all possible combinations.

Example (Nairobi Academy)

SELECT 
    s.student_name,
    e.exam_name
FROM students s
CROSS JOIN exams e;

Produces Cartesian product
This Generates combinations

6. SELF JOIN
A table joins with itself.
Example

SELECT 
    s1.student_name AS student_1,
    s2.student_name AS student_2,
    s1.class
FROM students s1
JOIN students s2 
    ON s1.class = s2.class
    AND s1.student_id <> s2.student_id;

Useful for comparisons or hierarchies
This Compares rows within the same table.

7. LEFT ANTI JOIN (Find Missing Data)
Returns records in the left table with no match.

SELECT p.full_name
FROM patients p
WHERE NOT EXISTS (
    SELECT 1 
    FROM appointments a 
    WHERE a.patient_id = p.patient_id
);

This finds missing relationships.

8. RIGHT ANTI JOIN

SELECT d.full_name
FROM doctors d
WHERE NOT EXISTS (
    SELECT 1
    FROM appointments a
    WHERE a.doctor_id = d.doctor_id
);

Finds unused or inactive records

Nairobi Academy: Combining Data
Student Exam Results

SELECT s.student_name,e.exam_name,r.marks
FROM results r
INNER JOIN students s 
    ON r.student_id = s.student_id
INNER JOIN exams e 
    ON r.exam_id = e.exam_id;

Ranking Students

SELECT s.student_name,e.exam_name,r.marks,
    RANK() OVER (PARTITION BY e.exam_name ORDER BY r.marks DESC) AS rank
FROM results r
JOIN students s ON r.student_id = s.student_id
JOIN exams e ON r.exam_id = e.exam_id;

Here is a shortcut to choosing the Right Join
| *Join Type * | *When to Use * |
| ------------------- | ------------------------ |
| INNER JOIN | Only matching data |
| LEFT JOIN | All left + matches |
| RIGHT JOIN | All right + matches |
| FULL OUTER JOIN | Everything |
| CROSS JOIN | All combinations |
| SELF JOIN | Compare within table |
| ANTI JOIN | Find missing data |

Conclusion
SQL joins are the foundation of relational data analysis.
Mastering them allows you to: