Introduction
There are many kinds of joins in SQL. I often have trouble keeping them straight.
This article divides the joins into pairs of opposites, giving brief definitions of each
along with their opposites.
For the rest of this article, we'll be joining two tables, which we'll call the "left table"
and the "right table". We'll be using these tables:
create table rel1 (
id int primary key,
letter varchar(1)
);
create table rel2 (
id int primary key,
fk int,
foreign key (fk) references rel1(id),
mychar varchar(1)
);
select * from rel1;
+----+--------+
| id | letter |
+----+--------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+----+--------+
select * from rel2;
+----+--------+--------+
| id | fk | mychar |
+----+--------+--------+
| 1 | 1 | z |
| 3 | 4 | o |
| 5 | 2 | m |
| 8 | 2 | q |
+----+--------+--------+
`rel1` will be used as the left table, and `rel2` as the right table unless otherwise noted.
Inner vs. outer
Inner joins return only matching pairs of rows between the two tables.
Outer joins also return rows from one or both tables that
match 0 rows from the other table.
(Note that `on rel1.id = rel2.id` is the "join predicate" in these examples)
select *
from rel1
inner join rel2
on rel1.id = rel2.id;
+----+--------+----+------+--------+
| id | letter | id | fk | mychar |
+----+--------+----+------+--------+
| 1 | a | 1 | 1 | z |
| 3 | c | 3 | 4 | o |
+----+--------+----+------+--------+
select *
from rel1
outer join rel2
on rel1.id = rel2.id;
+------+--------+------+------+--------+
| id | letter | id | fk | mychar |
+------+--------+------+------+--------+
| 1 | a | 1 | 1 | z |
| 2 | b | NULL | NULL | NULL |
| 3 | c | 3 | 4 | o |
| 4 | d | NULL | NULL | NULL |
| NULL | NULL | 5 | 2 | m |
| NULL | NULL | 8 | 2 | q |
+------+--------+------+------+--------+
Outer join: left and right outer join
Left outer joins return, in addition to the rows returned by the corresponding inner join, rows from the
left table that don't match any rows in the right table.
Right outer joins are identical to inner joins except that they also return unmatched rows from the right table.
select *
from rel1
left outer join rel2
on rel1.id = rel2.id;
+----+--------+------+------+--------+
| id | letter | id | fk | mychar |
+----+--------+------+------+--------+
| 1 | a | 1 | 1 | z |
| 2 | b | NULL | NULL | NULL |
| 3 | c | 3 | 4 | o |
| 4 | d | NULL | NULL | NULL |
+----+--------+------+------+--------+
select *
from rel1
right outer join rel2
on rel1.id = rel2.id;
+------+--------+----+------+--------+
| id | letter | id | fk | mychar |
+------+--------+----+------+--------+
| 1 | a | 1 | 1 | z |
| 3 | c | 3 | 4 | o |
| NULL | NULL | 5 | 2 | m |
| NULL | NULL | 8 | 2 | q |
+------+--------+----+------+--------+
Note that the union of a left join and a right join is a full outer join;
their intersection is an inner join.
Semi vs. anti
"Semi"-joins return rows from the left table that match rows in the right table.
"Anti"-joins return rows from the left table that
don't match rows in the right table.
Both of these join forms act as a sort of filter for the left table, since no columns from the right table are returned.
-- all rows in rel1 that have a matching row (by fk value) in rel2:
select *
from rel1
where id in (select fk from rel2);
+----+--------+
| id | letter |
+----+--------+
| 1 | a |
| 2 | b |
| 4 | d |
+----+--------+
-- all rel1 rows that don't have a matching row (by fk value) in rel2:
select *
from rel1
where id not in (select fk from rel2);
+----+--------+
| id | letter |
+----+--------+
| 3 | c |
+----+--------+
Note that there are multiple ways to implement semi- and anti-joins in SQL:
-- "semi join" implemented using "inner join" and "distinct"
select distinct rel1.*
from rel1
inner join rel2
on rel1.id = rel2.fk;
+----+--------+
| id | letter |
+----+--------+
| 1 | a |
| 2 | b |
| 4 | d |
+----+--------+
-- "anti join" implemented using "left join" and "is null"
select distinct rel1.*
from rel1
left join rel2
on rel1.id = rel2.fk
where rel2.id is null;
+----+--------+
| id | letter |
+----+--------+
| 3 | c |
+----+--------+
Equi vs. theta
"Equi"-joins are those where the join predicate is an equality.
"Theta"-joins are those where the join predicate is an inequality.
-- equi-join
select *
from rel1
inner join rel2
on rel1.id = rel2.id;
+----+--------+----+------+--------+
| id | letter | id | fk | mychar |
+----+--------+----+------+--------+
| 1 | a | 1 | 1 | z |
| 3 | c | 3 | 4 | o |
+----+--------+----+------+--------+
-- theta-join
select *
from rel1
inner join rel2
on rel1.id != rel2.id;
+----+--------+----+------+--------+
| id | letter | id | fk | mychar |
+----+--------+----+------+--------+
| 2 | b | 1 | 1 | z |
| 3 | c | 1 | 1 | z |
| 4 | d | 1 | 1 | z |
| 1 | a | 3 | 4 | o |
| 2 | b | 3 | 4 | o |
| 4 | d | 3 | 4 | o |
| 1 | a | 5 | 2 | m |
| 2 | b | 5 | 2 | m |
| 3 | c | 5 | 2 | m |
| 4 | d | 5 | 2 | m |
| 1 | a | 8 | 2 | q |
| 2 | b | 8 | 2 | q |
| 3 | c | 8 | 2 | q |
| 4 | d | 8 | 2 | q |
+----+--------+----+------+--------+
Note that the union of an equi-join and a theta-join is a cartesian product,
if the same tables and join predicate are used.
Implicit vs. explicit
source
Explicit joins use one of the "join" keywords, such as "inner join" or "left join", combined with a join predicate:
select * from rel1 inner join rel2 on rel1.id = rel2.id;
+----+--------+----+------+--------+
| id | letter | id | fk | mychar |
+----+--------+----+------+--------+
| 1 | a | 1 | 1 | z |
| 3 | c | 3 | 4 | o |
+----+--------+----+------+--------+
Implicit joins do not use a "join" keyword, simply listing the tables in the "from" clause, and place the join predicate in the "where" clause:
select * from rel1, rel2 where rel1.id = rel2.id;
+----+--------+----+------+--------+
| id | letter | id | fk | mychar |
+----+--------+----+------+--------+
| 1 | a | 1 | 1 | z |
| 3 | c | 3 | 4 | o |
+----+--------+----+------+--------+
Explicit joins are generally recommended, as they are thought to be
more maintainable and more clearly express the programmer's intent.
Note that this is purely a
syntactic difference.
Cross join
This produces a cartesian product of the tables, joining every row in the left table with every row in the right table.
select * from rel1 inner join rel2 on 1;
+----+--------+----+------+--------+
| id | letter | id | fk | mychar |
+----+--------+----+------+--------+
| 1 | a | 1 | 1 | z |
| 2 | b | 1 | 1 | z |
| 3 | c | 1 | 1 | z |
| 4 | d | 1 | 1 | z |
| 1 | a | 3 | 4 | o |
| 2 | b | 3 | 4 | o |
| 3 | c | 3 | 4 | o |
| 4 | d | 3 | 4 | o |
| 1 | a | 5 | 2 | m |
| 2 | b | 5 | 2 | m |
| 3 | c | 5 | 2 | m |
| 4 | d | 5 | 2 | m |
| 1 | a | 8 | 2 | q |
| 2 | b | 8 | 2 | q |
| 3 | c | 8 | 2 | q |
| 4 | d | 8 | 2 | q |
+----+--------+----+------+--------+
Natural join
The result is the set of rows, from the two tables, that have the
same values for columns of the same name.
If the tables share all columns names, the result is an intersection.
If the tables share no column names, the result is a cartesian product.
The number of columns in the result is the number of distinct column names in the tables.
select *
from rel1
natural join rel2;
+----+--------+------+--------+
| id | letter | fk | mychar |
+----+--------+------+--------+
| 1 | a | 1 | z |
| 3 | c | 4 | o |
+----+--------+------+--------+
select *
from rel1
inner join rel2
using(id);
+----+--------+------+--------+
| id | letter | fk | mychar |
+----+--------+------+--------+
| 1 | a | 1 | z |
| 3 | c | 4 | o |
+----+--------+------+--------+
select rel1.*, rel2.fk, rel2.mychar
from rel1
inner join rel2
on rel1.id = rel2.id;
+----+--------+------+--------+
| id | letter | fk | mychar |
+----+--------+------+--------+
| 1 | a | 1 | z |
| 3 | c | 4 | o |
+----+--------+------+--------+
This join is not useful in practice, as it can be completely replaced by queries using (more easily understandable) inner joins.
Additionally, it may produce non-intuitive results in that tables are not necessarily joined
on foreign keys, which to my mind is the actual "natural" way to join tables.
(Recall that rel1.id <=> rel2.fk was the foreign key relationship we defined).
Self join
This is just a special case of a join where the same table is used for both sides of the join.
It can be combined with any of the preceding "join" forms.
select *
from rel2 a
left join rel2 b
on a.id < b.fk;
+----+------+--------+------+------+--------+
| id | fk | mychar | id | fk | mychar |
+----+------+--------+------+------+--------+
| 1 | 1 | z | 3 | 4 | o |
| 1 | 1 | z | 5 | 2 | m |
| 1 | 1 | z | 8 | 2 | q |
| 3 | 4 | o | 3 | 4 | o |
| 5 | 2 | m | NULL | NULL | NULL |
| 8 | 2 | q | NULL | NULL | NULL |
+----+------+--------+------+------+--------+
Note the table aliases, used to distinguish between the two uses of the same table.
Summary
A convenient way to understand joins is in pairs, as I showed:
| join type | opposite | major point of difference |
|---|
| inner | outer | do/don't keep non-matching rows |
| left outer | right outer | augment inner join with unmatched rows from left/right table |
| semi | anti | filter rows from a single table, keeping/dropping those that match rows in another table |
| equi | theta | join predicate is equality/inequality |
| implicit | explicit | syntax |
Note that cross, natural, and self joins don't have opposites in this sense.
Sources
article about joins
Wikipedia's "join" article
