JOIN

The FDB Record Layer supports INNER JOIN, LEFT OUTER JOIN, and RIGHT OUTER JOIN. All three accept an ON condition or a USING column list. The FULL OUTER JOIN variant is not yet supported.

INNER JOIN

An INNER JOIN combines rows from two sources based on a join condition. It returns only the rows where the join condition is satisfied in both tables.

Syntax

SELECT columns
FROM table1 INNER JOIN table2
ON table1.column = table2.column

The INNER keyword is optional: JOIN can be used as a shorthand for INNER JOIN.

Parameters

source

The table or subquery to join with.

ON joinCondition

A boolean expression that specifies the join condition. Only rows where this condition evaluates to true are included in the result.

USING (column(s))

A comma-separated list of column names that exist in both tables. This is shorthand for joining on equality of these columns. The USING clause automatically removes duplicate columns from the result set.

Returns

Returns a result set containing rows where the join condition is satisfied. For each matching pair of rows from the two sources, a combined row is produced with columns from both tables.

OUTER JOIN

An OUTER JOIN combines rows from two sources based on a join condition. Unlike an inner join, an outer join preserves every row from one of the two sides. When no matching row exists on the other side, the columns from that side are filled with NULL.

The FDB Record Layer supports LEFT OUTER JOIN and RIGHT OUTER JOIN. A LEFT OUTER JOIN preserves every row from the left side; a RIGHT OUTER JOIN preserves every row from the right side. Aside from the order of the produced columns, the two are equivalent: A LEFT OUTER JOIN B ON … produces the same set of rows as B RIGHT OUTER JOIN A ON ….

Syntax

SELECT columns
FROM table1 LEFT OUTER JOIN table2
ON table1.column = table2.column

SELECT columns
FROM table1 RIGHT OUTER JOIN table2
ON table1.column = table2.column

The OUTER keyword is optional: LEFT JOIN and LEFT OUTER JOIN are equivalent, as are RIGHT JOIN and RIGHT OUTER JOIN.

Parameters

source

The table or subquery to join with.

ON joinCondition

A boolean expression that specifies the join condition. Rows on the preserved side (left for LEFT OUTER JOIN, right for RIGHT OUTER JOIN) that have no match on the other side produce a single output row with NULL in every column from the other side.

USING (column(s))

A comma-separated list of column names that exist in both tables. This is shorthand for joining on equality of these columns. As with INNER JOIN … USING, duplicate columns are hidden from the result set.

Returns

Returns a result set containing:

• One combined row for every matching pair (same as an inner join).

• One null-padded row for every row on the preserved side that has no match on the other side.

Every row from the preserved side therefore appears at least once in the output.

Examples

For the examples below, assume the following tables:

CREATE TABLE emp (
    id BIGINT, fname STRING, lname STRING, dept_id BIGINT,
    PRIMARY KEY(id)
)
CREATE TABLE dept (
    id BIGINT, name STRING,
    PRIMARY KEY(id)
)

INSERT INTO emp VALUES
    (1, 'Alice', 'Smith', 1),
    (2, 'Bob',   'Jones', 1),
    (3, 'Carol', 'Lee',   2),
    (4, 'Dave',  'Kim',  99)

INSERT INTO dept VALUES
    (1, 'Engineering'),
    (2, 'Sales'),
    (3, 'Marketing')

Basic LEFT OUTER JOIN

Return every employee together with their department. Dave (dept_id = 99) has no matching department, so the department name in the result is NULL.

SELECT e.fname, e.lname, d.name
FROM emp e LEFT OUTER JOIN dept d ON e.dept_id = d.id

fname	lname	name
"Alice"	"Smith"	"Engineering"
"Bob"	"Jones"	"Engineering"
"Carol"	"Lee"	"Sales"
"Dave"	"Kim"	NULL

RIGHT OUTER JOIN

Because A LEFT OUTER JOIN B and B RIGHT OUTER JOIN A produce the same set of rows, the query above can equivalently be written as:

SELECT e.fname, e.lname, d.name
FROM dept d RIGHT OUTER JOIN emp e ON e.dept_id = d.id

Use whichever direction reads more naturally for the query at hand.

Anti-Join Pattern

Use LEFT OUTER JOIN with WHERE … IS NULL on the right side to find only the rows with no match. This is a common way to express an anti-join in SQL.

SELECT e.fname, e.lname
FROM emp e LEFT JOIN dept d ON e.dept_id = d.id
WHERE d.id IS NULL

fname	lname
"Dave"	"Kim"

Chained Joins

A LEFT OUTER JOIN can follow an INNER JOIN or another LEFT OUTER JOIN:

SELECT e.fname, d.name, p.name
FROM emp e
JOIN dept d ON e.dept_id = d.id
LEFT JOIN project p ON e.id = p.emp_id

For further examples, see Joins.

See Also

• SELECT - SELECT statement syntax

• WHERE - WHERE clause filtering

• Subqueries - Subqueries and correlated subqueries

• WITH - Common Table Expressions

• Joins - General overview of joins, with examples