INNER/OUTER/CROSS Joins, Legacy Join Syntax Awareness, and Cardinality Reasoning

Learning outcomes

ServiceHub now needs a report listing every technician, including technicians with no open tickets, plus an optional count of ticket tags. A developer writes a join that looks compact but silently removes technicians without tickets; another joins tags and mistakes the multiplied row count for duplicate data. The lesson’s central rule is simple: define the required row set before discussing join algorithms or indexes.

1. A join is first a rule for constructing rows

An inner join returns only row combinations that satisfy its join condition. A left outer join preserves every row from the left input and null-extends right-side columns when no match exists. A full outer join preserves unmatched rows from both sides. A cross join deliberately forms the Cartesian product: every row from one input paired with every row from the other.

These are result semantics. Oracle may implement an eligible join with nested loops, hash join, or sort merge join, but a different physical method must preserve the same SQL result. Therefore, validate the business row set before diagnosing plan shape.

SELECT    t.tech_id,    t.tech_name,    w.work_order_id,    w.status_codeFROM servicehub_sql_technician tLEFT JOIN servicehub_sql_work_order w  ON w.tech_id = t.tech_idORDER BY t.tech_id, w.work_order_id;

2. ON and WHERE are not interchangeable for outer joins

With an inner join, moving a simple filter between ON and WHERE often preserves the final row set. With an outer join, it can change the meaning. The ON clause decides whether right-side rows match while the left row is still preserved; a WHERE predicate is applied to the joined result and can reject the null-extended row.

SELECT t.tech_id, t.tech_name, w.work_order_idFROM servicehub_sql_technician tLEFT JOIN servicehub_sql_work_order w  ON w.tech_id = t.tech_idWHERE w.status_code = 'OPEN'ORDER BY t.tech_id;

For a technician with no matching ticket, w.status_code is null. The WHERE predicate is not true, so that preserved row is removed. If the requirement is “all technicians, and their open tickets if any,” move the status predicate into the join condition.

SELECT t.tech_id, t.tech_name, w.work_order_idFROM servicehub_sql_technician tLEFT JOIN servicehub_sql_work_order w  ON w.tech_id = t.tech_id AND w.status_code = 'OPEN'ORDER BY t.tech_id, w.work_order_id;

3. Cardinality multiplication is not automatically duplication

If one technician owns three work orders, joining technician to work order correctly returns three technician/work-order combinations. If each work order has two tags and you join tags too, you can get six rows. That is not necessarily corrupt data; it is the relational product implied by the relationships.

Before adding DISTINCT, ask what one output row is supposed to represent. Using DISTINCT to hide a join mistake can suppress legitimate combinations and consume extra work for sorting or hashing.

SELECT COUNT(*) AS techniciansFROM servicehub_sql_technician;SELECT tech_id, COUNT(*) AS work_ordersFROM servicehub_sql_work_orderGROUP BY tech_idORDER BY tech_id;SELECT work_order_id, COUNT(*) AS tagsFROM servicehub_sql_work_order_tagGROUP BY work_order_idORDER BY work_order_id;SELECT COUNT(*) AS joined_rowsFROM servicehub_sql_technician tJOIN servicehub_sql_work_order w  ON w.tech_id = t.tech_idJOIN servicehub_sql_work_order_tag x  ON x.work_order_id = w.work_order_id;

The last count is evidence about the composed relationship. It is not proof that the optimizer estimated the same cardinality, and it is not by itself evidence of duplicate base rows.

4. Accidental Cartesian products fail by being valid

A missing join predicate is dangerous because the SQL can be syntactically valid. Three technicians and four work orders produce twelve combinations. Large production tables can turn the same mistake into an explosive intermediate result.

SELECT COUNT(*) AS accidental_pairsFROM servicehub_sql_technician t,     servicehub_sql_work_order w;

Prefer explicit ANSI JOIN ... ON syntax for new code because the relationship is visible next to the join. Use CROSS JOIN when the Cartesian product is intentional; the keyword communicates intent during review.

5. Legacy Oracle (+) syntax: recognize it, do not normalize it

Older Oracle code often expresses an outer join by applying the (+) operator to columns from the optional side in the WHERE clause. Current Oracle still documents this syntax for compatibility, but it has restrictions that ANSI outer joins do not. For example, you cannot mix (+) and ANSI join syntax in the same query block, and if multiple predicates join the same two tables then (+) must be applied consistently or the query can silently become a simple join.

SELECT    t.tech_id,    t.tech_name,    w.work_order_idFROM servicehub_sql_technician t,     servicehub_sql_work_order wWHERE t.tech_id = w.tech_id(+)ORDER BY t.tech_id, w.work_order_id;

