Revision history for SQLJoins
Additions:
----
CategoryTechnicalSkills
CategoryTechnicalSkills
Additions:
==ON vs. WHERE==
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' %%
||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' %%
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' %%
||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' %%
Additions:
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.
==Full Join==
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 %%
==Full Join==
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 %%
Deletions:
Additions:
==Left Join==
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 %%
||2||b|| || ||
==Right Join==
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 %%
|| || ||5||zzz||
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 %%
||2||b|| || ||
==Right Join==
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 %%
|| || ||5||zzz||
Additions:
==Inner Join==
By default, all joins are **inner joins** unless you specify **left**, **right**, or **full** (see below). Even though none of the examples below contain //inner//, they are all inner joins.
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 %%
By default, all joins are **inner joins** unless you specify **left**, **right**, or **full** (see below). Even though none of the examples below contain //inner//, they are all inner joins.
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 %%
Additions:
%% SELECT * FROM t1 CROSS JOIN t2 %%
Deletions:
Additions:
|?|(x:4)SELECT * FROM t1 CROSS JOIN t2||
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.
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.
Deletions:
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.
Additions:
==Cross Join==
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.
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.
