basic sql joins tutorial with "visuals" from https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

tutorial.sql

/*------------------------------------------------------------------------------

  create two tables
  insert data into them

  run with:

    psql -f file.sql dbname

------------------------------------------------------------------------------*/

  DROP TABLE IF EXISTS TableA;
  DROP TABLE IF EXISTS TableB;

  CREATE TABLE TableA (
   id serial PRIMARY KEY,
   name varchar(50) NOT NULL
  );

  CREATE TABLE TableB (
   id serial PRIMARY KEY,
   name varchar(50) NOT NULL
  );

  INSERT INTO TableA (id,name) VALUES
   (1, 'Pirate')
  ,(2, 'Monkey')
  ,(3, 'Ninja')
  ,(4, 'Spaghetti');

  INSERT INTO TableB (id,name) VALUES
   (1, 'Rutabaga')
  ,(2, 'Pirate')
  ,(3, 'Darth Vader')
  ,(4, 'Ninja');

/*------------------------------------------------------------------------------

  table a             table b

  id |   name    |     id |    name     |
  ---+-----------+    ----+-------------+
  1  | Pirate    |     1  | Rutabaga    |
  2  | Monkey    |     2  | Pirate      |
  3  | Ninja     |     3  | Darth Vader |
  4  | Spaghetti |     4  | Ninja       |

------------------------------------------------------------------------------*/

  with all_table_a as (
    select
      *
    from
      TableA
  ),

  all_table_b as (
    select
      *
    from
      TableB
  ),

/*------------------------------------------------------------------------------

  INNER JOIN

    produces only the set of records that match in both Table A and Table B.

    |   |###|   |
    | A |###| B |
    |   |###|   |

    id |  name  | id |  name
    ---+--------+----+--------
    1  | Pirate |  2 | Pirate
    3  | Ninja  |  4 | Ninja

------------------------------------------------------------------------------*/

  inner_join as (
    select
      *
    from
      all_table_a a
    inner join all_table_b b
      on a.name = b.name
  ),

/*------------------------------------------------------------------------------

  FULL OUTER JOIN

    produces the set of all records in Table A and Table B, with matching
    records from both sides where available. If there is no match, the missing
    side will contain null.

    |###|###|###|
    |#A#|###|#B#|
    |###|###|###|

    id |   name    | id |    name
    ---+-----------+----+-------------
    1  | Pirate    |  2 | Pirate
    2  | Monkey    |  - | -
    3  | Ninja     |  4 | Ninja
    4  | Spaghetti |  - | -
    -  | -         |  3 | Darth Vader
    -  | -         |  1 | Rutabaga

------------------------------------------------------------------------------*/

  full_outer_join as (
    select
      *
    from
      all_table_a a
    full outer join all_table_b b
      on a.name = b.name
  ),

/*------------------------------------------------------------------------------

  LEFT OUTER JOIN

    Left outer join produces a complete set of records from Table A, with the
    matching records (where available) in Table B. If there is no match, the
    right side will contain null.

    |###|###|   |
    |#A#|###| B |
    |###|###|   |

    id |   name    | id |  name
    ---+-----------+----+--------
    1  | Pirate    |  2 | Pirate
    2  | Monkey    |  - | -
    3  | Ninja     |  4 | Ninja
    4  | Spaghetti |  - | -

------------------------------------------------------------------------------*/

  left_outer_join as (
    select
      *
    from
      all_table_a a
    left outer join all_table_b b
      on a.name = b.name
  ),

/*------------------------------------------------------------------------------

  LEFT OUTER JOIN (EXCLUDE RIGHT SIDE)

    To produce the set of records only in Table A, but not in Table B, we
    perform the same left outer join, then exclude the records we don't want
    from the right side via a where clause.

    |###|   |   |
    |#A#|   | B |
    |###|   |   |

    id |   name    | id | name
    ---+-----------+----+------
    2  | Monkey    | -  | -
    4  | Spaghetti | -  | -

------------------------------------------------------------------------------*/

  left_outer_join_exclude as (
    select
      *
    from
      all_table_a a
    left outer join all_table_b b
      on a.name = b.name
    where
      b.id is null
  ),

/*------------------------------------------------------------------------------

  FULL OUTER JOIN (EXCLUDE MUTUAL TO GET UNIQUE FOR BOTH)

    To produce the set of records unique to Table A and Table B, we perform the
    same full outer join, then exclude the records we don't want from both sides
    via a where clause.

    |###|   |###|
    |#A#|   |#B#|
    |###|   |###|

    id |   name    | id |    name
    ---+-----------+----+-------------
    2  | Monkey    | -  | -
    4  | Spaghetti | -  | -
    -  | -         | 3  | Darth Vader
    -  | -         | 1  | Rutabaga

------------------------------------------------------------------------------*/

  full_outer_join_exclude as (
    select
      *
    from
      all_table_a a
    full outer join all_table_b b
      on a.name = b.name
    where
      a.id is null
    or
      b.id is null
  ),

/*------------------------------------------------------------------------------

  CROSS JOIN

    There's also a cartesian product or cross join, which as far as I can tell,
    can't be expressed as a Venn diagram

    This joins "everything to everything", resulting in 4 x 4 = 16 rows, far
    more than we had in the original sets. If you do the math, you can see why
    this is a very dangerous join to run against large tables.

    id |   name    | id |    name
    ---+-----------+----+-------------
    1  | Pirate    |  1 | Rutabaga
    1  | Pirate    |  2 | Pirate
    1  | Pirate    |  3 | Darth Vader
    1  | Pirate    |  4 | Ninja
    2  | Monkey    |  1 | Rutabaga
    2  | Monkey    |  2 | Pirate
    2  | Monkey    |  3 | Darth Vader
    2  | Monkey    |  4 | Ninja
    3  | Ninja     |  1 | Rutabaga
    3  | Ninja     |  2 | Pirate
    3  | Ninja     |  3 | Darth Vader
    3  | Ninja     |  4 | Ninja
    4  | Spaghetti |  1 | Rutabaga
    4  | Spaghetti |  2 | Pirate
    4  | Spaghetti |  3 | Darth Vader
    4  | Spaghetti |  4 | Ninja

------------------------------------------------------------------------------*/

  cross_join as (
    select
      *
    from
      all_table_a a
    cross join all_table_b b
  )

/*------------------------------------------------------------------------------

  SELECT QUERIES

------------------------------------------------------------------------------*/

-- select * from all_table_a
-- select * from all_table_b
-- select * from inner_join
-- select * from full_outer_join
-- select * from left_outer_join
-- select * from left_outer_join_exclude
-- select * from full_outer_join_exclude
select * from cross_join