Sometimes the data you want is not stored in any single table in your database, but instead pieces of it are scattered across multiple tables. SQL offers several methods of gathering such far-flung data and presenting it to you integrated together in a nice compact result set, some of which are described here:
-
Relational operators have the ability to combine information from multiple sources in a variety of ways.
For example, the UNION operator will return all the rows that appear in either of two compatible tables. In contrast, the INTERSECT operator will return only the rows that appear in both of two compatible tables. When you use the EXCEPT operator, all the rows from one table will be returned, except for rows that match rows in the second table.
In addition, a wide variety of join operators will enable you to fine-tune your retrievals, pulling just the data you want from whichever tables it might reside in. -
Nested queries allow you to retrieve data from multiple tables. These are queries on one table that include a subquery on a different table. The subquery is executed first and its result is then passed to the main query to provide a result that contains information from both.
-
Recursive queries have the rather amazing ability to call themselves. This ability can be a major time saver, enabling a search tree to be traversed in a lot less time that an exhaustive search would take. Bill of Materials databases are places where recursive queries can deliver major performance gains.