# Joins
```php
$handler->selectStatement(null, ['t1.name', 't2.salary'])
->from(['t1' => 'employee'])
->join('info', 't2', 'INNER')
->on('t1.name = t2.name')
->fetchAll();
// SELECT t1.name, t2.salary FROM employee AS `t1` INNER JOIN info AS `t2` ON (t1.name = t2.name)
```
`join($tables, $alias = null, $type = null)` starts a join; `on(...$conditions)` adds conditions to the one
started last, AND-ed together. Several joins chain.
ON conditions are **raw SQL fragments**, not filters: a join compares two columns, while a filter compares a
column to a value it would bind. `['t1.id' => 't2.id']` would look for the literal string `'t2.id'`.
Several tables joined at once share one ON, as a parenthesised group:
```php
->from('t1')->join(['t2', 't3', 't4'], null, 'LEFT')->on('t2.a = t1.a', 't3.b = t1.b', 't4.c = t1.c')
// SELECT * FROM t1 LEFT JOIN (t2, t3, t4) ON (t2.a = t1.a AND t3.b = t1.b AND t4.c = t1.c)
```
Filters, ordering and limits all still apply, and a qualified column in a filter is quoted per segment
(`` `t1`.`id` ``).
## Not automatic
A `Query` filter on a nested path — `['author/name' => 'marc']`, meaning "a property of the related author" —
is **not** turned into a join. Nothing in the package knows which columns relate two tables; describing that
is what `helpers\Liaison` was meant to do and it is not built. Build the join yourself and filter on
`alias.column`.
## Join types, for reference
Given:
| id | | | | id | |
|----|---|---|---|----|---|
| 1 | A | | | 1 | C |
| 2 | B | | | 3 | D |
*table1* (left) and *table2* (right).
**`CROSS JOIN`** — every combination, no condition.
| t1.id | | t2.id | |
|---|---|---|---|
| 1 | A | 1 | C |
| 1 | A | 3 | D |
| 2 | B | 1 | C |
| 2 | B | 3 | D |
**`JOIN`** (inner) — only matching rows.
| t1.id | | t2.id | |
|---|---|---|---|
| 1 | A | 1 | C |
**`LEFT JOIN`** — every left row, matched or not.
| t1.id | | t2.id | |
|---|---|---|---|
| 1 | A | 1 | C |
| 2 | B | | |
**`RIGHT JOIN`** — every right row.
| t1.id | | t2.id | |
|---|---|---|---|
| 1 | A | 1 | C |
| | | 3 | D |
**`FULL JOIN`** — every row of both. Not supported by MySQL or SQLite; emulate with `LEFT … UNION … RIGHT`.
| t1.id | | t2.id | |
|---|---|---|---|
| 1 | A | 1 | C |
| 2 | B | | |
| | | 3 | D |