SQL For Dummies®

Chapter 10. Using Relational Operators

In This Chapter

• Combining tables with similar structures

• Combining tables with different structures

• Deriving meaningful data from multiple tables

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, relational databases are so named because they consist of multiple related tables.

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 two tables returns all the rows that appear in either table and eliminates duplicates.

Say that you create a baseball statistics database (like the one in Chapter 9). It contains two union‐compatible ...