In-depth practical guides

Check a join before combining two files

Measure empty keys, duplicates, unmatched rows and row multiplication before publishing linked data.

Updated :

File and table checks at an organised workstation.

AI-generated illustration

Does the join preserve what a row means?

A technically successful join can distort a total. The cause is often the grain: one file describes businesses, the other establishments or periods. Two occurrences on the left and three on the right produce six associations for one key. Check the join before calculating sums or rates.

  1. Define grain and contract

    State what one row represents in each file, the period and expected cardinality: one-to-one or many-to-one. If the right file has multiple periods, add period to the key or filter it explicitly.

  2. Preserve identifiers

    Load codes and references as text. Keep leading zeros, case and spaces unless a normalization rule is justified. A failed exact match requires investigation, not an automatic fuzzy merge.

  3. Inspect keys

    Count empty keys, groups of repeated keys and unmatched rows. A repeated key may be legitimate at a finer grain. Decide whether to retain a version, aggregate or expand the key before deduplication.

  4. Compare volumes

    Calculate expected inner and left join row counts. Then check counts and sums before and after; an increase is acceptable only if the grain change is intended and documented.

Reading workshop

Each occurrence makes its own pairs

The DEMO lists are fictional. A row is a retained occurrence, not an already deduplicated entity.

Fictional data and records for learning: none of these results comes from a live service query.

A,A,B,C against A,A,B,D

Codes are compared exactly. Positions distinguish the two A rows on each side; no row is removed before matching.

If the table exceeds the screen, scroll horizontally; arrow keys work when the region is focused.

A,A,B,C against A,A,B,D
KeyLeft / right occurrencesInner pairsEffect in a left join
A2 / 22 × 2 = 4Four rows, not two.
B1 / 11 × 1 = 1One matched row.
C1 / 00One left row retained without a right object.
D0 / 10Absent from the left join; inspect on the right.

Explore the keys in the join reader

Which result respects the received occurrences?

Open each proposition to compare its diagnosis. More checks may still be needed.

Three rows because there are three left codes

No. The output counts row associations, not just distinct codes. A produces several pairs.

C has no right match: assign it zero

No. No match establishes no measurement. Keep unmatched status separate from a known zero.

Five inner rows and six left-join rows

Yes: four for A, one for B, plus unmatched C in the left join. The many-to-one contract fails on right-side A.

Fictional cardinality record

Left: A, A, B, C
Right: A, A, B, D
A pairs: L1-R1, L1-R2, L2-R1, L2-R2
B pair: L3-R3
Inner: 5; left: 6
Unmatched: left C, right D

L and R positions are teaching locators. C and D rows do not become zero-valued observations.

Documentation of the rules, separate from fictional examples

Compare other situations and exercises

Two rows meet three

A key occurring twice on the left and three times on the right generates six inner join rows. For an intended many-to-one relationship, correct the right file or refine the key first.

Identifier with a leading zero

The strings 00123 and 123 differ. If a spreadsheet removed a leading zero, return to the source and dictionary. Do not automatically pad identifiers: an assumed length can create a false match.

Supporting documents double a total

Fictional example: two projects have amounts of 100 and 200 euros. Each has two supporting documents. A project-document join produces four rows and an apparent total of 600 instead of 300. Keep amounts at project level and documents in a separate relationship, or explicitly define an appropriate aggregation.

A filter changes a left join’s result

Illustrative SQL case: after a left join, WHERE status = 'active' on the right table also removes unmatched rows. Putting the condition in ON retains left rows, with missing right fields when no active object matches. Choose according to the question and record where the filter applies.

A composite key keeps periods separate

Fictional example: the left file contains (A, 2025) and (A, 2026); the right file contains (A, 2026) and (B, 2026). With id then period as the key, one row matches on each side. Inner, left, right and full joins contain 1, 2, 2 and 3 rows respectively. Using id alone would associate both periods of A with the same right object.

From observation to decision

An exact join can inflate a total by 60%

Orders A = 60 and B = 40 total 100 monetary units. A category table contains A → X, A → Y and B → Z. Joining on order ID is exact, but order A appears twice.

Fictional learning case: counts and dates illustrate the method and do not describe a real assignment.

The join amplifies an unchanged source total

Scale from 0 to 200 fictional monetary units. The second total repeats A’s amount.

  • Before join · 2 orders100
  • After join · 3 rows160
