SQL
Joins
Joins are a fundamental relational operation used to connect two tables or relations horizontally.
The relations are referred to as the _left_ and _right_ sides of the join
based on how they are written in the join clause.
Each result row has the columns from both relations.  
A join uses a rule to match pairs of rows from each relation.
Often this is a predicate, but there are other implied rules that may be specified.  
#### Outer Joins {#docs:sql:query_syntax:from::outer-joins}  
Rows that do not have any matches can still be returned if an `OUTER` join is specified.
Outer joins can be one of:  
* `LEFT` (All rows from the left relation appear at least once)
* `RIGHT` (All rows from the right relation appear at least once)
* `FULL` (All rows from both relations appear at least once)  
A join that is not `OUTER` is `INNER` (only rows that get paired are returned).  
When an unpaired row is returned, the attributes from the other table are set to `NULL`.  
#### Cross Product Joins (Cartesian Product) {#docs:sql:query_syntax:from::cross-product-joins-cartesian-product}  
The simplest type of join is a `CROSS JOIN`.
There are no conditions for this type of join,
and it just returns all the possible pairs.  
Return all pairs of rows:  
```sql
SELECT a.*, b.*
FROM a
CROSS JOIN b;
```  
This is equivalent to omitting the `JOIN` clause:  
```sql
SELECT a.*, b.*
FROM a, b;
```  
#### Conditional Joins {#docs:sql:query_syntax:from::conditional-joins}  
Most joins are specified by a predicate that connects
attributes from one side to attributes from the other side.
The conditions can be explicitly specified using an `ON` clause
with the join (clearer) or implied by the `WHERE` clause (old-fashioned).  
We use the `l_regions` and the `l_nations` tables from the TPC-H schema:  
```sql
CREATE TABLE l_regions (
r_regionkey INTEGER NOT NULL PRIMARY KEY,
r_name      CHAR(25) NOT NULL,
r_comment   VARCHAR(152)
);

CREATE TABLE l_nations (
n_nationkey INTEGER NOT NULL PRIMARY KEY,
n_name      CHAR(25) NOT NULL,
n_regionkey INTEGER NOT NULL,
n_comment   VARCHAR(152),
FOREIGN KEY (n_regionkey) REFERENCES l_regions(r_regionkey)
);
```  
Return the regions for the nations:  
```sql
SELECT n.*, r.*
FROM l_nations n
JOIN l_regions r ON (n_regionkey = r_regionkey);
```  
If the column names are the same and are required to be equal,
then the simpler `USING` syntax can be used:  
```sql
CREATE TABLE l_regions (regionkey INTEGER NOT NULL PRIMARY KEY,
name      CHAR(25) NOT NULL,
comment   VARCHAR(152));

CREATE TABLE l_nations (nationkey INTEGER NOT NULL PRIMARY KEY,
name      CHAR(25) NOT NULL,
regionkey INTEGER NOT NULL,
comment   VARCHAR(152),
FOREIGN KEY (regionkey) REFERENCES l_regions(regionkey));
```  
Return the regions for the nations:  
```sql
SELECT n.*, r.*
FROM l_nations n
JOIN l_regions r USING (regionkey);
```  
The expressions do not have to be equalities – any predicate can be used:  
Return the pairs of jobs where one ran longer but cost less:  
```sql
SELECT s1.t_id, s2.t_id
FROM west s1, west s2
WHERE s1.time > s2.time
AND s1.cost < s2.cost;
```  
#### Natural Joins {#docs:sql:query_syntax:from::natural-joins}  
Natural joins join two tables based on attributes that share the same name.  
For example, take the following example with cities, airport codes and airport names. Note that both tables are intentionally incomplete, i.e., they do not have a matching pair in the other table.  
```sql
CREATE TABLE city_airport (city_name VARCHAR, iata VARCHAR);
CREATE TABLE airport_names (iata VARCHAR, airport_name VARCHAR);
INSERT INTO city_airport VALUES
('Amsterdam', 'AMS'),
('Rotterdam', 'RTM'),
('Eindhoven', 'EIN'),
('Groningen', 'GRQ');
INSERT INTO airport_names VALUES
('AMS', 'Amsterdam Airport Schiphol'),
('RTM', 'Rotterdam The Hague Airport'),
('MST', 'Maastricht Aachen Airport');
```  
To join the tables on their shared [`IATA`](https://en.wikipedia.org/wiki/IATA_airport_code) attributes, run:  
```sql
SELECT *
FROM city_airport
NATURAL JOIN airport_names;
```  
This produces the following result:  
| city_name | iata |        airport_name         |
|-----------|------|-----------------------------|
| Amsterdam | AMS  | Amsterdam Airport Schiphol  |
| Rotterdam | RTM  | Rotterdam The Hague Airport |  
Note that only rows where the same `iata` attribute was present in both tables were included in the result.  
We can also express query using the vanilla `JOIN` clause with the `USING` keyword:  
```sql
SELECT *
FROM city_airport
JOIN airport_names
USING (iata);
```  
#### Semi and Anti Joins {#docs:sql:query_syntax:from::semi-and-anti-joins}  
Semi joins return rows from the left table that have at least one match in the right table.
Anti joins return rows from the left table that have _no_ matches in the right table.
When using a semi or anti join the result will never have more rows than the left hand side table.
Semi joins provide the same logic as the [`IN` operator](#docs:sql:expressions:in) statement.
Anti joins provide the same logic as the `NOT IN` operator, except anti joins ignore `NULL` values from the right table.