SQL JOIN CLINIC

join_clinic.sql

-- TABLE A
SELECT * FROM A;
-- id | name
-- ----+-------
--   1 | bob
--   2 | jill
--   3 | frank

-- TABLE B
SELECT * FROM B;
-- id | name
-- ----+-------
--  1 | mary
--  2 | bob
--  3 | frank

-- JOIN EXAMPLE
SELECT * FROM A
JOIN B
ON A.name = B.name;

-- OUTPUT
-- id | name  | id | name
-- ----+-------+----+-------
--  1 | bob   |  2 | bob
--  3 | frank |  3 | frank

-- FULL JOIN EXAMPLE
SELECT * FROM A
FULL OUTER JOIN B
ON A.name = B.name;

-- OUTPUT
-- id | name  | id | name
-- ----+-------+----+-------
--  1 | bob   |  2 | bob
--  3 | frank |  3 | frank
--  2 | jill  |    |
--    |       |  1 | mary

-- LEFT OUTER JOIN EXAMPLE
SELECT * FROM A
LEFT OUTER JOIN B
ON A.name = B.name;

-- OUTPUT
-- id | name  | id | name
-- ----+-------+----+-------
--  1 | bob   |  2 | bob
--  3 | frank |  3 | frank
--  2 | jill  |    |

-- LEFT OUTER JOIN WHERE column is NULL
SELECT * FROM A
LEFT OUTER JOIN B
ON A.name = B.name
WHERE B.name is NULL;

-- OUTPUT
-- id | name | id | name
-- ----+------+----+------
--  2 | jill |    |

-- CROSS JOIN EXAMPLE
SELECT * FROM A
CROSS JOIN B;

-- OUTPUT
-- id | name  | id | name
-- ----+-------+----+-------
--  1 | bob   |  1 | mary
--  1 | bob   |  2 | bob
--  1 | bob   |  3 | frank
--  2 | jill  |  1 | mary
--  2 | jill  |  2 | bob
--  2 | jill  |  3 | frank
--  3 | frank |  1 | mary
--  3 | frank |  2 | bob
--  3 | frank |  3 | frank