6. Plan evidence comes after semantic evidence

Once row semantics are correct, inspect the optimizer’s estimated plan. EXPLAIN PLAN is not an executed cursor and its row estimates are not actual row counts. It is useful here to identify the join tree and estimated cardinalities, but later optimizer lessons will use executed-cursor evidence where appropriate.

EXPLAIN PLAN SET STATEMENT_ID = 'SH05L2'FORSELECT t.tech_id, t.tech_name, w.work_order_idFROM servicehub_sql_technician tLEFT JOIN servicehub_sql_work_order w  ON w.tech_id = t.tech_id AND w.status_code = 'OPEN';SELECT *FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'SH05L2', 'BASIC +PREDICATE'));

Do not hard-code a predicted join method into an acceptance test. The optimizer can choose a different legal plan as statistics, data volume, or system conditions change.

7. Hands-on lab: preserve technicians and expose multiplication

DROP TABLE servicehub_sql_work_order_tag IF EXISTS PURGE;DROP TABLE servicehub_sql_work_order IF EXISTS PURGE;DROP TABLE servicehub_sql_technician IF EXISTS PURGE;CREATE TABLE servicehub_sql_technician (    tech_id    NUMBER PRIMARY KEY,    tech_name  VARCHAR2(80) NOT NULL);CREATE TABLE servicehub_sql_work_order (    work_order_id NUMBER PRIMARY KEY,    tech_id       NUMBER,    status_code   VARCHAR2(20) NOT NULL,    CONSTRAINT sh05_wo_tech_fk      FOREIGN KEY (tech_id) REFERENCES servicehub_sql_technician(tech_id));CREATE TABLE servicehub_sql_work_order_tag (    work_order_id NUMBER NOT NULL,    tag_code      VARCHAR2(30) NOT NULL,    CONSTRAINT sh05_wot_pk PRIMARY KEY (work_order_id, tag_code),    CONSTRAINT sh05_wot_wo_fk      FOREIGN KEY (work_order_id) REFERENCES servicehub_sql_work_order(work_order_id));INSERT INTO servicehub_sql_technician VALUES (1,'Asha');INSERT INTO servicehub_sql_technician VALUES (2,'Mina');INSERT INTO servicehub_sql_technician VALUES (3,'Ravi');INSERT INTO servicehub_sql_work_order VALUES (101,1,'OPEN');INSERT INTO servicehub_sql_work_order VALUES (102,1,'CLOSED');INSERT INTO servicehub_sql_work_order VALUES (103,2,'CLOSED');INSERT INTO servicehub_sql_work_order_tag VALUES (101,'URGENT');INSERT INTO servicehub_sql_work_order_tag VALUES (101,'HVAC');INSERT INTO servicehub_sql_work_order_tag VALUES (102,'HISTORY');COMMIT;

-- Requirement: every technician, plus OPEN work orders when present.SELECT t.tech_id, t.tech_name, w.work_order_idFROM servicehub_sql_technician tLEFT JOIN servicehub_sql_work_order w  ON w.tech_id = t.tech_id AND w.status_code = 'OPEN'ORDER BY t.tech_id, w.work_order_id;-- Observe relationship multiplication deliberately.SELECT t.tech_id, w.work_order_id, x.tag_codeFROM servicehub_sql_technician tJOIN servicehub_sql_work_order w  ON w.tech_id = t.tech_idLEFT JOIN servicehub_sql_work_order_tag x  ON x.work_order_id = w.work_order_idORDER BY t.tech_id, w.work_order_id, x.tag_code;

DROP TABLE servicehub_sql_work_order_tag PURGE;DROP TABLE servicehub_sql_work_order PURGE;DROP TABLE servicehub_sql_technician PURGE;

8. Production judgment and next step

For production joins, document the intended grain: one row per technician, work order, tag, or some aggregate. Enforce key relationships where possible, inspect base-table multiplicity, and test outer-join filters with unmatched rows. Only then use plan/cardinality evidence to improve performance. A query that is fast and semantically wrong is still wrong.

No paid feature, management pack, restart, or COMPATIBLE change is required. The legacy (+) operator is included only for maintenance literacy. Lesson 3 moves to subqueries, where Oracle may transform EXISTS/IN into semijoins and NOT EXISTS/NOT IN into antijoins—but NULL semantics must be correct before any transformation is considered.

Authoritative references

• Joins — join concepts, semijoin/antijoin context, and optimizer behavior
• SQL Language Reference — Joins — ANSI and legacy (+) outer-join rules
• SELECT — join and query syntax
• DBMS_XPLAN — displaying plan-table and cursor plans