Case reading grid
ObservationUseful checkNext action
A → XAmount 60, first match for A.Category X receives a row; the order amount is repeated.
A → YAmount 60, second match for A.The relationship may be valid without making the amount additive across categories.
B → ZAmount 40, one match.The joined total becomes 60 + 60 + 40 = 160.
Your decision — Does SUM(DISTINCT amount) fix it?

Explained answer

No. Different orders may have the same amount; this expression would collapse them. Keep order IDs, check expected cardinality and decide whether to select a category, document an allocation or maintain a separate relationship table. Do not arbitrarily keep the first match.

Reference: a join produces a row for each eligible match. The orders and amounts in this case are fictional. PostgreSQL 18 — Joined Tables

Worked exercise

Predict volume before joining

Fictional example: the values and documents teach the method and do not describe a real organisation.

Each column is an independent table: positions are not matched pairs. Keys are exact codes; the intended relation is many-to-one.

If the table exceeds the screen, scroll horizontally; arrow keys work when the region is focused.

Starting information
PositionLeft keyRight key
1AA
2AA
3BA
4CB
5No rowD

Your checks

  1. Count occurrences per key on each side.
  2. Predict the pairs in an inner join.
  3. Check the contract before summing.
Read the answer

Answer and decision

A creates 2 × 3 = 6 pairs and B creates 1: 7 inner rows. A left join yields 8 including unmatched C; a full join yields 9 including D. The contract fails because A repeats on the right. Left-side totals would be multiplied.

If the case changes

If the three A rows represent legitimate periods, add period to the key instead of arbitrarily deleting two rows.

Record to retain

Keep cardinalities, unmatched keys, predicted volumes and the grain rule.

DOCUMENTED REFERENCE

COG 2026: one code does not distinguish every territorial object

France’s 1 January 2026 COG has several entity types. Insee allows a commune and delegated commune to share a code. Twenty official CSV records checked 22 October 2026 illustrate joins, not an exhaustive directory or population series.

COM + 01015Arboys en BugeyCOMD + 01015ArbignieuCOG 2026 · TYPECOM + COM
COG 2026: one code does not distinguish every territorial object
ReferenceValue or formWhat it identifies
COMCommuneCommune: COM 01015 is Arboys en Bugey. Do not sum it with delegated parts as disjoint territories.
COMAAssociated communeAssociated commune: Cordieux 01120 has parent 01262. Type does not justify copying statistics everywhere.
COMDDelegated communeDelegated commune: COMD 01015 is Arbignieu, while COM 01015 is its parent. Code-only joins can multiply rows.
ARMMunicipal arrondissementMunicipal arrondissement: 75101 and 75120 have Paris 75056 as parent, distinct from departmental administrative arrondissements.
COM field5 characters · textCOM is five-character text preserving zeros and letters, e.g. Ajaccio 2A004. Do not infer every relationship from prefixes.
COMPARENTParent commune codeParent-commune link. Select the intended parent type; two 01015 rows make untyped joins ambiguous.
CTCD · Mayotte976R · 2026-01-01Publication assigns 976R to Mayotte’s departmental-regional collectivity from 1 January 2026. CTCD differs from DEP, REG and COM.

Twenty COG 2026 objects

As of 1 January 2026 · checked 22 October 2026. Names and six fields come from the official CSV. “—” marks source-empty fields, not inferred values.

Twenty COG 2026 objects
TypeCodeOfficial nameParent communeDepartmentRegion
COM75056Paris—7511
ARM75101Paris 1er Arrondissement750567511
ARM75102Paris 2e Arrondissement750567511
ARM75120Paris 20e Arrondissement750567511
COM69123Lyon—6984
ARM69381Lyon 1er Arrondissement691236984
ARM69382Lyon 2e Arrondissement691236984
COM13055Marseille—1393
ARM13201Marseille 1er Arrondissement130551393
ARM13216Marseille 16e Arrondissement130551393
COM01015Arboys en Bugey—0184
COMD01015Arbignieu01015——
COMD01340Saint-Bois01015——
COM2A004Ajaccio—2A94
COM97611Mamoudzou—97606
COM07019Aubenas—0784
COMA01120Cordieux01262——
COMA02285Escaufourt59545——
COM01262Montluel—0184
COM59545Saint-Souplet—5932

Implication for the record

Keep vintage, TYPECOM, COM and labels. Source-empty DEP or REG stays empty rather than inferred from prefixes. Postal codes are another system and may cover multiple communes. No geometry or address test is included.

Why can joining 01015 produce two matches?

Because the commune and a delegated commune share the code. Select type, vintage and relationship before deduplicating; both records are legitimate.

Primary documentation

