ISYS1055 Chap.7 Multi-Table SQL: Simple, NATURAL, LEFT OUTER and Self Joins
Multi-Table SQL: Simple, NATURAL, LEFT OUTER and Self Joins
Week 6's first outcome is to use the various SQL join methods to write a single statement that retrieves data from more than one table, and the course names four forms explicitly: simple joins, NATURAL JOIN, LEFT OUTER JOIN and self joins. The mechanical recipe is the same every time - work out which tables hold the data, then find a common attribute to join on, normally a foreign key matched against the primary key it references. Joins carry the middle-weight queries in Part B of Assessment Task 2, they are quiz material in Weeks 7 and 11, and they are what Part D of the Database Design Project is testing when it asks for retrieval against your own schema.
What this chapter covers
- 01The joining process conceptually: form the combination of rows, apply the join condition, then project onto the SELECT list
- 02The two-step method for writing any join - identify the tables that hold the data, then identify the common attribute to join on
- 03JOIN ... ON with table aliases, and the older comma form that puts the join condition in WHERE (and silently produces a full combination if you forget it)
- 04Joining three or more tables by nesting the joins inside parentheses, then joining the result to the next table
- 05NATURAL JOIN: no ON clause, the condition taken from same-named columns, common attributes appearing once - and why the course asks you to argue against using it
- 06LEFT OUTER JOIN: every row of the left table survives, with nulls padding the right-hand columns, so table order in FROM matters
- 07Self joins with two aliases, one per role, for role pairing and for finding other rows related through a shared value
- 08The pair-listing idiom: using < rather than <> so each unordered pair appears once and no row joins to itself
A three-table join, then the outer join that stops silently dropping rows
- +1Identify the tables and the join keys before writing anything. Trader names live in trader, zones in stall and product names in product; trader joins to stall on traderID (foreign key to primary key) and stall joins to product on stallID. That is the whole design of the query - the SQL is then transcription.
- +1Write the two-table join first and check it. SELECT t.trader_name, s.zone / FROM trader t JOIN stall s ON t.traderID = s.traderID; - aliases t and s are good practice always and mandatory once a table appears twice. The comma form, listing both tables in FROM and putting the condition in WHERE, returns the same rows, but if the condition is omitted you silently get every combination of rows instead of an error, which is why the explicit ON form is preferred.
- +1Extend to three tables by nesting the joins inside parentheses, then joining the result to the third table: SELECT t.trader_name, s.zone, p.product_name / FROM (trader t JOIN stall s ON t.traderID = s.traderID) / JOIN product p ON s.stallID = p.stallID; - the first join produces an intermediate result, and the second joins that result to product on the stall key.
- +1For (b), an inner join would silently drop exactly the traders the question asks about, because they have no matching stall row. LEFT OUTER JOIN keeps every row of the left table whether or not a match exists and pads the right-hand columns with nulls: SELECT t.trader_name, s.zone / FROM trader t LEFT OUTER JOIN stall s ON t.traderID = s.traderID; - the table whose rows must all appear goes on the left, so swapping the order changes the answer. A stall-less trader appears with a null zone, which is the row the market committee wanted to see.
Key terms
- Join condition
- The predicate that decides which combinations of rows survive, normally a foreign key matched against the primary key it references. It is written after ON in the explicit form, or in the WHERE clause in the older comma form.
- Table alias
- A short name given to a table in the FROM clause and used to qualify column references. Aliases are mandatory when the same table appears more than once in a query, and good practice in every join.
- NATURAL JOIN
- A join whose condition is implicit, taken from columns that share a name in both tables, with the common attributes appearing only once in the result. It requires that no other same-named columns exist, and the course asks you to argue why it is discouraged: the condition is invisible in the query text, so renaming or adding a column silently changes what the query means.
- LEFT OUTER JOIN
- Returns every row of the left-hand table whether or not a matching right-hand row exists, padding the right-hand columns with nulls where there is no match. The table whose rows must all appear goes on the left, so the order in FROM changes the answer.
- Self join
- A join of a table to itself using two aliases, one per role. It answers questions about pairs of rows in the same table - a member and their sponsor, or two rows sharing a value - and works with inner or outer joins.
- The < pair idiom
- In a self join that lists pairs, joining on a strict inequality of the key rather than on <> returns each unordered pair exactly once and excludes the row that would otherwise join to itself.
Multi-Table SQL: Simple, NATURAL, LEFT OUTER and Self Joins FAQ
What is the difference between JOIN ... ON and listing tables in FROM with the condition in WHERE?
For an inner join they return the same rows - the comma form is the older style. The difference is safety and readability: with the explicit ON form the join condition sits next to the join it belongs to, whereas in the comma form omitting the condition is not an error, it just silently produces every combination of rows, which on real tables is both wrong and slow. Write the explicit form, and keep filtering conditions in WHERE where they belong.
Why is NATURAL JOIN discouraged?
Because the join condition is invisible. It is taken from whatever columns currently share a name in both tables, so the meaning of the query depends on the schema rather than on the query text, and adding or renaming a column can silently change which rows come back. It also does not apply at all when the foreign key and the primary key have different names, which is common - a country code column named CountryCode will not naturally join to a key named Code.
How do I join three tables?
Nest the joins. Parenthesise the first join so it produces an intermediate result, then join that result to the third table on the appropriate key. Build it in stages and check the row count as each table joins, because a wrong key in the second join is much easier to see against a known intermediate result than in one finished statement.
When would I need a self join?
Whenever the relationship you are querying lives inside one table. Two typical shapes: role pairing, where a foreign key in a row points at another row of the same table, such as a member and their sponsor; and finding rows that share a value with a given row, such as the other products sold in the same zone as a named product. Both need two aliases so the query can tell the two roles apart.
Assessment move
Decompose in English before you write SQL - the course makes this point explicitly, and it is what turns a hard query into two easy ones. Say the sentence out loud ("find the stalls in the zone where the honey seller trades, then list their products"), and the joins will be obvious. Practise by writing the same information need three ways, since Assessment Task 2 Part B rewards being able to pick the clearest form and Week 6's second half will add nested queries as a fourth. Always check whether the question contains the words "including those with none", which is the signal for a left outer join, and always name your aliases meaningfully rather than a, b, c. Run every join in SQLite Studio and eyeball the row count before you believe it.
Working through Multi-Table SQL: Simple, NATURAL, LEFT OUTER and Self Joins in ISYS1055? Sia is AskSia’s AI Information Technology tutor — ask any ISYS1055 Multi-Table SQL: Simple, NATURAL, LEFT OUTER and Self Joins question and get a clear, step-by-step explanation grounded in how ISYS1055 is taught and assessed. Read this chapter free, then take your hardest questions to Sia.
