DB Joins in MySQL

db-joins.md

Given a database with two tables.

select * from users;

+----+------+
| id | name |
+----+------+
|  1 | bob  |
|  2 | sam  |
|  3 | jan  |
|  4 | max  |
|  5 | pat  |
+----+------+

select * from transactions;

+----+--------------+---------+
| id | amount_cents | user_id |
+----+--------------+---------+
|  1 |            0 |       1 |
|  2 |           10 |       1 |
|  3 |           20 |       2 |
|  4 |           30 |       3 |
|  5 |           40 |       5 |
|  6 |           50 |       5 |
|  7 |           60 |       6 |
+----+--------------+---------+

In Rails terms:

class Users 
  has_many :transactions
end

class Transaction
  belongs_to :user
end

[INNER] JOIN

select u.*, t.id as transaction_id, t.amount_cents 
from users u 
  inner join transactions t on u.id = t.user_id;

Produces only matches that are in both tables.

+----+------+----------------+--------------+
| id | name | transaction_id | amount_cents |
+----+------+----------------+--------------+
|  1 | bob  |              1 |            0 |
|  1 | bob  |              2 |           10 |
|  2 | sam  |              3 |           20 |
|  3 | jan  |              4 |           30 |
|  5 | pat  |              5 |           40 |
|  5 | pat  |              6 |           50 |
+----+------+----------------+--------------+

LEFT [OUTER] JOIN

select u.*, t.id as transaction_id, t.amount_cents 
from users u 
  left join transactions t on u.id = t.user_id;

Produces all rows from the first table mentioned in from, whether or not there are matching rows in the second table.

+----+------+----------------+--------------+
| id | name | transaction_id | amount_cents |
+----+------+----------------+--------------+
|  1 | bob  |              2 |           10 |
|  1 | bob  |              1 |            0 |
|  2 | sam  |              3 |           20 |
|  3 | jan  |              4 |           30 |
|  4 | max  |           NULL |         NULL |
|  5 | pat  |              6 |           50 |
|  5 | pat  |              5 |           40 |
+----+------+----------------+--------------+
7 rows in set (0.00 sec)

RIGHT [OUTER] JOIN

select u.*, t.id as transaction_id, t.amount_cents 
from users u 
  right join transactions t on u.id = t.user_id;

Produces all rows from the second table mentioned in from, whether or not there are matching rows in the first table.

+------+------+----------------+--------------+
| id   | name | transaction_id | amount_cents |
+------+------+----------------+--------------+
|    1 | bob  |              1 |            0 |
|    1 | bob  |              2 |           10 |
|    2 | sam  |              3 |           20 |
|    3 | jan  |              4 |           30 |
|    5 | pat  |              5 |           40 |
|    5 | pat  |              6 |           50 |
| NULL | NULL |              7 |           60 |
+------+------+----------------+--------------+
7 rows in set (0.01 sec)

FULL JOIN

select u.*, t.id as transaction_id, t.amount_cents 
from users u 
  full join transactions t on u.id = t.user_id;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use near 
'full join transactions t on u.id = t.user_id' at line 1

FULL JOIN is doesn't exist in MySQL! Instead we can use UNION ALL to produce the same effect, merging LEFT and RIGHT outer join queries:

select
  u.id, u.name,
  t.id as transaction_id,
  t.amount_cents
from users u
  left join transactions t on u.id = t.user_id

UNION ALL

select
  u.id, u.name,
  t.id as transaction_id,
  t.amount_cents
from users u
  right join transactions t on u.id = t.user_id
where u.id is NULL -- only include rows that would not 
                   -- be included the first query

resulting in:

+------+------+----------------+--------------+
| id   | name | transaction_id | amount_cents |
+------+------+----------------+--------------+
|    1 | bob  |              2 |           10 |
|    1 | bob  |              1 |            0 |
|    2 | sam  |              3 |           20 |
|    3 | jan  |              4 |           30 |
|    4 | max  |           NULL |         NULL |  <-- LEFT OUTER
|    5 | pat  |              6 |           50 |
|    5 | pat  |              5 |           40 |
| NULL | NULL |              7 |           60 |  <-- RIGHT OUTER
+------+------+----------------+--------------+
8 rows in set (0.37 sec)