Documentation consulted on .

DOCUMENTED REFERENCE

Spatial joins: boundary points can change the result

Predicate choice changes matches for identical coordinates. The first three rules concern 2D geometry. The illustrative polygon, hole and points represent no actual commune.

BHCovers(A, B) = trueContains(A, B) = falseA && H = trueIntersects(A, H) = falseA
Spatial joins: boundary points can change the result
ReferenceValue or formWhat it identifies
ST_Covers(A,B)interior or boundaryCovers includes interior and boundary. A boundary point can match several adjacent territories.
ST_Contains(A,B)interior intersection also requiredContains additionally requires intersecting interiors. A boundary point fails, but a polygon contains itself.
ST_Intersects(A,B)at least one common pointIntersects needs a common point; boundary contact qualifies, a point strictly in the hole does not.
A && B2D bounding boxes only&& compares 2D bounding boxes only. A point in a hole can pass without belonging to the polygon.
ST_DWithingeometry: CRS units / geography: metresGeometry distance uses CRS units with matching SRID; geography uses metres and defaults to spheroid.
ST_IsValid2D geometric validityIsValid checks 2D geometry, even with Z or M; not positional accuracy, freshness or official status.

Implication for the record

Set boundary and multiple-match rules before aggregating. Duplicate matches are not independent observations. Invalid geometries may yield unreliable predicates. Proximity does not establish address or jurisdiction.

Why can overlapping boxes give a false match?

Boxes ignore shape and holes. H is inside the box but outside the polygon surface; apply the required geometry predicate.

Primary documentation

Documentation consulted on .

DOCUMENTED REFERENCE

Unicode normalization: decide what a join may equate

Visually identical labels can encode different sequences. UAX #15 defines four forms. Ten official cases below come from Unicode 17.0.0 conformance data, explicitly versioned for reproducibility; this does not identify a browser’s Unicode version.

U+0065 + U+0301U+00E9NFCe + ◌́ → é
Unicode normalization: decide what a join may equate
ReferenceValue or formWhat it identifies
NFCCanonical compositionNFC composes canonical equivalents where possible. Decomposed e plus acute matches é without losing the accent.
NFDCanonical decompositionNFD canonically decomposes. More code points need not change appearance.
NFKCCompatibility then compositionNFKC also applies compatibility mappings then composition; width, ligature and symbol distinctions may disappear.
NFKDCompatibility decompositionNFKD uses compatibility decomposition without final composition, not transliteration or general mark removal.
U+FB01 → U+0066 U+0069fi → fi · NFKC / NFKDOfficial fi ligature stays distinct in NFC but becomes f followed by i in NFKC. Original strings were not identical.
U+00A0 → U+0020NBSP → SPACE · NFKC / NFKDNon-breaking space becomes ordinary space in compatibility forms. Normalization does not trim spaces.

Compare two Unicode sequences

Ten official Unicode 17.0.0 cases or your own text. Comparison neither trims, removes accents nor changes case. Computation stays in this browser.

Maximum 512 UTF-16 units per field; isolated surrogates are rejected.

Received sequences : Different · After normalization NFC : Identical

Compare two Unicode sequences
Text A / Text BNormalized textCode points (hexadecimal)UTF-8 bytes (hexadecimal)Code point countUTF-16 unitsUTF-8 bytes
Text AéU+00E9C3 A9112
Text BéU+00E9C3 A9112

Equal sequences establish neither personal identity nor domain equivalence. Normalization engine and version depend on the browser.

Ten official test lines, comments omitted

Columns: source, NFC, NFD, NFKC and NFKD. Each hexadecimal number is a code point. Source line numbers allow verification.

Ten official test lines, comments omitted
File linesourceNFCNFDNFKCNFKD
14100E900E90065 030100E90065 0301
461E0A 03231E0C 03070044 0323 03071E0C 03070044 0323 0307
9300A000A000A000200020
1266212B00C50041 030A00C50041 030A
13092163216321630049 00560049 0056
138924602460246000310031
2443AC00AC001100 1161AC001100 1161
14076FB01FB01FB010066 00690066 0069
14927FEFBFEFBFEFB0644 06270644 0627
14961FF21FF21FF2100410041

Unicode 17.0.0 conformance data excerpt · © 2025 Unicode, Inc. · accessed 22 October 2026.

Unicode License v3 — full text
UNICODE LICENSE V3

COPYRIGHT AND PERMISSION NOTICE

Copyright © 1991-2026 Unicode, Inc.

