IS5413 Chap.10 Joins, Subqueries and Transaction Control in SQL
Joins, Subqueries and Transaction Control in SQL
A join is a relationship read backwards
A join combines two or more tables sharing a common domain into a single result. The shared columns are almost always the primary key of the table on the one side and the foreign key of the table on the many side, which means the joins you write are the relationships you drew earlier, read back out.
The published procedure for building one is explicit: be familiar with the data model, know the attributes the answer needs, identify the entity types that hold them, review the diagram, and construct one equality condition for each link.
That last instruction gives a free arithmetic check, since the number of equality conditions should be the number of tables minus one.
Four families, differing only in which unmatched rows survive
An equi join matches rows where the shared columns hold the same value, and that shared column is then printed twice.
A natural join is defined by the course as the same match with one copy of the repeated column dropped, so the shared column is printed once. An outer join keeps rows that have no match on the other side, appearing with empty columns, in contrast with an inner join where rows must match to appear at all. A union join includes every column of each table and a row for each row of each.
A self join is one table joined to itself through aliases, used for the unary relationships from the modelling chapters. Note one shorthand in the course material: a summary slide writes equi join, inner join and natural join as equivalents, while the definition slide distinguishes the natural join by its removal of the duplicate column.
Both statements appear; give the fuller definition whenever a question asks you to distinguish them.
Subqueries, and the difference that is really about cost
A subquery is a select statement placed inside another, sitting in the row condition, in the table list as a derived table, or in the group filter. A noncorrelated subquery runs once for the whole outer query.
A correlated subquery runs once for every row the outer query produces, because it refers to a value from that row. That distinction is a performance fact before it is a syntax fact, and it is why the published advice is to limit subqueries and prefer a single statement where one exists.
Many questions can be answered either way, and the deciding factor is usually whether the answer needs columns from both tables: if it does, a join is required, because a subquery returns nothing to the outer select list.
Making the work permanent, and the routines that watch it
A transaction is one indivisible piece of work that either completes in full or leaves nothing behind, bounded by begin and end, made permanent by commit and undone by rollback.
Alongside transactions sit two kinds of routine that differ by how they start rather than by what they do: procedures are called explicitly by name and may take input or output parameters, while triggers execute implicitly in response to an insert, update or delete. Functions return a value and take input parameters.
Views complete the set, giving controlled access through a named statement, dynamic when nothing is stored and materialised when a copy is kept and has to be refreshed.
What this chapter covers
- 01
One equality condition per link, and the arithmetic check
- 02
Equi, natural, outer, union and self joins
- 03
Correlated against noncorrelated subqueries
- 04
Choosing between a join and a subquery
- 05
Transaction boundaries, commit and rollback
- 06
Procedures, functions and triggers
- 07
Dynamic against materialised views
Worked example · free
Build a four table statement and set its transaction boundary
- 3State the number of equality conditions and name each link.
- 2Place the appointment reference condition and justify it.
- 2Say what happens if one link condition is omitted.
- 3Set the transaction boundary for the instalment payments.
Key terms
- Equi Join
- A join matching rows whose shared columns hold the same value, which leaves that column printed twice.
- Natural Join
- The same equality match with one copy of the repeated column dropped from the result, so the shared column is printed once.
- Outer Join
- A join that keeps rows without a match on the other side, which then appear with empty columns, unlike an inner join where only matches survive.
- Correlated Subquery
- An inner query that refers to a value from the outer query and is therefore executed once for every row the outer query produces.
- Transaction
- One indivisible piece of work that either completes in full or leaves nothing behind, made permanent by a commit and reversed entirely by a rollback.
- Trigger
- A routine that executes implicitly in response to a database event such as an insert, update or delete, as opposed to a procedure which is called by name.
- Materialised View
- A stored copy of a query result, which must be refreshed periodically to match the base tables, unlike a dynamic view which stores nothing.
Joins, Subqueries and Transaction Control in SQL FAQ
How many join conditions should a four table query have?
Three, one for each link in the diagram, which is the number of tables minus one. More than that means a relationship has been invented; fewer means a table has been left unjoined, and the consequence of that is not an error message but a result containing every combination of the unjoined table's rows. Counting conditions against tables takes two seconds and catches both faults.
Should I use a join or a subquery?
Use a join when the answer needs columns from both tables, because a subquery returns nothing to the outer select list. Use a subquery when the second table serves only as a filter and the result reads more clearly for it. Where a question invites the comparison, say which you chose and why, and note that a correlated subquery runs once per outer row, which is the reason the course advises limiting them.
Exam move
Practise only the skeleton for a week: for each question, list the attributes, name the owning tables, count the tables, write that many minus one equality conditions, and stop. Filling in the select list afterwards is mechanical.
Then take three update scenarios from your own project and draw the transaction boundary around each, saying in one sentence what would be inconsistent if the boundary were drawn one statement narrower.
Working through Joins, Subqueries and Transaction Control in SQL in IS5413? Sia is AskSia’s AI Information Technology tutor — ask any IS5413 Joins, Subqueries and Transaction Control in SQL question and get a clear, step-by-step explanation grounded in how IS5413 is taught and assessed. Read this chapter free, then take your hardest questions to Sia.
