8 Displaying Data from Multiple Tables

What is a SQL join?

A SELECT that retrieves and merges rows from two or more tables based on related columns.

What is an inner join?

A join that returns a merged row only when matching values exist in all joined tables.

What is an outer join?

A join that returns matched rows and also includes nonmatching rows from one or both tables with NULLs for missing columns.

List the three outer join types.

LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN.

What is an equijoin?

A join that matches rows using equality of column values between tables.

What is a non-equijoin?

A join that relates rows using non-equality comparisons such as

What is a natural join?

A join that automatically matches tables by columns with identical names; join columns aren't explicitly listed.

What is a self-join?

A join where a table is joined to itself, usually matching one column to a different column in the same table.

Can joins include more than two tables?

Yes. Joins can include three or more tables; FROM appears once and additional tables are added with JOIN … ON.

Are PRIMARY KEY and FOREIGN KEY constraints required to perform a join?

No. They help integrity but are not required for performing joins.

Show the ANSI inner join skeleton.

SELECT … FROM t1 JOIN t2 ON t1.col = t2.col;

Is INNER optional in INNER JOIN?

Yes. JOIN without INNER defaults to INNER JOIN.

Is ON required in ANSI joins (when not using NATURAL or USING)?

Yes. ON is required to specify the join condition.

Can a WHERE clause be used with an inner join?

Yes. WHERE filters the joined result after the join condition.

What happens to rows with NULL foreign keys in an inner join?

They are excluded because no match exists in the joined table.

What is the older Oracle inner join syntax?

List both tables in FROM and place the equality condition in WHERE, e.g., FROM A, B WHERE A.id = B.id.

Is the older Oracle join syntax ANSI-compliant?

No. It works in Oracle but is not ANSI standard; ANSI JOIN … ON is preferred.

What is a table alias?

A temporary name assigned to a table in a SQL statement for brevity and disambiguation.

How long does a table alias last?

Only for the duration of the SQL statement where it is declared.

When are table aliases necessary?

When two or more joined tables have columns with the same name to avoid ambiguity.

Where can table aliases be used besides SELECT?

In INSERT, UPDATE, DELETE, and MERGE statements.

How do you define table aliases?

After each table name in FROM, add a space and the alias, e.g., FROM employees em JOIN addresses ad …

If two tables both have EMPLOYEE_ID, how do you reference it unambiguously?

Prefix with table or alias like em.employee_id or ad.employee_id.

What columns does NATURAL JOIN use?

All columns that have identical names in both tables.

Is NATURAL JOIN an inner or outer join by default?

Inner by default; can be LEFT/RIGHT/FULL if specified.

Are table prefixes allowed on join columns in NATURAL JOIN?

No. Prefixed references to the join columns cause a syntax error.

From which table is an unqualified common column sourced in NATURAL JOIN (inner or left)?

From the leftmost table.

From which table is an unqualified common column sourced in NATURAL RIGHT JOIN?

From the rightmost table.

How is a common column sourced in NATURAL FULL OUTER JOIN?

From the combination of both tables’ rows as returned in the full outer join.

What does the USING clause do in a join?

Specifies one or more identically named columns to join on without table prefixes.

Are table prefixes allowed with columns listed in USING?

No. You cannot prefix these columns anywhere in the statement.

How do join columns behave in the SELECT list with USING in an outer equijoin?

The output shows a value if present in either table’s join column; otherwise NULL.

Can USING join on multiple columns?

Yes. List multiple columns inside USING(…).

How do you extend a two-table join to a third table?

Append JOIN third_table alias ON … after the first join; FROM appears once.

What are practical considerations for many-table joins?

Performance and maintenance complexity increase; many practitioners keep joins around five tables, context dependent.

What is the structure of a three-table join?

FROM A a JOIN B b ON … JOIN C c ON … with each ON defining its join condition.

Give an example of a non-equijoin condition.

ON s.testscore BETWEEN g.scoremin AND g.score_max.

Do non-equijoins require PK/FK relationships?

No. They match based on comparison logic, not keys.

What operators are valid in non-equijoins?

, >=, <, <=, BETWEEN, and comparisons using expressions and Boolean logic.

How do you reference the same table twice in a self-join?

List the table twice in FROM with different aliases and join columns accordingly.

Why might a LEFT OUTER self-join be used with hierarchical data?

To include top-level rows with no parent (e.g., REPORTS_TO is NULL).

Is a self-referencing foreign key required for a self-join?

No. It helps integrity but is optional for the join.

What columns are typically joined in a self-join for reporting lines?

Child.REPORTS_TO joined to Parent.POSITION_ID.

Can a self-join be written as a non-equijoin?

Yes. Self-joins can be inner/outer and equi/non-equi as needed.

What does LEFT OUTER JOIN guarantee?

All rows from the left table appear; matching rows from the right table appear when present, else NULLs.

What does RIGHT OUTER JOIN guarantee?

All rows from the right table appear; matching rows from the left table appear when present, else NULLs.

What does FULL OUTER JOIN guarantee?

All rows from both tables appear, matched where possible and standalone otherwise with NULLs.

Is the OUTER keyword mandatory in outer joins?

No. OUTER is optional; LEFT JOIN equals LEFT OUTER JOIN, etc.