NOTICE TO USER: Carefully read the following legal agreement. BY
DOWNLOADING, INSTALLING, COPYING OR OTHERWISE USING DATA FILES, AND/OR
SOFTWARE, YOU UNEQUIVOCALLY ACCEPT, AND AGREE TO BE BOUND BY, ALL OF THE
TERMS AND CONDITIONS OF THIS AGREEMENT. IF YOU DO NOT AGREE, DO NOT
DOWNLOAD, INSTALL, COPY, DISTRIBUTE OR USE THE DATA FILES OR SOFTWARE.

Permission is hereby granted, free of charge, to any person obtaining a
copy of data files and any associated documentation (the "Data Files") or
software and any associated documentation (the "Software") to deal in the
Data Files or Software without restriction, including without limitation
the rights to use, copy, modify, merge, publish, distribute, and/or sell
copies of the Data Files or Software, and to permit persons to whom the
Data Files or Software are furnished to do so, provided that either (a)
this copyright and permission notice appear with all copies of the Data
Files or Software, or (b) this copyright and permission notice appear in
associated Documentation.

THE DATA FILES AND SOFTWARE ARE PROVIDED "AS IS", WITHOUT WARRANTY OF ANY
KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT OF
THIRD PARTY RIGHTS.

IN NO EVENT SHALL THE COPYRIGHT HOLDER OR HOLDERS INCLUDED IN THIS NOTICE
BE LIABLE FOR ANY CLAIM, OR ANY SPECIAL INDIRECT OR CONSEQUENTIAL DAMAGES,
OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS,
WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION,
ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THE DATA
FILES OR SOFTWARE.

Except as contained in this notice, the name of a copyright holder shall
not be used in advertising or otherwise to promote the sale, use or other
dealings in these Data Files or Software without prior written
authorization of the copyright holder.

Implication for the record

Preserve received text and comparison rule. NFC does not lowercase, remove accents or merge lookalikes from different scripts. NFKC may erase distinctions relevant to identifiers; use a schema-approved rule.

Does NFKC equality prove two names identify one person?

No. It establishes transformed-string equality, not identity. Check other attributes and provenance.

Primary documentation

Documentation consulted on .

DOCUMENTED REFERENCE

Outer joins: ON and WHERE do not retain the same rows

PostgreSQL 18 publishes two three-row teaching tables. The explorer reproduces them for nine join/filter combinations, without real entities or database access. Counts apply only to this published example.

ONOUTER JOINWHERELEFT JOIN · t2.value = 'xxx'ON → 3 · WHERE → 1
Outer joins: ON and WHERE do not retain the same rows
ReferenceValue or formWhat it identifies
INNER JOIN · t1.num = t2.num2Inner equality matches numbers 1 and 3, not unmatched 2 or 5.
LEFT JOIN · t1.num = t2.num3Left join preserves three t1 rows, padding t2 columns with SQL NULL for 2; no source row is invented.
FULL JOIN · t1.num = t2.num4Full join also retains t2 number 5, giving four output rows, not four entities in each input.
LEFT JOIN · ON … AND t2.value = 'xxx'3ON restricts matching partners to xxx; left rows b and c still survive with no retained partner.
LEFT JOIN · WHERE t2.value = 'xxx'1WHERE filters the result. Non-xxx and unknown comparisons are removed, leaving a.
NATURAL JOINAll shared column namesNATURAL uses every shared column name. A new shared column can change matches; prefer explicit conditions for stable contracts.

Explore joins on the two official teaching tables

PostgreSQL 18 teaching example, with its six input rows. Results are computed locally for fixed data; no SQL request is sent. NULL means SQL missingness, not an empty string.

The tested filter is always t2.value = 'xxx'. ON limits accepted pairs; WHERE then removes rows not satisfying the condition.

LEFT JOIN · No additional filter · Result rows : 3

Result rows
t1.numt1.namet2.numt2.value
1a1xxx
2bNULLNULL
3c3yyy
Explore joins on the two official teaching tables
COUNT(*)3
COUNT(t2.num)2
COUNT(DISTINCT t1.num)3

Distinct counts can stay stable as rows increase. This does not guarantee a correct post-join sum.

Illustrated query

SELECT t1.num, t1.name, t2.num, t2.value
FROM t1 LEFT JOIN t2 ON t1.num = t2.num;

Numbers retain provenance: t1.num and t2.num are not merged. Display order aids reading; real SQL requires ORDER BY when order matters.

Independent input tables

t1
numname
1a
2b
3c
t2
numvalue
1xxx
3yyy
5zzz

