SQL For Dummies®, 7th Edition

Chapter 10. Using Relational Operators

You probably know by now that SQL is a query language for relational databases. In previous chapters, I present simple databases, and in most cases, my examples deal with only one table. In this chapter, I put the relational in "relational database." After all, the name means "a database that consists of multiple related tables." Here's where we scrutinize those relationships.

Because the data in a relational database is distributed across multiple tables, a query usually draws data from more than one table. SQL has operators that combine data from multiple sources into a single result table. These are the UNION, INTERSECTION, and EXCEPT operators, as well as a family of JOIN operators. Each operator combines data from multiple tables in a different way.

UNION

The UNION operator is the SQL implementation of relational algebra's union operator. The UNION operator enables you to draw information from two or more tables that have the same structure. Same structure means

• The tables must all have the same number of columns.

• Corresponding columns must all have identical data types and lengths.

When these criteria are met, the tables are union-compatible: The union of the two tables returns all the rows that appear in either table and eliminates duplicates.

Suppose you create a baseball-statistics database (like the one in Chapter 9). It contains two union-compatible tables named AMERICAN and NATIONAL. Both tables have three columns, and corresponding columns ...