--- title: "SQL at Depth: Subqueries, Set Operations and Views" description: "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 " canonical: https://lightmysky.com/learn/computing/sql-at-depth-subqueries-set-operations-and-views-mt_SLKUlC-7or source: https://lightmysky.com/learn/computing/sql-at-depth-subqueries-set-operations-and-views-mt_SLKUlC-7or.md retrieved: 2026-11-12 --- > **Agent view.** This is the Markdown twin of the page, for tools and assistants. > When to use this site, and the call that answers each job: https://lightmysky.com/agent-instructions.md > API description (OpenAPI 3.1): https://lightmysky.com/openapi.json · Authentication: https://lightmysky.com/auth.md > Pricing: https://lightmysky.com/pricing.md · Catalog: https://lightmysky.com/llms.txt · Full catalog: https://lightmysky.com/llms-full.txt > Every machine-readable file on this domain: https://lightmysky.com/.well-known/ai-catalog.json > Ask for Markdown with `Accept: text/markdown`, a `.md` address, or `?mode=agent`. # 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. Subject: Computing · Area: Data & Databases · Ages 18 to 20 Page: https://lightmysky.com/learn/computing/sql-at-depth-subqueries-set-operations-and-views-mt_SLKUlC-7or ## Ready when they can - 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 ## Lesson: Asking about rows that are missing 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. **Example.** 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. **Recap.** Test absence with NOT EXISTS, stack shapes with set words, and use views for repeats. ## Practice 8 questions on this page, each with its working shown. ## Needs first - [SQL: Selecting, Filtering and Ordering Rows](https://lightmysky.com/learn/computing/sql-selecting-filtering-and-ordering-rows-mt_lDVVG2AKzs) ## Optional connections - [Functional Dependencies and Boyce-Codd Normal Form](https://lightmysky.com/learn/computing/functional-dependencies-and-boyce-codd-normal-form-mt_PfWLq1aEQz) ## Opens up - [Window Functions and Running Calculations](https://lightmysky.com/learn/computing/window-functions-and-running-calculations-mt_aaVxFPjWQv)