Nine counts available without interaction

Nine counts available without interaction
Join typeFilter placementCOUNT(*)COUNT(t2.num)COUNT(DISTINCT t1.num)
INNER JOINNo additional filter222
INNER JOINIn ON111
INNER JOINIn WHERE111
LEFT JOINNo additional filter323
LEFT JOINIn ON313
LEFT JOINIn WHERE111
FULL JOINNo additional filter433
FULL JOINIn ON533
FULL JOINIn WHERE111

PostgreSQL 18 documentation example · accessed 22 October 2026. Rows are not people or companies.

PostgreSQL License — full text
PostgreSQL Database Management System
 (also known as Postgres, formerly as Postgres95)


 Portions Copyright © 1996-2026, The PostgreSQL Global Development Group


 Portions Copyright © 1994, The Regents of the University of California


 Permission to use, copy, modify, and distribute this software and its
 documentation for any purpose, without fee, and without a written agreement
 is hereby granted, provided that the above copyright notice and this
 paragraph and the following two paragraphs appear in all copies.


 IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR
 DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
 LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
 DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE
 POSSIBILITY OF SUCH DAMAGE.


 THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES,
 INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
 AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS
 ON AN "AS IS" BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO
 PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

Implication for the record

Input keys are unique here. Real multiple matches can multiply rows before aggregation. Left preservation occurs before final WHERE filtering. Explorer order is illustrative, not a replacement for ORDER BY.

Why did a left join lose unmatched rows?

A right-table WHERE predicate can remove them because NULL does not satisfy ordinary equality. Choose filter placement according to the question.

Primary documentation

Documentation consulted on .

Record to retain

  • File names and versions; grain, key and period on each side.
  • Normalization rules, treatment of empty keys and duplicates.
  • Before/after counts, unmatched rows and validation decision.
  • Sums at the original grain, observed cardinality and filter placement before/after the join.

Official references

Related source profiles

Related protocols

Checks before reaching a conclusion

  • Is the right table unique on the key for a many-to-one relationship?
  • Are unmatched rows examined before being treated as zeros?
  • Is the total calculated at the same grain before and after?
  • Do composite keys use the same column order on both sides, and does every group meet the contract?

Frequently asked questions

Should every duplicate be removed?

No. First determine whether rows describe different establishments, dates or versions. Arbitrary removal can lose legitimate data.

Why not match names automatically?

A name may change or be shared. Fuzzy matching generates candidates to review, not demonstrated identity.

Does SELECT DISTINCT repair an inflated sum?

Not necessarily. Different documents create distinct rows while repeating the project amount. Check calculation grain and cardinality rather than deleting rows until an expected total appears.

Does a number of stable lines prove a good joint?

Not to. Losses and multiplications can be compensated. Examining the correspondences, keys and unparent cases remains necessary to verify that the result represents the expected relationship.

DOCUMENTED REFERENCE

Repeated keys: predict row counts before joining

An identifier appearing in both tables does not guarantee a one-to-one relationship. Each key’s frequencies determine the number of pairs. The teaching example uses two-string composite keys; FR and FI are markers only, not references to real records.

2 × 2 = 4["FR", "001"] · nL × nR
Repeated keys: predict row counts before joining
ReferenceValue or formWhat it identifies
nL = 2 · nR = 2INNER = 4A complete key occurring twice on the left and twice on the right produces four pairs. Deduplicating afterwards may conceal a scope problem.
LEFTΣ nL × max(1, nR)A left join also retains every unmatched left row. A complete key contributes nL × max(1, nR) rows.
FULLINNER + unmatched L + unmatched RA full join adds unmatched right rows. A key missing from one side does not create a Cartesian product with other keys.
NULL componenta = b AND c = dWith component equalities joined by AND, a NULL component prevents a true match. Two rows containing NULL do not automatically match each other.
composite key["FR","001"] ≠ ["FI","001"]The pair ["FR","001"] differs from ["FI","001"]. Dropping the first component changes the relationship; turning "001" into a number destroys identifier formatting.

Measure key multiplication

JSON array of 0–50 rows; each row has 1–3 components, always the same count. A component is a string of at most 64 code points or null. Both joined tables must have the same width. Maximum 20,000 JSON characters per field. An empty string remains a value distinct from null.

Teaching examples and local calculation, with no database query or transmission of inputs. Case-sensitive comparison without trimming spaces, Unicode normalisation or type conversion. Results describe entered rows only; they do not certify actual database constraints.

Teaching example calculated; edit the inputs, then examine them.

