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.
In-depth practical guides
Measure empty keys, duplicates, unmatched rows and row multiplication before publishing linked data.
Updated :

AI-generated illustration
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.
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.
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.
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.
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
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.
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.
| Key | Left / right occurrences | Inner pairs | Effect in a left join |
|---|---|---|---|
| A | 2 / 2 | 2 × 2 = 4 | Four rows, not two. |
| B | 1 / 1 | 1 × 1 = 1 | One matched row. |
| C | 1 / 0 | 0 | One left row retained without a right object. |
| D | 0 / 1 | 0 | Absent from the left join; inspect on the right. |
Explore the keys in the join reader
Open each proposition to compare its diagnosis. More checks may still be needed.
No. The output counts row associations, not just distinct codes. A produces several pairs.
No. No match establishes no measurement. Keep unmatched status separate from a known zero.
Yes: four for A, one for B, plus unmatched C in the left join. The many-to-one contract fails on right-side A.
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.
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.
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.
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.
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.
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
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.
Scale from 0 to 200 fictional monetary units. The second total repeats A’s amount.
| Observation | Useful check | Next action |
|---|---|---|
| A → X | Amount 60, first match for A. | Category X receives a row; the order amount is repeated. |
| A → Y | Amount 60, second match for A. | The relationship may be valid without making the amount additive across categories. |
| B → Z | Amount 40, one match. | The joined total becomes 60 + 60 + 40 = 160. |
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
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.
| Position | Left key | Right key |
|---|---|---|
| 1 | A | A |
| 2 | A | A |
| 3 | B | A |
| 4 | C | B |
| 5 | No row | D |
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 three A rows represent legitimate periods, add period to the key instead of arbitrarily deleting two rows.
Keep cardinalities, unmatched keys, predicted volumes and the grain rule.
DOCUMENTED REFERENCE
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.
| Reference | Value or form | What it identifies |
|---|---|---|
| COM | Commune | Commune: COM 01015 is Arboys en Bugey. Do not sum it with delegated parts as disjoint territories. |
| COMA | Associated commune | Associated commune: Cordieux 01120 has parent 01262. Type does not justify copying statistics everywhere. |
| COMD | Delegated commune | Delegated commune: COMD 01015 is Arbignieu, while COM 01015 is its parent. Code-only joins can multiply rows. |
| ARM | Municipal arrondissement | Municipal arrondissement: 75101 and 75120 have Paris 75056 as parent, distinct from departmental administrative arrondissements. |
| COM field | 5 characters · text | COM is five-character text preserving zeros and letters, e.g. Ajaccio 2A004. Do not infer every relationship from prefixes. |
| COMPARENT | Parent commune code | Parent-commune link. Select the intended parent type; two 01015 rows make untyped joins ambiguous. |
| CTCD · Mayotte | 976R · 2026-01-01 | Publication assigns 976R to Mayotte’s departmental-regional collectivity from 1 January 2026. CTCD differs from DEP, REG and COM. |
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.
| Type | Code | Official name | Parent commune | Department | Region |
|---|---|---|---|---|---|
| COM | 75056 | Paris | — | 75 | 11 |
| ARM | 75101 | Paris 1er Arrondissement | 75056 | 75 | 11 |
| ARM | 75102 | Paris 2e Arrondissement | 75056 | 75 | 11 |
| ARM | 75120 | Paris 20e Arrondissement | 75056 | 75 | 11 |
| COM | 69123 | Lyon | — | 69 | 84 |
| ARM | 69381 | Lyon 1er Arrondissement | 69123 | 69 | 84 |
| ARM | 69382 | Lyon 2e Arrondissement | 69123 | 69 | 84 |
| COM | 13055 | Marseille | — | 13 | 93 |
| ARM | 13201 | Marseille 1er Arrondissement | 13055 | 13 | 93 |
| ARM | 13216 | Marseille 16e Arrondissement | 13055 | 13 | 93 |
| COM | 01015 | Arboys en Bugey | — | 01 | 84 |
| COMD | 01015 | Arbignieu | 01015 | — | — |
| COMD | 01340 | Saint-Bois | 01015 | — | — |
| COM | 2A004 | Ajaccio | — | 2A | 94 |
| COM | 97611 | Mamoudzou | — | 976 | 06 |
| COM | 07019 | Aubenas | — | 07 | 84 |
| COMA | 01120 | Cordieux | 01262 | — | — |
| COMA | 02285 | Escaufourt | 59545 | — | — |
| COM | 01262 | Montluel | — | 01 | 84 |
| COM | 59545 | Saint-Souplet | — | 59 | 32 |
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.
Because the commune and a delegated commune share the code. Select type, vintage and relationship before deduplicating; both records are legitimate.
Documentation consulted on .
DOCUMENTED REFERENCE
Predicate choice changes matches for identical coordinates. The first three rules concern 2D geometry. The illustrative polygon, hole and points represent no actual commune.
| Reference | Value or form | What it identifies |
|---|---|---|
| ST_Covers(A,B) | interior or boundary | Covers includes interior and boundary. A boundary point can match several adjacent territories. |
| ST_Contains(A,B) | interior intersection also required | Contains additionally requires intersecting interiors. A boundary point fails, but a polygon contains itself. |
| ST_Intersects(A,B) | at least one common point | Intersects needs a common point; boundary contact qualifies, a point strictly in the hole does not. |
| A && B | 2D bounding boxes only | && compares 2D bounding boxes only. A point in a hole can pass without belonging to the polygon. |
| ST_DWithin | geometry: CRS units / geography: metres | Geometry distance uses CRS units with matching SRID; geography uses metres and defaults to spheroid. |
| ST_IsValid | 2D geometric validity | IsValid checks 2D geometry, even with Z or M; not positional accuracy, freshness or official status. |
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.
Boxes ignore shape and holes. H is inside the box but outside the polygon surface; apply the required geometry predicate.
Documentation consulted on .
DOCUMENTED REFERENCE
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.
| Reference | Value or form | What it identifies |
|---|---|---|
| NFC | Canonical composition | NFC composes canonical equivalents where possible. Decomposed e plus acute matches é without losing the accent. |
| NFD | Canonical decomposition | NFD canonically decomposes. More code points need not change appearance. |
| NFKC | Compatibility then composition | NFKC also applies compatibility mappings then composition; width, ligature and symbol distinctions may disappear. |
| NFKD | Compatibility decomposition | NFKD uses compatibility decomposition without final composition, not transliteration or general mark removal. |
| U+FB01 → U+0066 U+0069 | fi → fi · NFKC / NFKD | Official fi ligature stays distinct in NFC but becomes f followed by i in NFKC. Original strings were not identical. |
| U+00A0 → U+0020 | NBSP → SPACE · NFKC / NFKD | Non-breaking space becomes ordinary space in compatibility forms. Normalization does not trim spaces. |
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
| Text A / Text B | Normalized text | Code points (hexadecimal) | UTF-8 bytes (hexadecimal) | Code point count | UTF-16 units | UTF-8 bytes |
|---|---|---|---|---|---|---|
| Text A | é | U+00E9 | C3 A9 | 1 | 1 | 2 |
| Text B | é | U+00E9 | C3 A9 | 1 | 1 | 2 |
Equal sequences establish neither personal identity nor domain equivalence. Normalization engine and version depend on the browser.
Columns: source, NFC, NFD, NFKC and NFKD. Each hexadecimal number is a code point. Source line numbers allow verification.
| File line | source | NFC | NFD | NFKC | NFKD |
|---|---|---|---|---|---|
| 141 | 00E9 | 00E9 | 0065 0301 | 00E9 | 0065 0301 |
| 46 | 1E0A 0323 | 1E0C 0307 | 0044 0323 0307 | 1E0C 0307 | 0044 0323 0307 |
| 93 | 00A0 | 00A0 | 00A0 | 0020 | 0020 |
| 1266 | 212B | 00C5 | 0041 030A | 00C5 | 0041 030A |
| 1309 | 2163 | 2163 | 2163 | 0049 0056 | 0049 0056 |
| 1389 | 2460 | 2460 | 2460 | 0031 | 0031 |
| 2443 | AC00 | AC00 | 1100 1161 | AC00 | 1100 1161 |
| 14076 | FB01 | FB01 | FB01 | 0066 0069 | 0066 0069 |
| 14927 | FEFB | FEFB | FEFB | 0644 0627 | 0644 0627 |
| 14961 | FF21 | FF21 | FF21 | 0041 | 0041 |
Unicode 17.0.0 conformance data excerpt · © 2025 Unicode, Inc. · accessed 22 October 2026.
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.
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.
No. It establishes transformed-string equality, not identity. Check other attributes and provenance.
Documentation consulted on .
DOCUMENTED REFERENCE
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.
| Reference | Value or form | What it identifies |
|---|---|---|
| INNER JOIN · t1.num = t2.num | 2 | Inner equality matches numbers 1 and 3, not unmatched 2 or 5. |
| LEFT JOIN · t1.num = t2.num | 3 | Left join preserves three t1 rows, padding t2 columns with SQL NULL for 2; no source row is invented. |
| FULL JOIN · t1.num = t2.num | 4 | Full join also retains t2 number 5, giving four output rows, not four entities in each input. |
| LEFT JOIN · ON … AND t2.value = 'xxx' | 3 | ON restricts matching partners to xxx; left rows b and c still survive with no retained partner. |
| LEFT JOIN · WHERE t2.value = 'xxx' | 1 | WHERE filters the result. Non-xxx and unknown comparisons are removed, leaving a. |
| NATURAL JOIN | All shared column names | NATURAL uses every shared column name. A new shared column can change matches; prefer explicit conditions for stable contracts. |
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
| t1.num | t1.name | t2.num | t2.value |
|---|---|---|---|
| 1 | a | 1 | xxx |
| 2 | b | NULL | NULL |
| 3 | c | 3 | yyy |
| 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.
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.
| num | name |
|---|---|
| 1 | a |
| 2 | b |
| 3 | c |
| num | value |
|---|---|
| 1 | xxx |
| 3 | yyy |
| 5 | zzz |
| Join type | Filter placement | COUNT(*) | COUNT(t2.num) | COUNT(DISTINCT t1.num) |
|---|---|---|---|---|
| INNER JOIN | No additional filter | 2 | 2 | 2 |
| INNER JOIN | In ON | 1 | 1 | 1 |
| INNER JOIN | In WHERE | 1 | 1 | 1 |
| LEFT JOIN | No additional filter | 3 | 2 | 3 |
| LEFT JOIN | In ON | 3 | 1 | 3 |
| LEFT JOIN | In WHERE | 1 | 1 | 1 |
| FULL JOIN | No additional filter | 4 | 3 | 3 |
| FULL JOIN | In ON | 5 | 3 | 3 |
| FULL JOIN | In WHERE | 1 | 1 | 1 |
PostgreSQL 18 documentation example · accessed 22 October 2026. Rows are not people or companies.
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.
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.
A right-table WHERE predicate can remove them because NULL does not satisfy ordinary equality. Choose filter placement according to the question.
Documentation consulted on .
No. First determine whether rows describe different establishments, dates or versions. Arbitrary removal can lose legitimate data.
A name may change or be shared. Fuzzy matching generates candidates to review, not demonstrated identity.
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.
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
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.
| Reference | Value or form | What it identifies |
|---|---|---|
| nL = 2 · nR = 2 | INNER = 4 | A 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. |
| FULL | INNER + unmatched L + unmatched R | A full join adds unmatched right rows. A key missing from one side does not create a Cartesian product with other keys. |
| NULL component | a = b AND c = d | With 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. |
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.
| Complete key or indicated group | nL | nR | INNER | LEFT | FULL |
|---|---|---|---|---|---|
| ["FR","001"] | 2 | 2 | 4 | 4 | 4 |
| ["FI","001"] | 1 | 0 | 0 | 1 | 1 |
| ["FR","003"] | 0 | 1 | 0 | 0 | 1 |
| Left rows with NULL · unmatched | 1 | 0 | 0 | 1 | 1 |
| Right rows with NULL · unmatched | 0 | 1 | 0 | 0 | 1 |
{
"left": [
[
"FR",
"001"
],
[
"FR",
"001"
],
[
"FI",
"001"
],
[
null,
"002"
]
],
"right": [
[
"FR",
"001"
],
[
"FR",
"001"
],
[
"FR",
"003"
],
[
null,
"002"
]
]
}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.
Documentation consulted on .
DOCUMENTED REFERENCE
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.
| Reference | Value or form | What it identifies |
|---|---|---|
| "B" IN ("A", NULL) | NULL | For 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) | FALSE | With x = "A" in the same list, one true equality suffices: IN is TRUE and NOT IN is FALSE despite the NULL. |
| empty subquery | IN = FALSE · NOT IN = TRUE | An 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 only | NOT 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 FROM | NULL can match NULL | Replacing = 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. |
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.
| Expression | Logical value | Effect when used in WHERE |
|---|---|---|
| x IN (SELECT k FROM r) | NULL | Row not retained |
| x NOT IN (SELECT k FROM r) | NULL | Row not retained |
| NOT EXISTS (SELECT 1 FROM r WHERE r.k = x) | TRUE | Row retained |
| NOT EXISTS (SELECT 1 FROM r WHERE r.k IS NOT DISTINCT FROM x) | TRUE | Row retained |
{
"candidate": "B",
"values": [
"A",
null
]
}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.
Documentation consulted on .
DEEPER READING / PRACTICE
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 .
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.
A joint can multiply lines and swell the totals. A loss of records can also change the scope without producing visible error.
The joining journal retains keys, expected cardinality, facts and unparent cases. The transformations of the identifiers are documented separately.