SQL For Dummies®

Chapter 11. Delving Deep with Nested Queries

In This Chapter

• Pulling data from multiple tables with a single SQL statement

• Comparing a value from one table with a set of values from another table

• Using the SELECT statement to compare a value from a table with a value from another table

• Comparing a value from one table with all the corresponding values in another table

• Making queries that correlate two corresponding rows in tables

• Determining which rows to update, delete, or insert by using a subquery

One of the best ways to protect your data's integrity is to avoid modification anomalies 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.

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 ...