Ravindra BagaleCourses & study guides Track your progress

14. Why Tables Join: Keys and Relationships

14.3 How to Draw a Tiny Relationship Map Before You Write SQL

By Last updated:

Why. A two-minute sketch of how tables connect saves you from writing a join on the wrong column. It also tells you which side may have repeats.

What. A relationship map is a small drawing with one box per table and a line from each foreign key to its primary key. A key is unique when COUNT(*) equals COUNT(DISTINCT key) and the key has no NULLs. An orphan is a row whose foreign key value has no match, for example a shipment pointing to a hub that does not exist. A missing key is different: it is NULL, which means the current value is unknown or not available.

How. Check uniqueness in hubs and hub_contacts, then find shipments whose hub does not exist or is missing.

Steps

  1. Draw boxes for customers, shipments, hubs and hub_contacts, with a line for each key.
  2. Run the first example: if rows equals distinct_ids, the key is unique.
  3. Run the second example and mark hub_contacts as having repeats on hub_id.
  4. Run the third example to list orphans and missing keys, then run the fourth example, which does the same check with NOT EXISTS.

Examples

Example 1 · dataset: swiftship_logistics

SELECT COUNT(*) AS rows,
       COUNT(DISTINCT hub_id) AS distinct_ids
FROM hubs;

Output

rowsdistinct_ids
55

Meaning. 5 rows and 5 distinct ids, so hub_id is unique in hubs.

Example 2 · dataset: swiftship_logistics

SELECT COUNT(*) AS rows,
       COUNT(DISTINCT hub_id) AS distinct_ids
FROM hub_contacts;

Output

rowsdistinct_ids
54

Meaning. 5 rows but only 4 distinct ids, so one hub has more than one contact. Joining to this table can repeat rows.

Example 3 · dataset: swiftship_logistics

SELECT shipment_id, hub_id
FROM shipments
WHERE hub_id IS NULL
   OR hub_id NOT IN (SELECT hub_id FROM hubs);

Output

shipment_idhub_id
10099
1010NULL

Meaning. 1009 is an orphan: hub 9 does not exist. 1010 has a missing key: its hub is unknown right now. Both need checking, but for different reasons. This NOT IN check is safe here only because hubs.hub_id is the INTEGER PRIMARY KEY, so it can never be NULL.

Example 4 · dataset: swiftship_logistics

SELECT s.shipment_id, s.hub_id
FROM shipments AS s
WHERE s.hub_id IS NULL
   OR NOT EXISTS (SELECT 1 FROM hubs AS h
                  WHERE h.hub_id = s.hub_id);

Output

shipment_idhub_id
10099
1010NULL

Meaning. NOT EXISTS gives the same two shipments. It stays correct even if the other table had a NULL key, so it is the safer habit when you are not sure the key is NOT NULL.

Ravindra Bagale's Tip

Keep the IS NULL check in the orphan query, because NOT IN never returns a row whose own value is NULL. Also, if the subquery itself returns a NULL, NOT IN returns no rows at all, so use NOT EXISTS when the other key might be NULL.