adapted from http://www.postgresql.org/docs/current/interactive/queries-table-expressions.html
Assume we are working with two tables:
Table t2
| id | value |
|---|
| 1 | xxx |
| 3 | yyy |
| 5 | zzz |
A
cross join will combine each row from
t1 with each row from
t2:
SELECT * FROM t1 CROSS JOIN t2
| id | name | id | value |
|---|
| 1 | a | 1 | xxx |
| 1 | a | 3 | yyy |
| 1 | a | 5 | zzz |
| 2 | b | 1 | xxx |
| 2 | b | 3 | yyy |
| 2 | b | 5 | zzz |
| 3 | c | 1 | xxx |
| 3 | c | 3 | yyy |
| 3 | c | 5 | zzz |
Notice there are 9 rows: (3 rows from
t1) x (3 rows from
t2) = 9 rows total. You will rarely, if ever, need to use a cross join at PVS.
By default, all joins are
inner joins unless you specify
left,
right, or
full (see below). The word
inner is optional, and is usually omitted.
An
inner join satisfies the following: For each row
r1 in
t1, the resulting table has a row for each row in
t2 that satisfies the join condition.
SELECT * FROM t1 JOIN t2 ON t1.id = t2.id
| id | name | id | value |
|---|
| 1 | a | 1 | xxx |
| 3 | c | 3 | yyy |
Notice that in the previous example, rows that weren't matched were omitted from the results. There are times when you want all of the rows in the first table to show up in the results. A
left join does an inner join first, then, for every row in
t1 that isn't in the results, that row is added (with null values if necessary). It's called a
left join because
t1 show up on the left side of the LEFT JOIN statement.
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id
| id | name | id | value |
|---|
| 1 | a | 1 | xxx |
| 2 | b | | |
| 3 | c | 3 | yyy |
Likewise, a
right join does an inner join first, then for every row in *t2* that isn't in the results, that row is added (with null values if necessary). Notice that
t2 is on the right side of the RIGHT JOIN statement.
SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.id
| id | name | id | value |
|---|
| 1 | a | 1 | xxx |
| 3 | c | 3 | yyy |
| | | 5 | zzz |
With a
full join, an inner join is performed first, then for every row in
t1 that isn't in the results, that row is added (with null values if necessary). For every row in
t2 that isn't in the results, that row is added (with null values if necessary).
SELECT * FROM t1 FULL JOIN t2 ON t1.id = t2.id
| id | name | id | value |
|---|
| 1 | a | 1 | xxx |
| 2 | b | | |
| 3 | c | 3 | yyy |
| | | 5 | zzz |
Sometimes you might be tempted to combine conditions. For instance, let's say you wanted to do a
left join between
t1 and
t2, and filter the results so that only records containing "xxx" were returned (for instance, all candidates from a certain state). Your first try might be something like this:
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id AND t2.value = 'xxx'
| id | name | id | value |
|---|
| 1 | a | 1 | xxx |
| 2 | b | | |
| 3 | c | | |
Probably not what you expected! The reason is that the LEFT JOIN will
always contain rows in
t1 that aren't in the results. So the first row matches your conditional, then the two last rows are added because they do not match the conditional.
What you really want to do here is use a WHERE clause, as this is evaluated
after the join:
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.value = 'xxx'
CategoryTechnicalSkills
