ISYS1055 Chap.8 Nested Queries and FROM-Clause Sub-SELECTs
Nested Queries and FROM-Clause Sub-SELECTs
Week 6's second outcome is to embed one query inside another so that complex logic decomposes into smaller sub-problems, and the course names three shapes: a sub-SELECT in the FROM clause, a subquery in WHERE with IN, and a correlated subquery with EXISTS. The principle underneath all three is that the result of a SELECT is no different from a table already in the database, so it can be used wherever a table, a list or a truth value is accepted. In assessment this is the second half of the Week 6 practical work, it is quiz material in Week 7, and it is what the later, heavier queries of Part B in Assessment Task 2 are usually asking for - the practical set even requires you to rewrite the same query in a different form.
What this chapter covers
- 01The principle: a result set is just another table, so a SELECT may appear wherever a table, a value list or a truth value is accepted
- 02FROM-clause sub-select (derived table): the inner query runs first, its result is held as a temporary table and must be given an alias
- 03Subquery in WHERE with IN: the inner query must return exactly one attribute, whose values form the list matched against the outer attribute; NOT IN inverts it
- 04EXISTS and NOT EXISTS: true if and only if the subquery result is non-empty, with nothing on the left-hand side to compare
- 05Correlation: with EXISTS the outer query drives, so the inner query may refer to the outer query's attributes - the correlation is the join, and SELECT * inside is conventional
- 06Decomposing an information need into plain-English sub-problems before writing any SQL
- 07Writing the same question as a join, as IN and as EXISTS, and converting between the three forms
- 08When to prefer which: decompose when the logic has a clear inner sub-problem, join when you need columns from both sides in the output
One question, three nested forms - IN, EXISTS and a FROM-clause sub-select
- +1Decompose the question in English first, which is the step the course insists on. Sub-problem 1: which exhibits are on loan? That is loan_item. Sub-problem 2: which galleries do those exhibits sit in? That is exhibit. Sub-problem 3: what are those galleries called? That is gallery. The nesting writes itself once the sub-problems are named.
- +1The IN form. The inner query must return exactly one attribute, and it is that attribute which is matched against the outer one: SELECT gallery_name / FROM gallery / WHERE galleryID IN ( SELECT e.galleryID FROM exhibit e JOIN loan_item l ON e.exhibitID = l.exhibitID ); - the inner query runs first and produces a list of gallery identifiers, and the outer query keeps the rows whose key is in that list. NOT IN would answer the opposite question, but be careful with it if the inner list can contain a null.
- +1The EXISTS form. EXISTS is true if and only if its subquery returns at least one row; there is nothing on its left to compare with, and the inner SELECT list is irrelevant, so SELECT * is conventional. SELECT g.gallery_name / FROM gallery g / WHERE EXISTS ( SELECT * FROM exhibit e JOIN loan_item l ON e.exhibitID = l.exhibitID WHERE e.galleryID = g.galleryID ); - note the reference to g.galleryID inside the inner query. That correlation is what makes this a correlated subquery: the order of evaluation is inverted, the outer query drives, and each gallery is tested in turn.
- +1The FROM-clause form. The inner query is materialised as a derived table and must be given an alias, and then it is used exactly like a base table: SELECT DISTINCT g.gallery_name / FROM gallery g JOIN ( SELECT e.galleryID FROM exhibit e JOIN loan_item l ON e.exhibitID = l.exhibitID ) onloan / ON g.galleryID = onloan.galleryID; - DISTINCT is needed here because a gallery holding three loaned exhibits would otherwise appear three times, whereas the IN and EXISTS forms test each gallery once and cannot duplicate it.
Key terms
- Derived table (FROM-clause sub-select)
- A subquery written in the FROM clause whose result is held as a temporary table for the outer query to work against. It must be given an alias, and the inner query is evaluated first.
- IN subquery
- A subquery in the WHERE clause returning exactly one attribute, whose values become the list that the outer attribute is tested against. NOT IN inverts the test; a null inside the list makes NOT IN return no rows.
- EXISTS
- A predicate that is true if and only if its subquery returns at least one row. Nothing appears on its left-hand side, and because only emptiness matters, the inner SELECT list is conventionally written as *.
- Correlated subquery
- A subquery that refers to attributes of the outer query, so it cannot be evaluated on its own. The outer query drives, feeding each row's values inward, and that correlation does the work a join condition would otherwise do.
- Decomposition
- The habit of splitting an information need into plain-English sub-problems before writing SQL. Each sub-problem becomes either a nested query or a join, and the course teaches this as the method that makes hard queries writable.
- Reusable inner query
- An inner query that recurs across your work is a candidate to become a view - a named, stored query that behaves like a virtual table, which is the Week 7 topic.
Nested Queries and FROM-Clause Sub-SELECTs FAQ
When should I use a nested query instead of a join?
Use a nested query when the question breaks naturally into an inner sub-problem and the output only needs columns from the outer table - "which galleries hold something on loan" needs gallery columns, so the loan logic can be nested away. Use a join when the result has to show columns from both sides. Many questions can be written either way, and being able to convert between the forms is itself assessable.
What is the difference between IN and EXISTS?
IN compares a value against a list, so the inner query must return exactly one attribute and is evaluated to produce that list. EXISTS compares nothing - it simply asks whether the inner result is non-empty - and because it typically references the outer row it is correlated, meaning the outer query drives and the inner one is evaluated per outer row. As a rule of thumb, IN reads better for membership against a computed list, and EXISTS reads better for "is there at least one matching row".
Why does my FROM-clause sub-select fail with a syntax error?
Most often because the derived table has no alias. A subquery in the FROM clause produces a table, and a table in a join needs a name to reference its columns by. The second common cause is expecting the inner query to see a column from the outer query, which a FROM-clause sub-select cannot do - only a correlated subquery in WHERE can.
Are sub-selects ever discouraged?
The course poses that question deliberately. A FROM-clause sub-select materialises an intermediate result and can hide a simpler join, and a correlated subquery may be evaluated once per outer row. The answer is not to avoid them but to choose the clearest form for the question - and to remember that a query is not finished until you have run it and checked the row count.
Assessment move
Practise conversion rather than composition: take five queries you have already written as joins and rewrite each one as an IN subquery and again with EXISTS, checking that all three return identical rows. That single exercise covers most of what this chapter can be assessed on, and it is exactly what the Week 6 practical asks for. Get into the habit of writing the plain-English decomposition as a comment above the query before you type any SQL, and keep the inner query runnable on its own so you can test it independently - an inner query that returns the wrong list is invisible once it is nested. Note which inner queries you keep re-using; those are the ones that should become views in Week 7.
Working through Nested Queries and FROM-Clause Sub-SELECTs in ISYS1055? Sia is AskSia’s AI Information Technology tutor — ask any ISYS1055 Nested Queries and FROM-Clause Sub-SELECTs 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.
