Simple example to show the difference between INNER, LEFT OUTER, RIGHT OUTER, and FULL OUTER joins in SQL

join_practice.txt

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=#