SQL For Dummies®, 7th Edition

Chapter 11. Delving Deep with Nested Queries

One of the best ways to protect your data's integrity is to avoid modification anomalies (see Chapter 5 for the gory details of those) by normalizing your database. Normalization involves breaking up a single table into multiple tables, each of which has a single theme. You don't want product information in the same table with customer information, for example, even if the customers have bought products.

If you normalize a database properly, the data is scattered across multiple tables. Most queries that you want to make need to pull data from two or more tables. One way to do this is to use a join operator or one of the other relational operators (UNION, INTERSECT, or EXCEPT). The relational operators take information from multiple tables and combine it all into a single table. Different operators combine the data in different ways.

Note

Another way to pull data from two or more tables is to use a nested query. In SQL, a nested query is one in which an outer enclosing statement contains within it a subquery. That subquery may serve as an enclosing statement for a lower-level subquery that is nested within it. There are no theoretical limits to the number of nesting levels a nested query may have, but you do face some practical limits that depend on your SQL implementation.

Subqueries are invariably SELECT statements, but the outermost enclosing statement may also be an INSERT, UPDATE, or DELETE statement.

Tip

A subquery can operate on a table ...