SQL
Joins
Return a list of city–airport code pairs from the `city_airport` table where the airport name **is not available** in the `airport_names` table:  
```sql
SELECT *
FROM city_airport
ANTI JOIN airport_names
USING (iata);
```  
| city_name | iata |
|-----------|------|
| Eindhoven | EIN  |
| Groningen | GRQ  |  
This query is equivalent with:  
```sql
SELECT *
FROM city_airport
WHERE iata NOT IN (SELECT iata FROM airport_names WHERE iata IS NOT NULL);
```  
#### Lateral Joins {#docs:sql:query_syntax:from::lateral-joins}  
The `LATERAL` keyword allows subqueries in the `FROM` clause to refer to previous subqueries. This feature is also known as a _lateral join_.  
```sql
SELECT *
FROM range(3) t(i), LATERAL (SELECT i + 1) t2(j);
```  
<div class="center_aligned_header_table"></div>  
| i | j |
|--:|--:|
| 0 | 1 |
| 2 | 3 |
| 1 | 2 |  
Lateral joins are a generalization of correlated subqueries, as they can return multiple values per input value rather than only a single value.  
```sql
SELECT *
FROM
generate_series(0, 1) t(i),
LATERAL (SELECT i + 10 UNION ALL SELECT i + 100) t2(j);
```  
<div class="center_aligned_header_table"></div>  
| i |  j  |
|--:|----:|
| 0 | 10  |
| 1 | 11  |
| 0 | 100 |
| 1 | 101 |  
It may be helpful to think about `LATERAL` as a loop where we iterate through the rows of the first subquery and use it as input to the second (` LATERAL`) subquery.
In the examples above, we iterate through table `t` and refer to its column `i` from the definition of table `t2`. The rows of `t2` form column `j` in the result.  
It is possible to refer to multiple attributes from the `LATERAL` subquery. Using the table from the first example:  
```sql
CREATE TABLE t1 AS
SELECT *
FROM range(3) t(i), LATERAL (SELECT i + 1) t2(j);

SELECT *
FROM t1, LATERAL (SELECT i + j) t2(k)
ORDER BY ALL;
```  
<div class="center_aligned_header_table"></div>  
| i | j | k |
|--:|--:|--:|
| 0 | 1 | 1 |
| 1 | 2 | 3 |
| 2 | 3 | 5 |  
> DuckDB detects when `LATERAL` joins should be used, making the use of the `LATERAL` keyword optional.  
#### Positional Joins {#docs:sql:query_syntax:from::positional-joins}  
When working with data frames or other embedded tables of the same size,
the rows may have a natural correspondence based on their physical order.
In scripting languages, this is easily expressed using a loop:  
```cpp
for (i = 0; i < n; i++) {
f(t1.a[i], t2.b[i]);
}
```  
It is difficult to express this in standard SQL because
relational tables are not ordered, but imported tables such as [data frames](#docs:api:python:data_ingestion::pandas-dataframes-–-object-columns)
or disk files (like [CSVs](#docs:data:csv:overview) or [Parquet files](#docs:data:parquet:overview)) do have a natural ordering.  
Connecting them using this ordering is called a _positional join:_  
```sql
CREATE TABLE t1 (x INTEGER);
CREATE TABLE t2 (s VARCHAR);

INSERT INTO t1 VALUES (1), (2), (3);
INSERT INTO t2 VALUES ('a'), ('b');

SELECT *
FROM t1
POSITIONAL JOIN t2;
```  
<div class="center_aligned_header_table"></div>  
| x |  s   |
|--:|------|
| 1 | a    |
| 2 | b    |
| 3 | NULL |  
Positional joins are always `FULL OUTER` joins, i.e., missing values (the last values in the shorter column) are set to `NULL`.  
#### As-Of Joins {#docs:sql:query_syntax:from::as-of-joins}  
A common operation when working with temporal or similarly-ordered data
is to find the nearest (first) event in a reference table (such as prices).
This is called an _as-of join:_  
Attach prices to stock trades:  
```sql
SELECT t.*, p.price
FROM trades t
ASOF JOIN prices p
ON t.symbol = p.symbol AND t.when >= p.when;
```  
The `ASOF` join requires at least one inequality condition on the ordering field.
The inequality can be any inequality condition (` >=`, `>`, `<=`, `<`)
on any data type, but the most common form is `>=` on a temporal type.
Any other conditions must be equalities (or `NOT DISTINCT`).
This means that the left/right order of the tables is significant.  
`ASOF` joins each left side row with at most one right side row.
It can be specified as an `OUTER` join to find unpaired rows
(e.g., trades without prices or prices which have no trades.)  
Attach prices or NULLs to stock trades:  
```sql
SELECT *
FROM trades t
ASOF LEFT JOIN prices p
ON t.symbol = p.symbol
AND t.when >= p.when;
```  
`ASOF` joins can also specify join conditions on matching column names with the `USING` syntax,
but the *last* attribute in the list must be the inequality,
which will be greater than or equal to (` >=`):  
```sql
SELECT *
FROM trades t
ASOF JOIN prices p USING (symbol, "when");
```  
Returns symbol, trades.when, price (but NOT prices.when):  
If you combine `USING` with a `SELECT *` like this,
the query will return the left side (probe) column values for the matches,
not the right side (build) column values.
To get the `prices` times in the example, you will need to list the columns explicitly:  
```sql
SELECT t.symbol, t.when AS trade_when, p.when AS price_when, price
FROM trades t
ASOF LEFT JOIN prices p USING (symbol, "when");
```  
#### Self-Joins {#docs:sql:query_syntax:from::self-joins}  
DuckDB allows self-joins for all types of joins.
Note that tables need to be aliased, using the same table name without aliases will result in an error:  
```sql
CREATE TABLE t(x int);
SELECT * FROM t JOIN t USING(x);
```  
```console
Binder Error: Duplicate alias "t" in query!
```  
Adding the aliases allows the query to parse successfully:  
```sql
SELECT * FROM t AS t t1 JOIN t t2 USING(x);
```