User's Guide Part II. Tutorials Chapter 8. Joining TablesIn order to understand how to construct other kinds of joins, you must first understand how the information in one table is related to that in another.
The primary key for a table identifies each row in the table. Tables are related to each other using a foreign key.
This section shows how primary and foreign keys together let you construct queries from more than one table.
Every table in the employee database has a primary key. A primary key is one or more columns that uniquely identify a row in the table. For example, an employee number uniquely identifies an employee---emp_id is the primary key of the employee table.
The sales_order_items table is an example of a table with two columns that make up the primary key. The order ID by itself does not uniquely identify a row in the sales_order_items table because there can be several items in an order. Also, the line_id number does not uniquely identify a row in the sales_order_items table. Both the order ID name and line_id are required to uniquely identify a row in the sales_order_items table. The primary key of the table is both columns taken together.
There are several tables in the employee database that refer to other tables in the database. For example, the sales_order table has a sales_rep column to indicate which employee is responsible for an order. Only enough information to uniquely identify an employee is kept in the sales_order table. The sales_rep column in the sales_order table is a foreign key to the employee table.
A foreign key is one or more columns that contain primary key values from another table. Each foreign key relationship in the employee database is represented by an arrow between two tables. The arrow starts at the foreign key side of the relationship and points to the primary key side of the relationship.