Important topics: Joins and relationships

1.1 Why data is split across tables

A course has a title, a fee and an instructor. A student has a name, a city and a join date. An enrollment links one student to one course. Storing all of that in one wide table would repeat the course fee in every enrollment row and make a fee change a multi-row update that can go wrong halfway. Splitting the data into tables removes the repetition, and a join puts the pieces back together for one query.

A join combines rows from two tables using a condition. Most conditions match a foreign key to the primary key it references, such as enrollments.student_id = students.student_id. The condition names the relationship, and the join type names what to do when a row has no partner. Joins are the reason foreign keys matter: the constraint protects the relationship that the join depends on. Every example in this module runs on SQLite 3.53.

1.2 INNER JOIN keeps matching pairs only

INNER JOIN returns pairs where the join condition is TRUE. Rows with no partner on either side are dropped. The condition goes in the ON clause; a following WHERE filters the combined rows. Table aliases keep the query short, and qualifying every column with its alias removes the ambiguity when two tables have a column with the same name.

SQLite supports the plain JOIN spelling as a synonym for INNER JOIN. The written order of the tables does not change an inner join's result, though it can change the plan. Three tables chain from one ON to the next: students to enrollments on student_id, then enrollments to courses on course_code.

SELECT s.name, e.status, e.rating
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
WHERE e.course_code = 'SQL'
ORDER BY s.name;

name         | status    | rating
-------------+-----------+-------
Aarav Sharma | completed | 5
Ishita Bose  | active    | 3
Meera Iyer   | active    | 4
Sana Khan    | completed | 4
Zoya Ali     | active    | NULL

SELECT s.name, c.title, e.status
FROM enrollments e
JOIN students s ON s.student_id = e.student_id
JOIN courses c ON c.course_code = e.course_code
WHERE s.city = 'Delhi'
ORDER BY s.name, c.title;

name         | title             | status
-------------+-------------------+----------
Aarav Sharma | Data Structures   | active
Aarav Sharma | SQL and Databases | completed
Rohan Verma  | Python Basics     | active

1.3 LEFT JOIN keeps the left side

LEFT JOIN returns every row of the left table, plus matching right-side values. When no match exists, the right-side columns are filled with NULL. That makes a left join the standard way to answer "which parents have no children": join from the parent to the child, then filter the child key with IS NULL. The filter must go after the join, because inside ON it changes what counts as a match instead of what happens to unmatched rows.

RIGHT JOIN is the mirror image, keeping every row of the right table. SQLite gained it in version 3.39, so older tutorials that say "SQLite has no right join" are out of date. FULL OUTER JOIN keeps unmatched rows from both sides. In practice a right join can be rewritten as a left join with the tables swapped, and many teams do that for consistency. Pick one direction per query and keep it.

SELECT c.course_code, c.title, i.name AS instructor
FROM courses c
LEFT JOIN instructors i ON c.instructor_id = i.instructor_id
ORDER BY c.course_code;

course_code | title                   | instructor
------------+-------------------------+-------------
CN          | Computer Networks       | Nadia Sheikh
DS          | Data Structures         | Arjun Das
ML          | Machine Learning Basics | Vikram Rao
PY          | Python Basics           | NULL
SQL         | SQL and Databases       | Priya Menon
WEB         | Web Development         | Vikram Rao

SELECT s.name
FROM students s
LEFT JOIN enrollments e ON e.student_id = s.student_id
WHERE e.student_id IS NULL;

name
---------
Dev Patel

SELECT i.name AS instructor, c.title
FROM courses c
RIGHT JOIN instructors i ON c.instructor_id = i.instructor_id
ORDER BY i.name, c.title;

instructor   | title
-------------+------------------------
Arjun Das    | Data Structures
Nadia Sheikh | Computer Networks
Priya Menon  | SQL and Databases
Tara Singh   | NULL
Vikram Rao   | Machine Learning Basics
Vikram Rao   | Web Development

