In a database, you can have multiple tables containing related information. You can extract information from multiple tables as part of a query. In this case, you specify multiple table names in the SELECT statement, as follows:

SELECT LastName, FirstName, Street, City, State, Zip
    FROM employees, addresses 
    WHERE employees.EmpID = addresses.EmpID
    ORDER BY LastName, FirstName

This SELECT statement uses the EmpID field to connect the two tables. This query prefixes the EmpID column with the table name. This is necessary because each table has a column named EmpID. You must prefix a column name with its table name if the column name appears in multiple tables.

In this case, you extract LastName and FirstName information from the employees table and Street, City, State, and Zip information from the addresses table. You can use output such as this is to generate mailing addresses for an employee newsletter.

The results of a SELECT statement that references multiple tables is a single result table containing a join of the information from corresponding rows. A join means information from two or more rows is combined to form a single row of the result. In this case, the resultant record set has the following structure:

What is interesting in this result is that even though you used the EmpID field to combine information from the two tables, you did not include that field in the output.

Modifying a database

You can use SQL to modify a database in the following ways:

• Inserting data into a database
• Updating data in a database
• Deleting data from a database
• Updating multiple tables