Learning Objectives – after this chapter you will be able to:
- Join two or more tables with an equi-join and qualify columns with table names or aliases.
- Recognise a Cartesian product and avoid it.
- Combine join conditions with normal conditions and ORDER BY.
- Write self-joins and outer joins using the (+) operator.
- Use the set operators UNION, UNION ALL, INTERSECT and MINUS and their rules.
Joining Tables
In a relational database the data is spread over several tables (storing everything in one table would cause redundancy). Joining combines two or more tables so that values from the columns of all of them can be retrieved. Tables can be joined only through a common column – e.g. CCODE for COURSES and BATCHES.
Equi-join of BATCHES and COURSES
select bcode, b.ccode, name, fcode, stdate from batches b, courses c -- tables separated by commas; b and c are aliases where b.ccode = c.ccode; -- join condition (equi-join)
Output
BCODE CCODE NAME FCODE STDATE ----- ----- ---------------- ----- --------- b1 ora Oracle database gk 12-JAN-01 b2 asp ASP.NET da 15-JAN-01 b3 c C programming hs 20-JAN-01 ...
- A join that uses the equal-to operator is an equi-join.
- A column present in more than one of the tables (CCODE) must be qualified with the table name or alias.
- A table alias is a short name for a table, valid only in the query where it is defined (it is not stored anywhere).
Cartesian Product, Join and Normal Conditions
- Without a join condition the result is the product (Cartesian product): every row of the first table joined with every row of the second – 6 courses × 7 batches = 42 rows. It is usually the result of an error.
- The condition that joins the tables is the join condition; other conditions in WHERE are normal conditions used to filter rows. ORDER BY can sort the joined result.
- Minimum number of join conditions = number of tables − 1 (more if several columns form the join).
Joining three tables with a filter and ORDER BY
select bcode, c.name course, f.name faculty, stdate from batches b, courses c, faculty f where b.ccode = c.ccode -- join condition 1 and b.fcode = f.fcode -- join condition 2 (3 tables -> 2 conditions) and enddate is not null -- normal condition order by stdate;
Self-Join
A self-join joins a table with itself: two copies of the table (two aliases) are joined as if they were different tables.
Batches that started after batch b3
select b2.* from batches b1, batches b2 where b1.bcode = 'b3' -- anchor the first copy at batch b3 and b2.stdate > b1.stdate; -- batches that started after b3
Outer Join
A normal join returns only rows that have matching values in both tables. If a new course cs (C Sharp) has no batch, it is missing from the join. An outer join also returns parent rows that have no child rows by extending the other table with null rows.
Outer join – course cs is shown though it has no batch
select c.ccode, name, fee, bcode, stdate, enddate from courses c, batches b where c.ccode = b.ccode (+) -- (+) on the side that is extended with nulls order by c.ccode;
Output
CCODE NAME FEE BCODE STDATE ENDDATE ----- ---------------- ---- ----- --------- --------- asp ASP.NET 5000 b2 15-JAN-01 05-MAR-01 c C programming 3500 b3 20-JAN-01 27-FEB-01 cs C Sharp 7000 java Java Language 4500 b5 05-APR-01 10-MAY-01 ...
Put (+) on the side of the table that must be extended with null rows (the table that may lack matching rows). A parent row may exist without child rows, but with a foreign key a child row can never exist without its parent.
Set Operators
Set operators combine the results of two queries into one result; they implement the union, intersection and difference operators of relational algebra.
| Operator | Returns |
|---|---|
| UNION | Unique rows of both queries (duplicates removed) |
| UNION ALL | All rows of both queries including duplicates |
| INTERSECT | Rows common to both queries |
| MINUS | Rows of the first query that are not in the second |
COURSES and OLDCOURSES
select ccode from oldcourses union select ccode from courses; -- ora and c appear only once select ccode from courses intersect select ccode from oldcourses; -- courses in both tables select ccode from oldcourses minus select ccode from courses; -- old courses no longer offered
Rules for set operators
- Both queries must select the same number of columns.
- Corresponding columns must have the same datatype (length and names may differ).
- The column names of the first query become the headings.
- NULLs are treated as identical – unlike normal comparisons, two NULLs match in set operations.
Key Takeaways
- A join needs a common column; n tables need at least n − 1 join conditions.
- Missing join condition = Cartesian product.
- Self-join uses two aliases of one table; outer join uses (+) to include unmatched rows.
- UNION removes duplicates, UNION ALL keeps them; INTERSECT and MINUS find common/different rows.
Chapter 9 Notes (PDF)
ch9.pdfKeys: ← → page · + − zoom · F full screen
Question / Answer (15 questions)
Moderate-level questions based on the Chapter 9 notes (PDF). Click a question to see the answer.
Answer : In a relational database the required data is generally scattered over several tables, because storing everything in one table leads to redundancy. Joining is combining two or more tables so that values from the columns of all of them can be retrieved to get the required data.
Answer : There must be a common column between the two tables, which is used in the join condition in the WHERE clause, e.g. CCODE to join COURSES and BATCHES.
Answer : A join in which the tables are joined using the equal-to operator in the join condition, e.g. WHERE batches.ccode = courses.ccode.
Answer : By prefixing the column with the table name or the table alias followed by a dot, e.g. batches.ccode or b.ccode. Unqualified ambiguous columns are not allowed.
Answer : A table alias is a short name used to refer to a table within a query, e.g. FROM batches b, courses c. It is not stored anywhere – it is available only in the query in which it is created.
Answer : The result is the product (Cartesian product) of the tables – each row of the first table is joined with each row of the second. Joining 6 rows of COURSES with 7 rows of BATCHES gives 42 rows. It is generally the result of an error.
Answer : The join condition is the condition used to join the tables on their common column. A normal condition filters rows (e.g. enddate IS NOT NULL). Both are given in the WHERE clause; Oracle filters rows with the normal condition and joins the filtered rows using the join condition.
Answer : The minimum number of join conditions = number of tables being joined − 1. For example, joining BATCHES, COURSES and FACULTY needs two conditions (b.ccode = c.ccode and b.fcode = f.fcode). If more than one column is used to join tables, more conditions may be needed.
Answer : A self-join is joining a table with itself – two copies of the same table (two aliases) are joined as if they were two different tables. Example: SELECT b2.* FROM batches b1, batches b2 WHERE b1.bcode = 'b3' AND b2.stdate > b1.stdate lists batches that started after batch b3.
Answer : A normal join returns only rows that have common key values in both tables, so a parent row without child rows – e.g. course CS that has no batch – is not included. An outer join retrieves rows from the parent table even when there are no corresponding child rows.
Answer : The (+) operator is placed in the join condition on the side of the table that is to be extended with null rows, e.g. WHERE c.ccode = b.ccode (+). Oracle adds null rows to BATCHES for courses that have no batch, so their BCODE, STDATE and ENDDATE are shown as null.
Answer : A parent row may exist without child rows, but a child row cannot exist without a parent row when a foreign key constraint is defined on the child table; Oracle enforces this whenever rows are inserted or updated in the child table.
Answer : UNION returns the unique rows of both queries; UNION ALL returns all rows of both queries including duplicates; INTERSECT returns the rows common to both queries; MINUS returns the rows that exist in the first query but not in the second.
Answer : Both queries must select the same number of columns; corresponding columns must be of the same datatype (length and names may differ); the column names of the first query become the column headings; and null values are treated as identical.
Answer : Normally two nulls are not equal, but in set operators Oracle treats two null values as identical. If both tables have a row with C1 = 10 and C2 = null, UNION displays that row only once.
MCQ Test : Check Your Understanding (5 questions)
Select an option and press Check Answer, or answer all questions and press Submit Test to see your score. Each answer comes with an explanation.
Bibliography/References
The chapter notes PDF is the course study material and is credited to its author below. The explanations, questions and MCQs on this page are original study aids written for this course from the topics covered in that PDF. The other works are cited for verification and further reading. Oracle, SQL*Plus and PL/SQL are trademarks of Oracle Corporation; other names belong to their respective owners.
- Srikanth, P. (n.d.). Oracle For Beginners (chapter notes – the PDF shown on each chapter page). Srikanth Technologies. http://www.srikanthtechnologies.com/
- Codd, E. F. (1970). A Relational Model of Data for Large Shared Data Banks. Communications of the ACM, 13(6), 377–387. https://doi.org/10.1145/362384.362685
- Oracle Corporation. (n.d.). Oracle Database SQL Language Reference, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/
- Silberschatz, A., Korth, H. F., & Sudarshan, S. (2019). Database System Concepts (7th ed.). McGraw-Hill Education.
- Elmasri, R., & Navathe, S. B. (2016). Fundamentals of Database Systems (7th ed.). Pearson.