INNER JOIN and relationship cardinality

sqlrelational-datasqlitepostgresqlindependent-practice

Saved on this device

Full lesson · 4 learning steps Open any exercise for focused practice

Step 1 of 4 · intro card

INNER JOIN and relationship cardinality

Concept and contract

Join identities through declared relationships and predict how many rows each match creates.

A join forms output rows from matching combinations. The worked query connects each role to its company through company_id and the company primary key. The foreign-key relationship gives each role one company, so the output retains one row per role record.

Predict eight rows, not four companies. Company A appears three times because it owns three roles. This repetition is expected at the chosen output grain. It is not evidence of duplicated source data or a reason to add DISTINCT without understanding the relationship.

Mechanism and worked example

INNER JOIN keeps matching pairs and ON states the relationship.

INNER JOIN keeps matching pairs and ON states the relationship. Table aliases r and c clarify which identity or label an expression comes from. Matching a role's company_id to c.id connects the intended entities rather than comparing unrelated role and company labels.

The company primary key prevents multiple company matches for one role key. A different joined table, such as role_skills, is one-to-many and would multiply rows. Read constraints and data shape before predicting a join's output cardinality.

Complete worked example

sql

SELECT r.id, c.name FROM roles r
JOIN companies c ON c.id = r.company_id ORDER BY r.id;

Reasoning: Each role connects to exactly one company identity.

Counterexamples and common mistakes

The counterexample cross-joins roles and companies without a relationship.

The counterexample cross-joins roles and companies without a relationship. It produces thirty-two combinations. A later WHERE condition might reduce this, but an accidental Cartesian product is not repaired by selecting fewer columns or limiting the first page.

A subtle wrong join can return the expected number of rows while connecting the wrong entities. Check the identity-to-company mapping, not just the count. Avoid joining by human-readable names unless the data contract truly makes them stable unique keys.

Counterexample: a plausible but wrong query

sql

SELECT r.id, c.name FROM roles r CROSS JOIN companies c
ORDER BY r.id, c.id;

Reasoning: The Cartesian product creates four company combinations for each role.

Independent practice: small query

Return identities and company names for active roles in Ukraine.

Return identities and company names for active roles in Ukraine. Predict two rows, both Atlas Cloud, with identities 1 and 2. Keep role status and vacancy country as filters on roles; the company table does not supply the vacancy location in this fixture.

Start with the same key relationship as the worked query and add the two role predicates. Do not infer vacancy country from headquarters. This small exercise tests that the relationship and the population remain separate decisions.

Step 1: Write the company-key relationship. Step 2: Filter role status and vacancy country.

Practice starter: solve before reading the solution

sql

SELECT r.id, c.name FROM roles r
JOIN companies c ON /* relationship */
WHERE /* active Ukrainian roles */ ORDER BY r.id;

Reasoning: Keep the relationship in ON and the role population in WHERE.

Hint 1: Use c.id = r.company_id.
Hint 2: Role 7 is a useful negative case because it is archived.

Solution, tests and trade-offs

The solution returns the two active Ukrainian role identities with Atlas Cloud.

The solution returns the two active Ukrainian role identities with Atlas Cloud. Archived role 7 belongs to the same company and country but is excluded explicitly. A result containing only one Atlas Cloud label would have lost the requested role grain.

Acceptance checks compare both selected columns. If a join is later extended with skill evidence, revisit the grain and predict multiplicity again. A correct two-table relationship does not guarantee that an additional one-to-many table preserves the same output cardinality.

Practice solution and acceptance checks

sql

SELECT r.id, c.name FROM roles r
JOIN companies c ON c.id = r.company_id
WHERE r.active = 1 AND r.country = 'UA' ORDER BY r.id;

Reasoning: Identities 1 and 2 both map to Atlas Cloud.

Interview reasoning and next step

Interview explanations should name the join keys, expected relationship and output grain.

Interview explanations should name the join keys, expected relationship and output grain. Discuss missing matches and integrity assumptions separately. An INNER JOIN can remove an orphan in unconstrained external data; it does not by itself validate the source's claimed relationship.

This fixture declares foreign keys to make the intended relationship concrete. The next lesson keeps unmatched left-side entities, including the company with no roles. That shift changes both missing-value behavior and the placement of population filters.

Optional stretch — after the core task works: Join skills and explain exactly which role identities become repeated.

Computer practice: https://applydjinn.com/typorithm/sql-lab-20261105.zip. Load the shared setup.sql fixture before running a snippet. The local verifier compares exact ordered row arrays in SQLite and PostgreSQL, keeps SQL values separate from driver bindings and never connects to the production website database.

Join identities through declared relationships and predict how many rows each match creates.