SQLite adds full outer joins for relational comparison

Comparing two datasets requires keeping records that appear on only one side. An inner join removes those records, while a left join preserves unmatched rows from one chosen side. SQLite 3.39.0, released on June 25, adds RIGHT JOIN and FULL OUTER JOIN. A full join expresses preservation of unmatched rows from both inputs in one relational operation. It is particularly useful when the missing records are themselves part of the answer.

The release also adds IS DISTINCT FROM and IS NOT DISTINCT FROM. These are the SQL-standard spellings equivalent to SQLite's existing IS NOT and IS comparison operators. Together, the changes make a class of comparison queries more direct. The join determines which records are present, while null-aware comparisons determine whether their values differ under an explicitly chosen rule.

I value SQLite's ability to support substantial SQL without turning a small application into a database deployment project. Full outer joins improve that usefulness for reconciliation work. The benefit is a clearer expression of the required result, especially when absence on either side matters as much as a difference between two matched rows.

Preserving unmatched rows from both inputs

The 3.39.0 outer-join tests include chains of full joins over tables with different subsets of integer identifiers. They verify that the combined output retains the identifiers and places NULL values where an input has no corresponding row. A smaller example illustrates the same purpose with two independently populated tables.

CREATE TABLE expected(id INTEGER PRIMARY KEY, quantity INT);
CREATE TABLE observed(id INTEGER PRIMARY KEY, quantity INT);
INSERT INTO expected VALUES(1, 10), (2, 20);
INSERT INTO observed VALUES(2, 25), (3, 30);

SELECT expected.id AS expected_id,
       observed.id AS observed_id,
       expected.quantity AS expected_quantity,
       observed.quantity AS observed_quantity
FROM expected
FULL OUTER JOIN observed ON expected.id = observed.id
ORDER BY coalesce(expected.id, observed.id);

The result represents three identifiers. For identifier 1, expected data exists without an observed row. Identifier 2 has both rows, whose quantities differ. The remaining identifier, 3, has observed data without an expected row. SQL NULL occupies the selected columns of the absent side. Those NULL values arise during join evaluation, independently of whether the stored columns permit NULL.

This matters to the interpretation of a result. A primary key that cannot be NULL in a stored table can be NULL in an outer-join result because no row from that table participated. Declared storage constraints describe stored records. They do not prohibit the join from representing an absent record in its output.

The engine's SELECT implementation explicitly handles that distinction. Its comments describe a column on the left of a RIGHT JOIN as potentially NULL even when the underlying column has a NOT NULL declaration. Code that changes join expressions must retain the information that this result can be nullable. Supporting the syntax therefore requires corresponding changes in the planner's understanding of expressions.

RIGHT JOIN changes the preserved input

A RIGHT JOIN preserves unmatched rows from its right-hand input. For a simple two-table operation, an author can express the corresponding result by exchanging the inputs of a LEFT JOIN and adjusting the projection. Native support still matters for compatibility and for reading a query in the order its author chose.

The implementation represents join types with flags. In the released parser logic, a full join carries both left and right outer-join flags. This representation reflects the result's preservation requirements. The engine needs to account for rows that fail to match on either side, while an inner join needs neither preservation rule.

Changing the preserved side of a join can also change what later predicates encounter. A condition that assumes a non-NULL identifier may now see the NULL representation of an absent row. Queries that chain joins need to account for the values each earlier join contributes, rather than treat every column reference as an unconditional reference to a stored record.

The new operation is useful precisely because that complexity belongs in the query engine. An application can state that both inputs must contribute unmatched rows and let the implementation perform that relational operation. Hand-written application logic combining independently fetched datasets needs its own rules about matching and missing records. A full join places those rules in the SQL statement.

The origin of a predicate remains significant

The SELECT source explains that ON and USING conditions become extra expression terms during processing. SQLite marks them with EP_OuterON or EP_InnerON so their origin remains available to later logic. For an outer join, this distinguishes a matching condition from a general WHERE predicate even when the internal representation stores them together.

The comments give a LEFT JOIN example with a condition depending only on the left table. Applying that condition immediately after reading the left row could discard it. Deferring it to the join allows a failed match to produce a NULL-valued right side, as required. Dependencies on columns alone do not establish when a condition can safely filter rows.

That implementation detail gives a practical explanation of why moving conditions between ON and WHERE changes an outer join. An ON condition helps decide whether two rows match. A WHERE condition selects from the rows produced by the join. If the latter rejects the NULL-extended rows, an application can lose the unmatched records it introduced the outer join to preserve.

For the expected-versus-observed example, filtering to discrepancies should happen with that distinction in mind. The query needs to retain an identifier present on only one side. It also needs to retain a matched identifier when quantities differ. A predicate about quantity alone may fail to represent all the cases the comparison intends to report.

Null-aware comparisons make the policy explicit

The new standard comparison names provide a way to state a particular treatment of NULL. In this SQLite release, a IS NOT DISTINCT FROM b is equivalent to a IS b, and a IS DISTINCT FROM b is equivalent to a IS NOT b. Their purpose is compatibility and explicit spelling, with existing SQLite behavior underneath.

In the example, the following query selects an unmatched identifier or a differing quantity. The non-NULL primary keys make the missing-side checks unambiguous for these tables.

SELECT coalesce(expected.id, observed.id) AS id,
       expected.quantity,
       observed.quantity
FROM expected
FULL OUTER JOIN observed ON expected.id = observed.id
WHERE expected.id IS NULL
   OR observed.id IS NULL
   OR expected.quantity IS DISTINCT FROM observed.quantity
ORDER BY id;

The join still matches on ordinary identifier equality. The distinctness comparison applies to quantities after matching. Choosing null-aware matching for the identifiers would be a separate decision, particularly for tables whose match keys permit NULL. Keeping those decisions separate makes it easier to see which records form pairs and which differences the query reports.

For nullable quantities, the distinctness rule treats two NULL values as not distinct. Whether that is the correct policy depends on what an unknown or absent quantity means to the application. The operator makes the selected policy visible. It does not establish that two unknown measurements represent a verified equality in the business domain.

The projection also makes the source of each value visible. Selecting both identifiers separately is useful while understanding a comparison, even if an application later presents only their coalesced value. A combined identifier answers which entity the row concerns. Separate identifiers answer whether each input contributed a record. Those are different questions, and preserving both during query development helps distinguish a missing record from a present record containing nullable attributes. The example keeps the quantities separate for the same reason.

The tests cover more than one table representation

The outer-join regression suite repeats related data across ordinary tables, WITHOUT ROWID tables, and views. Some views use limits, while others combine underlying tables through UNION ALL. This matters because a query's logical inputs can have different physical implementations. Equivalent rows should retain the same join meaning across those representations.

Another test constructs a sequence of full joins across eight tables. Each contains identifiers selected by a different bit of the number. The resulting relation has 255 identifiers, each appearing once, and the test checks the relation between each identifier and the columns indicating membership in the original inputs. That is a concrete check of retaining and combining rows across repeated full joins.

These tests provide evidence about the new implementation without requiring claims about workloads that have not been measured. They focus on the result that must survive changes in representation and nesting. For reconciliation queries, the essential property is that unmatched data remains visible throughout the relational operation.

I prefer using the full join when the intended comparison needs both sides preserved. It lets the query express that requirement directly and gives the subsequent discrepancy predicate a complete set of records to evaluate. SQLite's new null-aware comparison spellings complement that operation by making the treatment of nullable values explicit in the same statement.