SQL chapters

Chapter 12 of 17

Keys and joins

Try this in our SQLite Compiler →

The idea

Related tables store different entities. A primary key identifies a row and a foreign key connects related records. JOIN combines rows matching an explicit condition. Without the correct join condition you can accidentally multiply every row by every other row.

Worked example

WITH students(id, name) AS (VALUES (1, 'Asha'), (2, 'Ravi')),
results(student_id, score) AS (VALUES (1, 82), (2, 60))
SELECT students.name, results.score FROM students JOIN results ON results.student_id = students.id ORDER BY students.id;

What to check

The result pairs Asha with 82 and Ravi with 60. Qualified column names clarify which relation each value belongs to.

Practice task

Add a student without a result and compare JOIN with LEFT JOIN. Find the NULL score in the latter result.

Check your understanding

Explain the result in your own words before changing the example. Change one thing at a time, compare the result with your prediction, and keep the version that demonstrates the concept clearly.

Try it yourself

Output
Run your project to see output here.
Ctrl/Cmd+Enter runs · Ctrl/Cmd+S downloads the project · Local auto-save is always on