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 gives a query a name 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. A short alias after a table name stands for that table in the rest of the query. EXISTS asks whether matching rows occur, and NOT EXISTS asks whether none occur. Set words like UNION stack results that share the same shape. UNION removes duplicate rows, while UNION ALL keeps them. For example, stacking 3 and 4 with 3 and 4 gives 3 and 4 once each under UNION.
To list suppliers who never shipped, you write: SELECT name FROM Suppliers s WHERE NOT EXISTS (SELECT 1 FROM Shipments h WHERE h.supplier_id = s.id). The same ask runs a second way: SELECT name FROM Suppliers s LEFT JOIN Shipments h ON h.supplier_id = s.id WHERE h.supplier_id IS NULL. The join keeps every supplier. s stands for Suppliers and h stands for Shipments. An unmatched supplier gets NULL in the shipment columns, so the IS NULL test finds it.
A view gives a query a name 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. You define one with CREATE VIEW, followed by the view name, AS, and the query. That keeps logic in one place without copying data, like one home per fact.
Two matching shipments give one supplier two joined rows, while EXISTS asks if a match occurs. IN means equality with any listed value. For a customer with no matching order, one NULL id makes that test unknown, not false. NOT keeps unknown, and WHERE keeps only true rows, so that customer is left out. A matching customer gives true, so NOT IN is false. For a customer with no matching order, NOT EXISTS finds no match, so that customer stays, while a matching customer is excluded.
Test absence with NOT EXISTS, stack shapes with set words, and use views for repeats.
2 · Watch
En anglaisTake it off screen
Where it sits
Learn first
Optional connections
This opens up
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.