SQL at Depth: Subqueries, Set Operations and Views
A query can use another query as a table, as a value, or as a test of existence, and set operations combine results that share a shape. A view names a query so the rest of the system can treat it as a table.
What a learner can do afterwards
- Rewrite a correlated subquery as a join and say when the two differ
- Use exists and not exists to express a question about absence
- Define a view and explain what it does and does not store
1 · Read
A query can nest inside another as a table, as a single value, or as an existence test. EXISTS asks whether matching rows occur, and NOT EXISTS asks whether none occur. Set words like UNION stack results that share the same shape.
To list customers who never ordered, you write: SELECT name FROM Customers c WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.customer_id = c.id). The same ask runs a second way: LEFT JOIN Orders and keep rows where the order side is NULL.
A view names a query so later queries can treat it as a table. It stores the query text, not the rows, so each use runs fresh against live data. That keeps logic in one place without copying data.
A join and a subquery can differ. Joins multiply rows on many matches and NOT IN breaks on NULLs, while NOT EXISTS stays steady. Pick the form whose edge behavior you can defend.
Test absence with NOT EXISTS, stack shapes with set words, and name repeats with views.
2 · Watch
Take it off screen
Where it sits
8 questions wait behind this lesson, each with its answer explained. Every answer feeds the sky: stars light as they are learned, and dim when it is time to come back.