Skip to content

Instantly share code, notes, and snippets.

@davidvandusen
Created March 27, 2017 23:30
Show Gist options
  • Save davidvandusen/b5d4ca914275c0922136bfaaa315265c to your computer and use it in GitHub Desktop.
Save davidvandusen/b5d4ca914275c0922136bfaaa315265c to your computer and use it in GitHub Desktop.
Simple example to show the difference between INNER, LEFT OUTER, RIGHT OUTER, and FULL OUTER joins in SQL
join_practice=# create table users (
join_practice(# id serial primary key,
join_practice(# username varchar(255)
join_practice(# );
CREATE TABLE
join_practice=# create table photos (
join_practice(# id serial primary key,
join_practice(# title varchar(255),
join_practice(# photographer_id integer
join_practice(# );
CREATE TABLE
join_practice=# insert into users (username) values ('Jim'), ('Steph');
INSERT 0 2
join_practice=# insert into photos (title, photographer_id) values ('sunflower', 1), ('sunset', null);
INSERT 0 2
join_practice=# select * from users;
id | username
----+----------
1 | Jim
2 | Steph
(2 rows)
join_practice=# select * from photos;
id | title | photographer_id
----+-----------+-----------------
1 | sunflower | 1
2 | sunset |
(2 rows)
join_practice=# select * from users join photos on users.id = photos.photographer_id;
id | username | id | title | photographer_id
----+----------+----+-----------+-----------------
1 | Jim | 1 | sunflower | 1
(1 row)
join_practice=# select * from users left join photos on users.id = photos.photographer_id;
id | username | id | title | photographer_id
----+----------+----+-----------+-----------------
1 | Jim | 1 | sunflower | 1
2 | Steph | | |
(2 rows)
join_practice=# select * from users right join photos on users.id = photos.photographer_id;
id | username | id | title | photographer_id
----+----------+----+-----------+-----------------
1 | Jim | 1 | sunflower | 1
| | 2 | sunset |
(2 rows)
join_practice=# select * from users full join photos on users.id = photos.photographer_id;
id | username | id | title | photographer_id
----+----------+----+-----------+-----------------
1 | Jim | 1 | sunflower | 1
2 | Steph | | |
| | 2 | sunset |
(3 rows)
join_practice=#
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment