What you will be able to do
- Write INNER, LEFT, RIGHT and FULL joins with ON or USING, and predict which unmatched rows each one keeps
- Join tables on several key columns, using either ON with AND or USING with a column list
- Use SEMI, ANTI and CROSS joins and recognise when a join quietly turns into a Cartesian product
- Combine query results with UNION or UNION ALL, and know the column-count and type rules that set operators enforce
Key concept
Join type decides which unmatched rows survive — Every join pairs up rows whose keys match. The join type only decides what happens to rows that find no partner: drop them (INNER), keep them from one side (LEFT or RIGHT), or keep them from both sides (FULL), with NULL filling the columns from the missing side.
1.The parts of a JOIN clause
Databricks SQL uses standard ANSI join syntax. A join clause follows a left table reference and has three parts: an optional join type, the keyword JOIN with a right table reference, and optional join criteria. The join types you can write are [INNER], LEFT [OUTER], RIGHT [OUTER], FULL [OUTER], [LEFT] SEMI, [LEFT] ANTI and CROSS. The words in brackets are optional, so LEFT JOIN and LEFT OUTER JOIN mean the same thing. If you write a bare JOIN, you get an inner join, because INNER is the default.
The join criteria tell Databricks how to pair rows. There are two forms:
- ON boolean_expression: any expression that returns BOOLEAN. A pair of rows counts as a match when it evaluates to true. If the expression doesn't return a BOOLEAN (for example ON 1), Databricks raises JOIN_CONDITION_IS_NOT_BOOLEAN_TYPE.
- USING (column_name [, ...]): matches rows on equality of the listed columns, which must exist in both tables. If a column is missing from one side, Databricks raises UNRESOLVED_USING_COLUMN_FOR_JOIN.
A third form, NATURAL, writes the criteria for you: it matches on equality of every column whose name appears in both tables. NATURAL can't be combined with CROSS. Trying it raises INCOMPATIBLE_JOIN_TYPES.
Checkpoint 1 of 9· Check yourself
An analyst writes SELECT * FROM orders LEFT JOIN customers and forgets the ON clause. What happens?
With no join criteria, any join type takes on CROSS JOIN semantics. Matching on same-named columns only happens when you write NATURAL explicitly.
“If you omit the join_criteria the semantic of any join_type becomes that of a CROSS JOIN.”Source: docs.databricks.com
2.INNER, LEFT, RIGHT and FULL on the same data
The Databricks reference uses two small temporary views to compare the join types. employee has six people (Chloe, Paul, John, Lisa, Evan, Amy) in departments 5, 3, 1, 2, 4 and 6. department has only three departments: 3 Engineering, 2 Sales and 1 Marketing. Three employees (Chloe, Evan, Amy) belong to departments that don't exist in department. Every department, though, has at least one employee.
An inner join keeps only rows whose key appears on both sides. Chloe, Evan and Amy have no matching department, so they're dropped:
> SELECT id, name, employee.deptno, deptname
FROM employee
INNER JOIN department ON employee.deptno = department.deptno;
103 Paul 3 Engineering
101 John 1 Marketing
102 Lisa 2 SalesA left (outer) join keeps every row from the left table. Where there's no match, the right table's columns are filled with NULL. All six employees come back, and the three without a department show NULL for deptname:
> SELECT id, name, employee.deptno, deptname
FROM employee
LEFT JOIN department ON employee.deptno = department.deptno;
105 Chloe 5 NULL
103 Paul 3 Engineering
101 John 1 Marketing
102 Lisa 2 Sales
104 Evan 4 NULL
106 Amy 6 NULLA right (outer) join is the mirror of a left join: it keeps every row from the *right* table and fills the left table's columns with NULL where there's no match. Here the right table is department, and every department has an employee, so the result is the same three rows as the inner join. The unmatched employees disappear because a right join only protects right-side rows. A full (outer) join keeps unmatched rows from both sides. In this example that gives the same six rows as the left join, because the right side has no unmatched rows to add.
| Join type | Unmatched left rows | Unmatched right rows | Rows in example |
|---|---|---|---|
| [INNER] JOIN | Dropped | Dropped | 3 |
| LEFT [OUTER] JOIN | Kept, right columns NULL | Dropped | 6 |
| RIGHT [OUTER] JOIN | Dropped | Kept, left columns NULL | 3 |
| FULL [OUTER] JOIN | Kept, right columns NULL | Kept, left columns NULL | 6 |
Checkpoint 2 of 9· Match them up
Match each join type to the rows it returns
Tap a term, then the definition that fits it.
Each outer join keeps the unmatched rows of the side it names (FULL names both) and fills the other side's columns with NULL. INNER keeps no unmatched rows.
“Returns the rows that have matching values in both table references. The default join-type.”Source: docs.databricks.com
Checkpoint 3 of 9· Exam question
An analyst needs a report of every `orders` row paired with the matching `customers` row, using `orders.customer_id = customers.customer_id`. Orders that reference a customer_id no longer present in `customers` must be dropped from the result entirely. Which query returns exactly that result set? ``` SELECT o.order_id, c.customer_name FROM orders o ___ customers c ON o.customer_id = c.customer_id ```
Correct answer: A — INNER JOIN, because it returns only rows whose join key matches in both tables, dropping unmatched orders
- A. This is correct: an inner join only returns rows where the join predicate is satisfied on both sides, so any order whose customer_id has no matching row in customers is excluded, exactly as the scenario requires.
- B. A left join preserves every row from the left table (orders) even when there is no match, producing NULL customer_name values instead of dropping the unmatched orders, which does not meet the requirement.
- C. A full outer join keeps unmatched rows from both orders and customers with NULLs filling the missing side, so orphaned orders would still appear in the output rather than being dropped.
- D. A cross join produces the Cartesian product of both tables and ignores the join predicate entirely, generating far more rows than intended and pairing orders with unrelated customers.
Sources1
3.Joining on one key or several
So far each join matched on one column, deptno. Real tables are often keyed by a combination of columns, and a row only matches when *all* of them agree. Both forms of join criteria handle this. With ON, join the equality tests with AND, because ON accepts any BOOLEAN expression. With USING, list every key column in the parentheses.
The Databricks reference spells out exactly what USING does for a two-column key:
SELECT * FROM first JOIN second USING (a, b)SELECT first.a, first.b,
first.* EXCEPT(a, b),
second.* EXCEPT(a, b)
FROM first JOIN second ON first.a = second.a AND first.b = second.bThe equivalence shows two things. First, the matching logic is the same: both key columns must be equal. Second, the *output shape* differs. With USING (or NATURAL), SELECT * returns each key column once, first, then the remaining left columns, then the remaining right columns. With ON, both tables keep their own copies of the key columns. That's why the earlier examples wrote employee.deptno rather than plain deptno: with ON, both sides have a deptno, and an unqualified name is ambiguous. The reference lists AMBIGUOUS_COLUMN_REFERENCE and AMBIGUOUS_REFERENCE among the errors for this clause.
Checkpoint 4 of 9· Fill the gap
Which keyword completes this join so it matches rows on equality of columns a and b, and returns each key column only once under SELECT *?
SELECT * FROM first JOIN second ? (a, b)USING takes a list of column names that must exist in both relations and matches rows on equality of each one. ON needs a BOOLEAN expression rather than a column list, and NATURAL doesn't take a column list at all.
Source: docs.databricks.comCheckpoint 5 of 9· Exam question
A retail team wants to identify customers in the `customers` table who have never placed an order in the `orders` table, joining on `customer_id`. The output should only include columns from `customers`, with one row per customer that has zero matching orders. Which approach satisfies this requirement most directly?
Correct answer: A — Use a LEFT ANTI JOIN from customers to orders on customer_id, which returns only customer rows that have no matching row in orders
- A. A LEFT ANTI JOIN returns rows from the left table that have no matching row in the right table and never includes columns from the right side, so it directly returns customers with zero orders in one step.
- B. An inner join drops unmatched rows before any filter can run, so a customer with no orders is never present in the joined result and the IS NULL filter would never see it; the predicate is also contradictory with an inner join's matching semantics.
- C. This works correctly for a left join, but the question asks for the most direct approach, and a left join followed by a NULL filter still requires selecting and then discarding matched rows rather than producing exactly the anti-join result in one construct.
- D. A right join preserves all rows from orders, not from customers, so this would keep every order and would not isolate customers lacking any order at all.
Sources1
4.SEMI, ANTI and CROSS joins
The outer joins answer the question: show me these rows, plus related columns where they exist. Two other join types answer a narrower question: *does* a match exist? They return only left-side columns and never repeat or widen a row.
- **[LEFT] SEMI JOIN returns the left rows that have a match on the right.
- [LEFT] ANTI JOIN** returns the left rows that have *no* match on the right.
On the example data, a semi join returns Paul, John and Lisa with only the employee columns (id, name, deptno). An anti join returns the other three:
> SELECT *
FROM employee
ANTI JOIN department ON employee.deptno = department.deptno;
105 Chloe 5
104 Evan 4
106 Amy 6Compare the anti join with the left join from earlier. The anti join returns the same three people that the left join padded with NULL, but without the department columns. When the question is "which employees have an invalid department?", the anti join answers it directly.
At the other extreme, **CROSS JOIN** takes no criteria and returns the Cartesian product: every left row paired with every right row. Six employees crossed with three departments gives 18 rows. Use it on purpose (for example, to build every combination of two small lists). Don't let it happen by accident through a missing ON.
Three rows (Paul, John, Lisa) and three columns (id, name, deptno). A semi join only filters the left table. It never adds the right table's columns.
Checkpoint 6 of 9· Check yourself
You need the list of customers who have never placed an order, with only the customer columns in the result. Which join fits best?
An anti join keeps the left rows that have no match on the right and returns only left-side columns. A semi join does the opposite: it keeps the customers who *do* have orders.
“Returns the values from the left table reference that have no match with the right table reference.”Source: docs.databricks.com
Sources1
5.Stacking results: UNION vs UNION ALL
Joins combine tables *side by side*, adding columns. Set operators combine query results *on top of each other*, adding rows. Databricks supports three: UNION, INTERSECT and EXCEPT (MINUS is accepted as an alternative spelling of EXCEPT). Because the rows are stacked, both queries must have the same shape: the same number of columns, and a least common type for each pair of columns. A column-count mismatch raises NUM_COLUMNS_MISMATCH, and incompatible types raise INCOMPATIBLE_COLUMN_TYPE. Columns are paired by position, and each result column takes the least common type of the pair.
The reference demonstrates set operators with two single-column views that both contain duplicates:
> CREATE TEMPORARY VIEW number1(c) AS VALUES (3), (1), (2), (2), (3), (4);
> CREATE TEMPORARY VIEW number2(c) AS VALUES (5), (1), (1), (2);> (SELECT c FROM number1) UNION (SELECT c FROM number2);
1
3
5
4
2UNION ALL skips the duplicate removal and returns every row from both inputs: 6 + 4 = 10 rows, including the repeated 1s, 2s and 3s. Choose based on meaning. If you're stacking two months of transactions, a repeated row is real data, and UNION would silently drop it. If you're building a list of distinct customer IDs from two sources, UNION gives you the deduplicated list.
Checkpoint 7 of 9· Fill the gap
This query returned all 10 rows, duplicates included. Which set operator fills the blank?
> SELECT c FROM number1 ? ALL (SELECT c FROM number2);
3
1
2
2
3
4
5
1
1
2UNION ALL returns every row of the first query followed by every row of the second. INTERSECT ALL would return only 1, 2, 2, and EXCEPT ALL only 3, 3, 4.
Source: docs.databricks.comThe other two operators follow the same ALL | DISTINCT pattern, and DISTINCT is again the default. INTERSECT returns rows found in both queries. EXCEPT returns rows of the first query that aren't in the second. With EXCEPT ALL, each row in the second query removes exactly one matching row from the first. When you chain operators without parentheses, INTERSECT binds more tightly than UNION and EXCEPT.
Checkpoint 8 of 9· Check yourself
SELECT id, name FROM current_customers UNION ALL SELECT id FROM archived_customers is run. What happens?
Set operators need the same number of columns on both sides, with or without ALL. Databricks doesn't pad the narrower query with NULLs. It raises an error.
“If the number of columns differs, Databricks raises NUM_COLUMNS_MISMATCH.”Source: docs.databricks.com
Checkpoint 9 of 9· Exam question
A finance analyst writes `SELECT o.order_id, c.customer_name FROM orders o RIGHT JOIN customers c ON o.customer_id = c.customer_id`. How does the result set differ from writing the equivalent query as `SELECT o.order_id, c.customer_name FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id`?
Correct answer: A — The two queries return the same rows, because a right join preserves every customer row, matching a left join from customers
- A. RIGHT JOIN keeps every row from the table on the right side of the join, so `orders RIGHT JOIN customers` preserves all customers; this is semantically identical to `customers LEFT JOIN orders`, which preserves all customers on the left, so the row sets match.
- B. This describes an inner join's behavior, not a right join; a right join still preserves unmatched rows from the preserved side, so customers with zero orders remain in the output with NULL order_id values.
- C. Neither join type introduces duplicate rows on its own; duplicates only occur if the join key is non-unique on one side, which is unrelated to choosing RIGHT versus LEFT here.
- D. Column order in the SELECT list is controlled by the query author, not by which side is designated LEFT or RIGHT, and the join key itself is unchanged between the two equivalent formulations.
Sources3
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.UNION simply appends one result to the other, so it returns the combined row count.Why is that wrong?
A plain UNION is UNION DISTINCT and removes duplicate rows. Only UNION ALL keeps every row from both inputs.
Covered in Stacking results: UNION vs UNION ALL
2.A LEFT or INNER JOIN with no ON clause fails, or falls back to matching on same-named columns.Why is that wrong?
Leaving out the join criteria turns any join type into a CROSS JOIN. Matching on same-named columns only happens when you write NATURAL.
Covered in The parts of a JOIN clause
3.A RIGHT JOIN always returns more rows than an INNER JOIN on the same tables.Why is that wrong?
A right join only adds NULL-padded rows for right-side rows with no match. If every right row has a match, as in the department example, it returns exactly the inner-join rows.
Covered in INNER, LEFT, RIGHT and FULL on the same data
4.SELECT * over a USING join returns the key columns twice, once from each table, just like an ON join.Why is that wrong?
With USING or NATURAL, SELECT * shows each join column once, placed first, followed by the remaining left columns and then the remaining right columns.
Covered in Joining on one key or several
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Returns the rows that have matching values in both table references. The default join-type.”
↩︎ The parts of a JOIN clause“If the result is true the rows are considered a match.”
↩︎ The parts of a JOIN clause“Specifies that the rows from the two relations will implicitly be matched on equality for all columns with matching names.”
↩︎ The parts of a JOIN clause“Returns all values from both relations, appending NULL values on the side that does not have a match.”
↩︎ INNER, LEFT, RIGHT and FULL on the same data“Returns all values from the right table reference and the matched values from the left table reference”
↩︎ INNER, LEFT, RIGHT and FULL on the same data“Matches the rows by comparing equality for list of columns column_name which must exist in both relations.”
↩︎ Joining on one key or several“SELECT * will only show one occurrence for each of the columns used to match first”
↩︎ Joining on one key or several“Returns values from the left side of the table reference that has a match with the right.”
↩︎ SEMI, ANTI and CROSS joins“Returns the Cartesian product of two relations.”
↩︎ SEMI, ANTI and CROSS joins“Returns all values from both relations, appending NULL values on the side that does not have a match.”
↩︎ Key concept“If you omit the join_criteria the semantic of any join_type becomes that of a CROSS JOIN.”
↩︎ Exam trap 2“Returns all values from the right table reference and the matched values from the left table reference”
↩︎ Exam trap 3“SELECT * will only show one occurrence for each of the columns used to match first”
↩︎ Exam trap 4“If you omit the join_criteria the semantic of any join_type becomes that of a CROSS JOIN.”
↩︎ Checkpoint“Returns the rows that have matching values in both table references. The default join-type.”
↩︎ Checkpoint“Returns the values from the left table reference that have no match with the right table reference.”
↩︎ Checkpoint - 2.https://docs.databricks.com/aws/en/transform/joinOfficial docs
“Databricks supports standard SQL join syntax, including inner, outer, semi, anti, and cross joins.”
↩︎ The parts of a JOIN clause - 3.https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-qry-select-setopsOfficial docs
“Both subqueries must have the same number of columns and share a least common type for each respective column.”
↩︎ Stacking results: UNION vs UNION ALL“If ALL is specified duplicate rows are preserved. If DISTINCT is specified the result does not contain any duplicate rows. This is the default.”
↩︎ Stacking results: UNION vs UNION ALL“When chaining set operations INTERSECT has a higher precedence than UNION and EXCEPT.”
↩︎ Stacking results: UNION vs UNION ALL“Returns the rows in subquery1 which are not in subquery2.”
↩︎ Stacking results: UNION vs UNION ALL“If ALL is specified duplicate rows are preserved. If DISTINCT is specified the result does not contain any duplicate rows. This is the default.”
↩︎ Exam trap 1“If the number of columns differs, Databricks raises NUM_COLUMNS_MISMATCH.”
↩︎ Checkpoint