Join Types

FULL OUTER JOIN returns every matched pair plus every unmatched row from both tables. An unmatched left row gets NULLs in all right-side columns, and an unmatched right row gets NULLs in all left-side columns.

INNER JOIN emits one output row for every pair of left and right rows whose ON predicate evaluates to TRUE. Any row on either side with no qualifying partner is dropped from the result, with no warning and no NULL placeholder.

LEFT OUTER JOIN keeps every row of the left table. When the ON predicate matches no right row, the left row is still returned once, with every right-side column filled in as NULL.

A join combines two row sources, not two files. Aliasing the same table twice creates two independently named sources, so e.manager_id and m.employee_id refer to different rows. Without aliases every column reference is ambiguous and the statement is rejected.

USING applies the same equality test as the matching ON predicate, but merges the two same-named columns into a single output column: SELECT * returns customer_id once, and the rest of the query references it unqualified.

The comma in the FROM list relates nothing, so the query is an accidental Cartesian product. The WHERE clause filters only orders, then every surviving order is paired with every customer: the result is (matching orders) times (customers) rows.

A join emits one row per qualifying pair, not per input row. If a key value appears twice on the left and three times on the right, those rows alone produce six output rows, so the result can far exceed both inputs.

LATERAL lets a derived table in the FROM clause reference columns of the FROM items written before it. Semantically the subquery is evaluated once per left-hand row, so a correlated subquery becomes a join that can return many rows and many columns.

Join users to itself on equal email and unequal key: ON a.email = b.email AND a.user_id <> b.user_id. Every row that has a twin is returned, once per twin, so use DISTINCT when three or more rows can share an address.

LEFT JOIN orders onto customers, then keep only the rows the join failed to match by testing a never-NULL right-side column in the WHERE clause, for example WHERE o.customer_id IS NULL. Those rows are the customers with no order.

Yes for inner joins: the standard defines an inner join as the Cartesian product filtered by the join predicate, so both forms return the same rows. CROSS JOIN accepts no ON clause of its own, and the explicit INNER JOIN form reads better.

A full outer join NULL-extends whichever side is unmatched, so each key column is NULL for rows that came only from the other table. COALESCE(l.customer_id, r.customer_id) merges the two into one column that is always populated.

Run a LEFT JOIN for the matched and left-only rows, then UNION ALL a second branch that keeps only the right-side rows with no left match. Use UNION ALL, not UNION, so legitimate duplicate rows survive.

For an inner join the two forms return exactly the same rows: a comma in FROM is a cross join, and the WHERE predicate then filters it. Explicit JOIN ... ON is preferred because it separates join conditions from filters and can express outer joins.

When the match is defined by containment rather than equal keys: assigning an amount to a price tier, a timestamp to a validity window, or a value to a bucket. The ON clause accepts any boolean predicate, not only column equality.

It keeps left-hand rows whose LATERAL subquery returned no rows, NULL-extending the subquery's columns instead of dropping the row. The inner forms, CROSS JOIN LATERAL and JOIN LATERAL ... ON TRUE, discard those rows entirely.

RIGHT OUTER JOIN preserves the table written after the keyword, and LEFT preserves the one written before it. Swapping the two table references and changing RIGHT to LEFT therefore yields the same rows; only the default column order changes.

The predicate a.id <> b.id keeps both orderings of every pair, so (1,2) and (2,1) both survive. Replacing it with a.id < b.id keeps exactly one ordering, cutting n(n-1) rows to n(n-1)/2 while still excluding self-pairs.

It computes each employee's salary rank: the number of distinct salaries above theirs, plus one for their own. Tied salaries share a rank and the numbering has no gaps, matching dense-rank behaviour, provided the join is a LEFT JOIN so the top earner survives.

NATURAL JOIN implicitly equates every column name the two tables share and merges each matched pair into one output column. If they share no column names the join condition is empty, so the result is a Cartesian product.

The standard makes the USING column a column of the join, referenced unqualified as dept_id. Qualifying it is not portable: Oracle rejects a qualifier on a USING column, while PostgreSQL resolves it to that table's own copy.

CROSS JOIN a date list to the product list to manufacture every (day, product) pair, LEFT JOIN the sales table on both keys, then COALESCE the aggregate to 0 so pairs with no sale still report a row.

Full outer join the two sets on the business key, COALESCE the keys into one column, then use CASE: a NULL key on one side means the row is missing there, and otherwise compare the value columns NULL-safely to flag mismatches.

Because an inner join is also a filter. Attaching a lookup table asserts that a matching row exists, so any fact row whose key is missing, orphaned or unmatched is removed from the result — silently, with no error and no NULL placeholder.

Drive the query from one row per group and join a LATERAL subquery that selects from the detail table, correlates on the group key, and carries its own ORDER BY with FETCH FIRST 3 ROWS ONLY. The limit then applies per left row, not to the whole result.

Three scalar subqueries are three independent per-row lookups, so on ties they can return columns from different rows and each must return at most one row. One LATERAL join picks a row once and exposes all of its columns together, consistently.

A join chain is evaluated left to right, so the inner join runs against the already NULL-extended intermediate result. Its predicate compares a NULL column, evaluates to unknown, and the preserved rows are discarded — turning the whole chain inner.

A join has no notion of "the row before", so the ON clause must define it: join the table to itself on the same device with an earlier timestamp, then keep only the nearest earlier row — via a MAX subquery or a NOT EXISTS check that no reading falls in between.

NATURAL JOIN re-derives its condition from whatever column names the two tables currently share, so created_at silently joined the key list and rows now match only when both timestamps are equal — almost never. Replace it with an explicit ON or USING.