Left rows
4
Right rows
4
INNER JOIN
4
LEFT JOIN
6
FULL JOIN
8
Keys repeated on both sides
1
Contribution of each complete key and rows with NULL
Complete key or indicated groupnLnRINNERLEFTFULL
["FR","001"]22444
["FI","001"]10011
["FR","003"]01001
Left rows with NULL · unmatched10011
Right rows with NULL · unmatched01001
Initial teaching-example data
{
    "left": [
        [
            "FR",
            "001"
        ],
        [
            "FR",
            "001"
        ],
        [
            "FI",
            "001"
        ],
        [
            null,
            "002"
        ]
    ],
    "right": [
        [
            "FR",
            "001"
        ],
        [
            "FR",
            "001"
        ],
        [
            "FR",
            "003"
        ],
        [
            null,
            "002"
        ]
    ]
}

Implication for the record

The reader models exact text equalities with no additional filter. A collation, cast, normalisation or different SQL condition can change the result. Sample frequencies do not establish a constraint on the complete database. Do not sum measures repeated by a join without recovering their observation unit.

Primary documentation

Documentation consulted on .

DOCUMENTED REFERENCE

Excluding rows: NOT IN and NOT EXISTS can differ

Finding keys absent from a second table requires a policy for missing values. A NULL in a subquery result can make NOT IN unknown. These expressions concern one column; the reader also distinguishes an equality that explicitly treats NULL values as comparable.

x = "B"r = ["A", null]NOT IN → NULLNOT EXISTS (r.k = x) → TRUE
Excluding rows: NOT IN and NOT EXISTS can differ
ReferenceValue or formWhat it identifies
"B" IN ("A", NULL)NULLFor x = "B" and values ["A", null], no equality is true, but one comparison is unknown: IN and NOT IN return NULL. WHERE does not retain the row.
"A" NOT IN ("A", NULL)FALSEWith x = "A" in the same list, one true equality suffices: IN is TRUE and NOT IN is FALSE despite the NULL.
empty subqueryIN = FALSE · NOT IN = TRUEAn empty subquery gives IN = FALSE and NOT IN = TRUE, even when x is NULL. The empty JSON array represents zero returned rows, not invalid SQL syntax IN ().
NOT EXISTS · =true match onlyNOT EXISTS with WHERE r.k = l.k looks for a true match. Without one it retains the row, including when x or a right value is NULL.
NOT EXISTS · IS NOT DISTINCT FROMNULL can match NULLReplacing = with IS NOT DISTINCT FROM makes two NULLs match. A NULL-key row can then be excluded when a NULL key exists on the right: that choice expresses a different business rule.

Read four inclusion or exclusion rules

Left: a JSON string or null. Right: an array of 0–50 strings or nulls. Maximum 64 code points per string and 20,000 JSON characters per field. [] represents a subquery returning no rows.

Teaching examples and local calculation, with no database query or transmission of inputs. Case-sensitive comparison without trimming spaces, Unicode normalisation or type conversion. Results describe entered rows only; they do not certify actual database constraints.

Teaching example calculated; edit the inputs, then examine them.

Same left value, four expressions
ExpressionLogical valueEffect when used in WHERE
x IN (SELECT k FROM r)NULLRow not retained
x NOT IN (SELECT k FROM r)NULLRow not retained
NOT EXISTS (SELECT 1 FROM r WHERE r.k = x)TRUERow retained
NOT EXISTS (SELECT 1 FROM r WHERE r.k IS NOT DISTINCT FROM x)TRUERow retained
Initial teaching-example data
{
    "candidate": "B",
    "values": [
        "A",
        null
    ]
}

Implication for the record

Strings are compared exactly, without type conversion or SQL collation. The reader evaluates a local model, not a database query. Filtering right-side NULLs alone does not settle how a left NULL key should be treated: define that policy before substituting one expression for another.

Primary documentation

Documentation consulted on .

DEEPER READING / PRACTICE

Check what a data join adds or loses

Two tables must be approached by an identifier. Before interpreting the result, you need to check the unicity of the keys, the correspondences and the unparalleled lines.

Updated on .

Priority checks

Calculate the lines before and after, examine duplicates and absents, and then check some correspondances. Defining whether the expected relationship is a single or multiple correspondence.

Interpretation pitfalls

A joint can multiply lines and swell the totals. A loss of records can also change the scope without producing visible error.

Working record to retain

The joining journal retains keys, expected cardinality, facts and unparent cases. The transformations of the identifiers are documented separately.