Where does JOIN appear in a multitable outer join?

After FROM on the first pair, then successive JOIN … ON clauses for additional tables.

What is the Oracle outer join plus operator?

A legacy syntax placing (+) on the side of the nonpreserved table in a WHERE equijoin predicate.

How do you express a left outer join with (+)?

Put (+) on the right-side table’s column in the WHERE equality, e.g., A.id = B.id(+).

How do you express a right outer join with (+)?

Put (+) on the left-side table’s column, e.g., A.id(+) = B.id.

Can you express FULL OUTER JOIN with (+)?

Not directly; you must UNION a left and a right outer join to simulate it.

Should you use the (+) operator today?

Oracle recommends avoiding it; use ANSI JOIN syntax. It is not tested on the exam.

What error occurs if you reference an ambiguous column without a prefix?

SQL raises an 'ambiguous column' error; you must prefix with a table or alias.

When does ambiguity commonly occur in joins?

When both tables have a column with the same name (e.g., EMPLOYEE_ID).

How do NATURAL and USING affect ambiguity handling?

They remove the need to prefix the join columns and disallow prefixes for those columns.

Where do GROUP BY and HAVING appear relative to JOIN and WHERE?

JOIN and WHERE form the row set first; GROUP BY and HAVING follow; ORDER BY is last if used.

Where does FROM appear in a JOIN query?

Once at the start of the table list; subsequent tables are added using JOIN.

In the SHIPS table, how is a home port recorded?

By a HOMEPORTID foreign key referencing PORTS.PORT_ID.

Why might 'Miami' not appear in an inner join of SHIPS and PORTS?

No ship has HOMEPORTID = PORT_ID of Miami; inner join returns only matches.

What is returned for join columns when a USING-based outer join includes an unmatched row?

NULL for the side with no value; otherwise the existing value appears.

Summarize the difference between EQUJOIN and NON-EQUIJOIN.

Equijoin uses equality on key columns; non-equijoin uses relational comparisons or ranges.

Summarize the difference between INNER and OUTER joins.

Inner returns only matched rows; outer returns matched rows plus nonmatching rows from the preserved side(s).

Can NATURAL JOIN be combined with outer joins?

Yes. NATURAL can be combined with LEFT, RIGHT, or FULL.

What does USING enable compared to NATURAL?

Explicitly names the common join columns instead of inferring all identically named columns.

Do joins require identical column names?

No for ON; Yes for NATURAL and USING (for the specified columns).

Self test: What does an inner join look for?

Values that match between the joined tables.

Self test: Which operator is characteristic of non-equijoins among options !=, <>, <=, none?

Self test: What do equijoins look for?

Exact data matches.

Self test: In 'SELECT … FROM A INNER JOIN B ON …', which keyword is optional?

INNER.

Self test: With JOIN … USING(VENDOR_ID), does the statement need ON?

No. USING replaces ON and the statement executes successfully.

Self test: JOIN … USING(VENDOR_ID) is what kind of join by default?

INNER equijoin.

Self test: What are two truths about table aliases?

They exist only for the statement and can remove ambiguity.

Self test: A query joining POSITIONS, EMPLOYEES, and PAY_HISTORY without aliases—valid?

Yes, it can execute successfully and connects three tables.

Self test: Is 'JOIN … ON p.salary BETWEEN pos.min_salary AND pos.max_salary' a non-equijoin?

Yes. It is a non-equijoin and also an inner join if INNER is implied.

Self test: How many tables can you join in one query?

One, two, three, or more; there is no fixed upper limit in SQL.

Self test: To list all customers and any purchases in last two weeks, which join?

An OUTER equijoin between CUSTOMERS and PURCHASES.

Self test: What characterizes a self-join?

Same table named twice in FROM and joined column-to-column within that table.

Self test: Is there a SELF JOIN keyword in Oracle?

No. You use standard JOIN syntax with aliases, not SELF JOIN.

Self test: Which outer join syntaxes are correct?

LEFT JOIN … ON … and RIGHT OUTER JOIN … ON … are both correct.

Self test: Key difference between INNER and OUTER joins?

INNER shows only matches; OUTER includes nonmatching rows as well.

Can NATURAL JOIN match multiple columns automatically?

Yes. It uses all identically named columns across both tables.

Risk of NATURAL JOIN in evolving schemas?

Adding a same-named column later can silently change join behavior; use USING/ON for control.

Does JOIN order (LEFT vs RIGHT) change which nonmatching rows are preserved?

Yes. LEFT preserves left table rows; RIGHT preserves right table rows.

Can WHERE filter rows after an outer join and accidentally remove preserved NULL-extended rows?

Yes. Conditions on the nonpreserved table must be written carefully (e.g., move to ON or use IS NULL checks).

Does JOIN … ON support expressions and functions in join conditions?

Yes. You can use expressions, functions, and boolean combinations in the ON clause.

What happens if join keys have multiple matches (1:N) on both sides?

The join returns the Cartesian set of matching pairs for those rows.

When joining many tables, what readability practice helps?

Use concise table aliases and align JOIN … ON blocks clearly.

Does FULL OUTER JOIN combine effects of LEFT and RIGHT?

Yes. It includes all unmatched rows from both sides plus matched rows.