Joins

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.

It makes every NULL-keyed row on one side match every NULL-keyed row on the other, multiplying rows; and if the sentinel ever appears in real data, genuine rows join to missing ones. Wrapping both columns in a function also blocks ordinary index use.

Look for WHERE predicates naming the outer-joined alias: a date filter on the optional table discards NULL-extended rows and collapses the LEFT JOIN. Move the date range into the ON clause, then assert that every preserved-side row still appears.

Use NOT EXISTS with one correlated equality per key column, ANDed together. It is the portable multi-column anti-join and extends to any number of columns. A LEFT JOIN with both equalities in ON plus an IS NULL test on a target key column works too.

DISTINCT de-duplicates the whole projected row, so it also collapses rows the left table genuinely holds more than once, while EXISTS preserves left-side multiplicity exactly. It also stops helping as soon as a right-side column is projected, since those values differ per match.

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.

Pairing N rows with a one-row subquery yields N rows, attaching that subquery's value — a grand total, say — to every row, so each row can be compared against it without repeating the aggregate query in the select list.

The merged column is the coalesce of both sides, so a row present on only one side shows that side's product_id rather than NULL. With an ON join you would have to write COALESCE(a.product_id, b.product_id) yourself.

No. The two agree only for left rows with no match at all. A customer whose orders are all unpaid survives the ON version as a NULL-extended row, but the OR guard drops it, because its rows carry a non-NULL o.id.

Standardise on it when every target engine supports it or its APPLY spelling and the queries genuinely need per-row subqueries returning several rows or columns. Otherwise prefer formulations that are portable everywhere and keep the LATERAL variants isolated behind engine-specific code.