Zerethon IDE
Sign in

JOIN

Combine tables with INNER JOIN and LEFT JOIN on a matching column.

Runs in Zerethon IDE · SQLite 3 Official SQL documentation ↗
main.sql 
SELECT s.name, c.title, e.grade
FROM students s
LEFT JOIN enrollments e ON e.student_id = s.id
LEFT JOIN courses c ON c.id = e.course_id
ORDER BY s.name, c.title;
setup/schema.sql 
CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT NOT NULL, year INTEGER);
CREATE TABLE courses (id INTEGER PRIMARY KEY, title TEXT NOT NULL);
CREATE TABLE enrollments (
    student_id INTEGER REFERENCES students (id),
    course_id INTEGER REFERENCES courses (id),
    grade REAL
);

INSERT INTO students VALUES (1, 'Ada', 2), (2, 'Alan', 3), (3, 'Grace', 2), (4, 'Linus', 1);
INSERT INTO courses VALUES (1, 'Algorithms'), (2, 'Databases'), (3, 'Networks');
INSERT INTO enrollments VALUES (1, 1, 9.5), (1, 2, 8.0), (2, 1, 7.5), (3, 2, 9.0), (3, 3, NULL);

Result

nametitlegrade
AdaAlgorithms9.5
AdaDatabases8
AlanAlgorithms7.5
GraceDatabases9
GraceNetworksNULL
LinusNULLNULL
Uses the school database in setup/schema.sql (shown below the query): students, courses and enrollments.
  • JOIN (INNER) keeps only rows that match on both sides; Linus would disappear.
  • LEFT JOIN keeps every row of the left table and fills the right side with NULL when nothing matches.
  • SQLite also supports RIGHT JOIN and FULL JOIN.
  • Give tables short aliases (students s) and prefix columns (s.name) when names repeat — otherwise SQLite reports an ambiguous column.

Related topics

Official references