Java Reference
In-Depth Information
The most important thing to understand when discussing SQL JOINS is the use of
primary and foreign keys. Database-management systems use the two following
kinds of keys:
Primary keys
Foreign keys
In each of the four tables in the sample database, there is an identifier such as
customer number or item number. These identifiers are the
primary keys
and are
used to provide a unique reference to a given record. A primary key is a column that
uniquely identifies the rest of the data in any given row. For example, in the
Customers Table, the Customer_Number column uniquely identifies that customer.
For this to work, no two rows can have the same key or, in this instance,
Customer_Number.
A
foreign key
is a column in a table where that column is a primary key of another
table. For example, the Orders Table contains one column for Order_Number, which
is the primary key for the Orders Table, and another column for the
Customer_Number, which is a foreign key. In effect, the foreign key acts as a pointer
to a row in the Customers Table.
The purpose of these keys is to establish relationships across tables, without having
to repeat data in every table. This concept encapsulates the power of relational
databases.
Accessing data from multiple tables with Equi-Joins
SQL Joins work by matching up equivalent columns in different tables by comparing
keys. The most common type of Join is an Equi-Join, where you look for items in one
table which have the same item number as items in another.
Writing SQL JOIN Commands
There are two ways to write SQL JOIN statements. The first is through the specific
use of the key word JOIN:
SELECT First_Name, Last_Name, Order_Number
FROM CUSTOMERS c INNER JOIN
ORDERS o ON c.Customer_Number = o.Customer_Number;
This statement will return exactly the same results as the short form:
SELECT First_Name, Last_Name, Order_Number
FROM CUSTOMERS c, ORDERS o
WHERE c.Customer_Number = o.Customer_Number;
