Oracle Database / Basic: relational and transaction foundations / Lesson 4

Joins and relational grain

Combine customers, shipments and scans without multiplying facts or losing empty relationships.

About 4 min readContent reviewed 2026-10-28

On this page

Lesson goals

Predict joined row counts from key relationships.
Preserve outer-join intent and use existence tests.

Name what one row represents

A customer row represents one customer; a shipment row represents one shipment; a scan row represents one recorded event. Joining these tables changes the result grain. One shipment with three scans produces three joined rows, even though its charge remains one business fact. Summing that charge after the join triples revenue. Before writing SQL, draw the key relationship and count a tiny fixture by hand. An apparently plausible monetary total is weak evidence because duplicated charges can cancel other omissions in aggregate reports. Correct row meaning must survive every added join.

Use keys rather than descriptive coincidence

Join customers and shipments by customer_id, not customer_name. Two legitimate customers can share a display name, while one customer can change that name over time. Qualify shared column names with table aliases so ownership is visible. ANSI JOIN syntax keeps relationship conditions separate from report filters and is preferable to the legacy Oracle (+) outer-join operator. In the fixture below, two shipments belong to customer 7. The result is two rows because the customer key is unique; no ordering beyond the explicit shipment identifier is assumed.

sql Illustrative technical example; logical results are reasoned, not engine-tested.

WITH customers AS (
 SELECT 7 customer_id, 'Nile Lab' customer_name FROM dual
), shipments AS (
 SELECT 101 shipment_id, 7 customer_id FROM dual UNION ALL
 SELECT 102, 7 FROM dual
)
SELECT s.shipment_id, c.customer_name
FROM shipments s JOIN customers c ON c.customer_id = s.customer_id
ORDER BY s.shipment_id;

Expected output

SHIPMENT_ID | CUSTOMER_NAME
101         | Nile Lab
102         | Nile Lab

Each shipment matches the same unique parent exactly once. UNION ALL preserves both synthetic shipment rows.

Place optional-side filters carefully

A left join preserves every left-side row and supplies NULL for an unmatched right side. To include shipments with no accepted scans, put the accepted-status restriction inside ON. Putting sc.status = accepted in WHERE rejects the NULL-extended rows and effectively demands a match. That may be correct for a different question, but it changes this report. COUNT(sc.scan_id) counts actual child matches, whereas COUNT(*) also counts the preserved no-child row. Select the expression from the intended business unit rather than a habit learned from a simpler query.

sql Illustrative technical example; logical results are reasoned, not engine-tested.

SELECT s.shipment_id, COUNT(sc.scan_id) AS accepted_scans
FROM shipments s
LEFT JOIN scans sc
  ON sc.shipment_id = s.shipment_id AND sc.status = 'accepted'
GROUP BY s.shipment_id
ORDER BY s.shipment_id;

Every shipment appears once after grouping. A shipment without an accepted scan has accepted_scans = 0.

Ask existence without materializing every child

When the output needs one row per shipment with at least one exception scan, EXISTS states that intent directly. A join followed by DISTINCT can hide accidental multiplication and may select the wrong business meaning once child columns are added. NOT EXISTS expresses the complementary no-match condition without the nullable-set trap of NOT IN. These forms do not dictate one fixed physical algorithm: the optimizer can transform them. Evaluate them first for semantic clarity, then inspect authorized plan evidence if performance matters. Do not infer engine work solely from SQL text length.

Aggregate independent child collections first

Suppose a shipment has three scans and two invoice lines. Joining both raw child tables produces six combinations. Aggregating scans to shipment_id and invoice lines to shipment_id before joining prevents this cross-multiplication. Each intermediate result must have a documented uniqueness guarantee at its joining key. A common table expression makes that review easier but does not guarantee materialization or eliminate all work. Keep a reconciliation fixture with one parent having multiple rows in both children, one having none and one having only one collection; this exposes defects that all-one-row examples cannot reveal.

Common errors and fixes

ORA-00918

Why it happens: A shared column name is referenced without a qualifying alias.
How to fix it: Qualify the intended owner in projection, conditions and ordering.
Prevent it next time: Use short consistent aliases when more than one table participates.

Shipments without scans disappear

Why it happens: A right-side filter in WHERE rejects outer-join NULL rows.
How to fix it: Move the qualifying child condition into ON when preservation is intended.
Prevent it next time: Test a parent with zero matching children in every outer-join report.

Revenue grows when scan columns are added

Why it happens: A one-to-many join duplicated shipment-level charges.
How to fix it: Aggregate each child to the required grain before combining monetary facts.
Prevent it next time: Write one-row meaning beside each reporting stage.

Check your understanding

1. A shipment has four scans and three invoice lines. How many raw joined combinations exist?

Show worked solution

Twelve: each scan combines with each invoice line. A shipment-level charge appears twelve times. Aggregate each child collection independently to one row per shipment, verify its key uniqueness, and only then join those summaries to the parent. DISTINCT on the final monetary total is not a principled repair.

2. Should a report listing customers with no shipments use an inner join?