1.4 CROSS JOIN makes every pair

CROSS JOIN pairs every row of the left table with every row of the right table. Six courses and five instructors produce 30 pairs. The result is useful for building a calendar or a price grid, where each combination needs a row. It is dangerous by accident: an inner join whose ON clause was forgotten, or a comma join with no WHERE link, produces the same multiplication, and a report that suddenly has millions of rows usually contains one.

A related trap is a join condition that matches too broadly. ON s.city = i.city is a legitimate join on a shared attribute, but it pairs every Delhi student with every Delhi instructor. Before running a join, decide whether you expect one, many or no partners per row, then check the row count against that expectation.

SELECT COUNT(*) AS pairs FROM courses CROSS JOIN instructors;

pairs
-----
30

1.5 Self joins give one table two roles

A self join joins a table to itself, which requires two aliases so the engine can tell the two roles apart. The students table stores a mentor_id that points at another student, so students AS s can be the mentee while students AS m is the mentor. The join condition s.mentor_id = m.student_id turns the id into a name.

A plain inner self join returns only students who have a mentor. Students without one disappear, which is correct for a mentor roster and wrong for a full student list. Swap in a left join when the mentor is optional, and the unmentored students come back with NULL in the mentor column. Self joins also appear in employee and manager tables and in any table that references itself, such as a category tree.

SELECT s.name AS student, m.name AS mentor
FROM students s
JOIN students m ON s.mentor_id = m.student_id
ORDER BY student;

student     | mentor
------------+-------------
Ishita Bose | Meera Iyer
Kabir Nair  | Meera Iyer
Neel Joshi  | Meera Iyer
Rohan Verma | Aarav Sharma
Sana Khan   | Aarav Sharma
Zoya Ali    | Aarav Sharma

1.6 Many-to-many needs a junction table

One student takes several courses, and one course has several students, so neither table can hold the other's key without repeating rows. The enrollments table solves this by holding one row per student and course, with a composite primary key on the pair plus columns that belong to the relationship itself, such as the enrollment date, status and rating. This is a junction table, also called a link table.

To query it, join the junction to both ends. The three-table join above already does that. The payoff is that relationship facts stay with the relationship: the rating of Sana in SQL is one fact, not a column added to the courses table where it would apply to every student. The cost is that most interesting questions now need a join, which is why join diagrams are worth learning.

1.7 Set operations compare result sets

Set operations combine two complete SELECT statements. UNION removes duplicate rows from the combined result, UNION ALL keeps every row, INTERSECT returns rows that appear in both inputs, and EXCEPT returns rows from the first input that do not appear in the second. The column lists must line up in number and order, and the result takes column names from the first query.

NULL handling deserves a note. UNION and INTERSECT compare whole rows, and NULL is treated as equal to NULL for that comparison, so one NULL row survives a UNION and a shared NULL city counts as an intersection. EXCEPT removes NULL when both sides contain it. SQLite and PostgreSQL support all four operators. MySQL has had UNION and UNION ALL for a long time, and it adds INTERSECT and EXCEPT from version 8.0.31, so older MySQL needs a join or EXISTS rewrite for those two.

SELECT city FROM students WHERE city IS NOT NULL
INTERSECT
SELECT city FROM instructors WHERE city IS NOT NULL;

SELECT city FROM students WHERE city IS NOT NULL
EXCEPT
SELECT city FROM instructors WHERE city IS NOT NULL;

city
---------
Bengaluru
Delhi
Kolkata
Mumbai
city
---------
Ahmedabad
Chennai
Kochi

1.8 Common mistakes

• Forgetting the ON clause and getting a cross join with far too many rows.
• Putting a right-table filter in WHERE after a LEFT JOIN and turning it into an inner join by accident.
• Expecting an inner self join to list rows without a partner.
• Joining two child tables directly instead of going through the junction table.
• Assuming UNION and UNION ALL differ only in speed. The duplicate rule is the part that changes results.