Theory
How do you find a record based on an unknown secret?
Imagine a manager asks you to find all helpdesk tickets in our TicketDesk system that were raised after the ticket titled 'Server Crash'. You do not know the exact date when 'Server Crash' occurred. In a standard database query, you would have to run one query to find the date of that crash, copy it down, and write a second query using that date. This manual approach fails completely in automated production systems and university exams. How can you make one query discover the date and immediately feed it into a second query?
Theory
The Box Inside a Box Lookup
Think of a nested query like a puzzle box where the key to opening the outer chest is hidden inside a smaller inner box. You cannot open the outer chest until you first crack open the inner box to retrieve the key. In SQL, the database engine solves the inner query first, extracts the single value or list of values, and then uses that fresh information as a constant value to filter and run the outer query automatically.
Theory
Defining Nested Queries Formally
In the Oracle dialect, a nested query is a SELECT statement written inside the WHERE or HAVING clause of an outer query. The inner query executes first and passes its results to the outer query. Nested queries are classified into single-row subqueries, which return exactly one value and use operators like =, >, or <, and multi-row subqueries, which return a column of values and require operators like IN, ANY, or ALL to prevent structural mismatches.
At a glance
Classification of Oracle nested queries based on inner query outputs and matching operators
| Query Type | Returned Rows | Allowed Operators | TicketDesk Instance |
|---|---|---|---|
| Single-Row | Exactly 1 row and 1 column | =, >, <, != | Finding tickets raised after a single known ticket's date |
| Multi-Row | Multiple rows and 1 column | IN, ANY, ALL | Finding tickets assigned to a specific list of active agents |
| Correlated | Depends on outer loop rows | EXISTS, NOT EXISTS | Finding agents who handle at least one ticket with Critical status |
Practical
Writing Single-Row and Multi-Row Nested Queries
-- Step 1: Single-row nested query to find tickets raised after 'Server Crash'
SELECT id, title, raised_on
FROM tickets
WHERE raised_on > (SELECT raised_on FROM tickets WHERE title = 'Server Crash');
-- Step 2: Multi-row nested query to find tickets assigned to specific agents
SELECT title, priority
FROM tickets
WHERE agent_id IN (SELECT id FROM agents WHERE name LIKE 'Amit%');This example runs in Gri-Learn on the web, where you can edit it and see the output.
Quiz
What will happen if an outer query uses the '=' operator, but the inner nested query returns more than one row?
- Oracle will automatically select the first row and ignore the rest
- Oracle will throw a runtime error because a single-row operator cannot compare multiple values
- The outer query will execute but return zero rows as a safety measure
- Oracle will switch the '=' operator to 'IN' dynamically behind the scenes
Show the answer
Oracle will throw a runtime error because a single-row operator cannot compare multiple values
When you use a single-row comparison operator like =, Oracle expects exactly one value. If the inner nested query returns multiple rows, Oracle encounters a mismatch and throws the famous ORA-01427 error: single-row subquery returns more than one row. To handle multiple rows, you must use a multi-row operator like IN.
Think first
Mental Challenge: Finding Tickets Above Average
Mentally write a nested query to find the id and title of all tickets from the tickets table whose priority level or numerical rating is higher than the average rating of all tickets. Think about the aggregate function you need before tapping to reveal.
Show the answer
The query is: SELECT id, title FROM tickets WHERE priority_level > (SELECT AVG(priority_level) FROM tickets); The inner query calculates the single average value first, and then the outer query compares each individual row against that average result.
Watch out
The Column Count Mismatch Trap
A common mistake that costs marks in university exams is selecting multiple columns in the inner query when the outer query expects only one. For example, writing WHERE agent_id IN (SELECT id, name FROM agents). Oracle will reject this with a syntax error because it cannot compare a single ID column against a dual-column result. Ensure your inner query selects exactly one column that matches the datatype of the outer column.
Theory
Real World Use and Future Semesters
Nested queries are vital when building complex reports or handling backend logic for AI dashboards. Instead of fetching data to your frontend code and running slow loops, a nested query runs entirely on the database server for maximum efficiency. In your Sem 3 SQLite curriculum, you will use these identical nested query structures to filter local data inside mobile applications without writing heavy boilerplate code.
Summary
Key takeaways
- A nested query is a SELECT statement placed inside another query's WHERE or HAVING clause.
- The inner query executes first, and its output is used by the outer query as a filtering constraint.
- Single-row nested queries return one value and require single-value operators like = or >.
- Multi-row nested queries return multiple records and require list operators like IN, ANY, or ALL.
- Inner queries must return exactly one column to avoid structural column mismatch errors.
- Memory hook: Inner query uncovers the secret, outer query delivers the final result!