Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
DROP TABLE IF EXISTS club_roster;
DROP TABLE IF EXISTS students;
DROP TABLE IF EXISTS clubs;
CREATE TABLE clubs (
id serial PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE students (
id serial PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE club_roster (
id serial PRIMARY KEY,
club_id integer REFERENCES clubs (id),
student_id integer REFERENCES students (id)
);
INSERT INTO clubs (name)
VALUES ('soccer'), ('baseball'), ('tennis');
INSERT INTO students (name)
VALUES ('Gerda'), ('Lorine'), ('Karianne'), ('Destin'), ('Emilie'), ('Jerod'), ('Kristy'), ('Rodrick');
INSERT INTO club_roster (club_id, student_id)
VALUES (1, 2), (1, 3), (2, 4), (2, 5), (2, 6), (3, 7), (3, 1);
-- inner join example
SELECT
students.id AS student_id,
students.name AS student_name
FROM
club_roster
INNER JOIN students ON club_roster.student_id = students.id
ORDER BY
student_id;
-- outer join example
SELECT
students.id AS student_id,
students.name AS student_name,
clubs.name AS club_name
FROM
students
LEFT OUTER JOIN club_roster ON students.id = club_roster.student_id
LEFT OUTER JOIN clubs ON club_roster.club_id = clubs.id
ORDER BY
student_id;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment