SQL JOIN combines rows from related database tables by evaluating a condition between their columns. Data-center inventories, manufacturing applications, business records and monitoring databases often separate equipment information from racks, facilities and other reference data. A foreign key can represent the relationship without repeating the entire rack description on every equipment record. OSSQL.006 explains how to retrieve matching data, preserve unmatched records, and avoid accidental row loss.

The lesson follows OSSQL.005 on LIMIT and OFFSET. That lesson used one fictional equipment table; joining expands a query beyond a single table. Review OSSQL.001: Relational Databases if primary keys and table relationships are unfamiliar. The new laboratory uses SQLite 3 with entirely invented records. The example training model labels and nominal terahashes-per-second values are not manufacturer specifications, installed hardware, or observed production measurements.

Learning objectives and prerequisites

• Describe primary keys, foreign keys and one-to-many relationships between tables.
• Use explicit INNER JOIN ... ON to return matching records.
• Use LEFT JOIN ... ON to retain every left-side record and display NULL when no right-side match exists.
• Explain table aliases, qualified column names, join multiplicity and why output order still requires ORDER BY.
• Identify unassigned equipment and empty racks with LEFT JOIN and IS NULL.
• Distinguish conditions inside ON from row filtering inside WHERE.
• Count related records correctly with COUNT(child.id), enable SQLite foreign-key enforcement, and run parameterized filters.
• Execute a complete SQLite training database and reproduce 24 automated assertions.

Prerequisites: SELECT, FROM, WHERE, comparison operators, basic schema concepts, and the ability to run Python 3. The SQL files are compatible with SQLite 3. The verified local test environment used Python 3.13.5 and SQLite 3.46.1. No external Python packages, credentials, network access, or administrator privileges are required.

Why databases separate equipment from rack descriptions

A rack inventory can assign one numeric rack_id to each rack, while a miner record refers to that identifier. Storing a rack name and location only once reduces repeated text and supports consistent updates. A primary key identifies each rack row uniquely. A foreign key connects a device to an existing rack, or can remain NULL when the schema permits an unassigned device. The latter is a valid relationship state, not necessarily a corrupt record.

CREATE TABLE racks (
  rack_id INTEGER PRIMARY KEY,
  rack_name TEXT NOT NULL UNIQUE,
  location TEXT NOT NULL
);
CREATE TABLE miners (
  miner_id INTEGER PRIMARY KEY,
  model TEXT NOT NULL,
  rack_id INTEGER REFERENCES racks(rack_id),
  rated_hashrate_th INTEGER NOT NULL
);

The full laboratory file includes a positive-value CHECK constraint and an optional index on the child-side rack identifier. SQLite foreign keys must be explicitly enabled on each database connection unless configured otherwise; execute PRAGMA foreign_keys = ON; outside any open transaction and verify its value. A foreign-key declaration alone should not be assumed to enforce every reference. Consult the official SQLite foreign-key documentation.

The source provides four racks and seven miners. Rack A has two miners, Rack B has one, Rack C has two, and Rack D has none. Two miners deliberately have rack_id = NULL. The relationship is one rack to potentially many miners, so a rack’s name may legitimately appear in more than one joined output row. The lab’s data is synthetic, and the shown numerical rates are not measured.

INNER JOIN returns only matches

INNER JOIN, often written simply as JOIN, returns matching pairs of rows satisfying an ON expression. With the training data, five miners have a rack assignment. The two unassigned miners do not match any rack because an equality comparison involving SQL NULL is not true. Column qualification makes the query readable when identifiers have similar names in multiple tables.

SELECT m.miner_id, m.model, r.rack_name
FROM miners AS m
INNER JOIN racks AS r
  ON m.rack_id = r.rack_id
ORDER BY m.miner_id;

Observed test result: five rows, for miner IDs 1001, 1002, 1003, 1005 and 1006. Miner 1004 and 1007 do not appear. m and r are local table aliases; they do not rename the actual tables. The official PostgreSQL joins tutorial shows the same relational behavior with a weather-and-cities example. OSSQL.002 on SELECT and FROM provides more background on choosing result columns.

Alex The Analyst, “Intermediate SQL Tutorial | Inner/Outer Joins | Use Cases”, March 19, 2020. An independently identified instructional video covering combined-table queries and join types. Examples may use a different SQL engine. End-to-end playback in the published article has not been independently verified.

LEFT JOIN preserves the left-hand side

LEFT JOIN retains every left-side row at least once, even when no matching right-side record exists. Where no rack matches a miner, its rack columns are presented as NULL. The original miner fields remain present. A LEFT JOIN can still return more rows than its left table if a left record matches multiple right records; preserving left rows is not a promise of exactly one output row per record.

SELECT m.miner_id, m.model, r.rack_name
FROM miners AS m
LEFT JOIN racks AS r
  ON m.rack_id = r.rack_id
ORDER BY m.miner_id;

Observed test result: all seven miner records appear. Rows 1004 and 1007 have a NULL rack name. SQL NULL does not mean an empty string, zero, or a literal text value reading “NULL”; it means the queried value is missing or unknown. The SQLite SELECT reference explains when outer-join NULL values are added.

Finding unassigned equipment

A left anti-join pattern identifies left-side records that have no match on the right. It uses a LEFT JOIN followed by a check for a right-side non-nullable key. Since r.rack_id is the primary key, a null result for it demonstrates the missing match. Be careful when checking an optional right-side column instead: a matched row might contain a NULL in a nullable field.

SELECT m.miner_id, m.model
FROM miners AS m
LEFT JOIN racks AS r
  ON m.rack_id = r.rack_id
WHERE r.rack_id IS NULL
ORDER BY m.miner_id;

Observed test result: miner IDs 1004 and 1007. To find unused racks, reverse the roles: begin with racks AS r LEFT JOIN miners AS m, then filter on m.miner_id IS NULL. That returns Rack D in the sample. A real inventory query would need to define how it treats retired equipment, scheduled assignments, missing telemetry and inventory cutoffs, rather than inferring operational outages from a NULL relationship.

ON and WHERE are not interchangeable in an outer join

For an INNER JOIN, a matching filter in ON is often logically equivalent to applying it later in WHERE. A LEFT JOIN behaves differently. ON determines which right-side records are eligible to match; WHERE filters the joined results, including any null-padded rows. Placing a condition for the right table in WHERE can accidentally remove the very unmatched rows that a LEFT JOIN was intended to retain.

-- Seven left-side miners remain; only Hall 1 racks match.
SELECT m.miner_id, r.rack_name
FROM miners AS m
LEFT JOIN racks AS r
  ON m.rack_id = r.rack_id AND r.location = 'Hall 1'
ORDER BY m.miner_id;

-- Only three matching miners remain.
SELECT m.miner_id, r.rack_name
FROM miners AS m
LEFT JOIN racks AS r ON m.rack_id = r.rack_id
WHERE r.location = 'Hall 1'
ORDER BY m.miner_id;

Observed test results: the first query returns seven miners, with NULL rack fields for Hall 2 miners and unassigned miners. The second returns only IDs 1001, 1002 and 1003, three rows. SQLite’s documentation explicitly distinguishes these processing stages. The WHERE conditions lesson provides the foundation for reasoning about the filter.

Chandoo, “SQL Joins — Beginner to PRO Masterclass with 10 Examples”, March 12, 2024. At approximately 6:28, the lesson compares filtering in JOIN versus WHERE; at approximately 12:51 it introduces outer joins and at 17:29 an anti-join. Independently identified video; successful playback in the published page remains unverified.

Counting equipment by rack

Joining from the racks table toward the miners table makes it possible to report an empty rack. Group by the rack’s identity and count only the matched miner IDs. COUNT(m.miner_id) ignores NULL values; COUNT(*) counts the null-padded outer-join row as an output row. An empty Rack D therefore produces zero with the first count but one with the second.

SELECT r.rack_name,
       COUNT(m.miner_id) AS miner_count
FROM racks AS r
LEFT JOIN miners AS m
  ON m.rack_id = r.rack_id
GROUP BY r.rack_id, r.rack_name
ORDER BY r.rack_id;

Observed test result: Rack A = 2, Rack B = 1, Rack C = 2, Rack D = 0. Grouping also illustrates why a join does not automatically deduplicate parent records. A rack assigned to two miners participates in two matching pairs. Use COUNT(DISTINCT ...) only when the distinct business entity being counted is defined clearly and the additional operation is appropriate.

Reproducible SQL and automated tests

Download the complete standalone join_lab.sql and verify_joins.py into the same local directory. The SQL file creates the training tables, inserts seven fake miner records and four fake racks, creates an optional join-key index, and runs six example queries. The Python verification program uses only built-in sqlite3 and unittest, constructs an in-memory SQLite database for each test, and checks the exact results.

python3 --version
python3 verify_joins.py

# If the SQLite command-line tool is installed:
sqlite3 :memory: < join_lab.sql

The test program was executed locally on Python 3.13.5 / SQLite 3.46.1. Recorded result: Ran 24 tests — OK. The checks include foreign-key enforcement, invalid reference rejection, five INNER JOIN matches, seven LEFT JOIN results, two unassigned miners, one empty rack, ON-versus-WHERE row counts, ordering, optional child indexing assumptions, and parameterized filtering. The lab has no external service or application credential dependency.

The SQL file contains DROP TABLE IF EXISTS commands to reset its own named training tables. Never run it against a production database or an existing database that contains valuable tables named miners or racks. Running through sqlite3 :memory: creates a disposable memory database; the Python harness also uses in-memory databases and is safer for repeated exercises. No claim of performance benchmarking or live hardware validation is made.

Parameterization, indexes, and realistic limits

When an application searches by rack location, pass the location as a bound parameter rather than concatenating untrusted input into SQL. The harness uses WHERE r.location = ? with a bound value of 'Hall 2', returning miner IDs 1005 and 1006. Placeholders differ across drivers, so use the binding syntax provided by the actual database API.

import sqlite3
connection = sqlite3.connect(":memory:")
connection.execute("PRAGMA foreign_keys = ON")
# After creating the lab schema:
hall = "Hall 2"
rows = connection.execute(
    """SELECT m.miner_id
       FROM miners AS m
       JOIN racks AS r ON m.rack_id = r.rack_id
       WHERE r.location = ?
       ORDER BY m.miner_id""",
    (hall,)
).fetchall()
# Expected: [(1005,), (1006,)]

Indexes on join keys can improve selected workloads, but their benefit depends on table sizes, cardinality, query plans, statistics and the chosen optimizer. Use EXPLAIN QUERY PLAN for SQLite and equivalent tools for other systems before claiming a measured speed improvement. Join type alone does not determine performance. The PostgreSQL 18 table-expression reference provides a broader formal explanation of inner and outer joins.

Troubleshooting and assessment

• Missing rows after joining: determine whether an INNER JOIN intentionally excludes unmatched records, whether a LEFT JOIN has a right-table predicate in WHERE, and whether the key values really match.
• Unexpected row multiplication: check whether the relationship is one-to-many or many-to-many; do not assume the joined result has the same row count as either source table.
• NULL surprises: use IS NULL and IS NOT NULL; = NULL does not test missing values correctly.
• Ambiguous column: use qualified names like m.rack_id and r.rack_id rather than relying on an unqualified shared name.
• Foreign key not enforcing: inspect PRAGMA foreign_keys on the same SQLite connection, outside an open transaction.
• Incorrect counts: distinguish counting output rows with COUNT(*) from counting non-null matched IDs with COUNT(m.miner_id).
• Unstable report order: add an explicit, sufficiently deterministic ORDER BY; a join does not guarantee natural row order.
• Untrusted inputs: use parameter binding and permissions appropriate to the database; do not assemble arbitrary SQL from a request string.

Practice problems: replace the INNER JOIN with LEFT JOIN and predict the additional two records. Reverse the LEFT JOIN to identify Rack D. Rewrite the location filter first inside ON and then inside WHERE, explaining the seven-versus-three row difference. Add one additional synthetic miner to Rack B and identify which row counts change. Demonstrate the effect of COUNT(*) for an empty rack. Finally, attempt to insert an invalid rack reference and explain why foreign-key enforcement prevents it.

Answers: LEFT JOIN retains miner IDs 1004 and 1007 with missing rack values. Reversing the join reveals Rack D as unused. Restricting Hall 1 matches within ON preserves all seven left-side miners, while WHERE eliminates Hall 2 and unmatched rows, leaving three. A new Rack B assignment increases its count by one and increases the total assigned-miner count by one. COUNT(*) sees one null-padded Rack D row, while COUNT(m.miner_id) sees zero matched miner IDs. A foreign key to rack 999 is rejected when enforcement is enabled and that parent is absent.

Official documentation and continuation

Primary references: SQLite SELECT and JOIN semantics; SQLite Foreign Keys; PostgreSQL 18 Joins Tutorial; PostgreSQL 18 Table Expressions. Related Open CERT lessons: OSSQL.004 ORDER BY and OSHTML.005 on presenting accessible tables.

A reliable join report begins with the correct relationship, an explicit condition, a deliberate choice between matching-only and left-preserving rows, and an understanding of NULL. SQL joins are central to infrastructure, finance, manufacturing and application reporting; their accuracy depends as much on defining the question as on writing syntactically valid statements.

Editor’s Note: OSSQL.006 follows the published OSSQL.005 in the Open CERT / TechCert rotation. The 1200 × 630 original cover is AI-generated educational illustration, not a photograph of a production database or an authentic screenshot. The exact small “Bitcoinversus.Tech” branding is neon green (#39FF14), at the lower-left, nominally 10 pt at 96 dpi. All rack and miner records are fabricated training inputs, not equipment specifications or operational measurements. The executable SQL examples passed 24 locally executed Python/SQLite checks; no production deployment or query performance benchmark occurred. Both educational video identities were verified from their public YouTube listings, but end-to-end playback and mobile pixel-level inspection of the published post remain independently unverified.

For mobile scanning, display the QR on a second screen or use a compatible wallet’s saved-image scanner. Confirm the destination and 5,000-satoshi amount before sending funds.

We volunteer daily to ensure the information on this platform is Verifiably True. If you would like to support the research, please scan the QR code.

BitcoinVersus.Tech teaches technical concepts for informational purposes; examples are not production operating procedures or security guarantees.