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
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 |
+----+------+----------------+--------------+
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)
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)
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)