JOIN operators

Queries created on a single table can sometimes provide you with the necessary data. However, in practice, most queries require data that is acquired from multiple tables. To create a query that will combine data from multiple tables into a single result set requires you to use a powerful relational operator called JOIN:

Figure 6-1. Two table join operation

The JOIN operator provides you with the functionality to combine data from two or more tables into a single result set. Related tables are created with common columns, usually named primary and foreign key. These keys are used to join related tables to each other. 

The database engine performs table joins in a way that it takes the record from the left table and, usually based on the common field, checks for one or more matches in the right table. There are several types of JOIN operators, but the most...