For example:

SELECT "list-of-columns"
FROM table1,table2
WHERE "search-condition(s)"

如果你不會使用JOIN，而只會使用單一資料表，那麼那一個資料表勢必會非常"肥大"。我們以以下的商品販售資料表來說明:

id	first	last	address	city	state	zip	date	item	price

當有新資料進來，那每一個欄位都要更動，也同時加入很多不需要的資料。例如每一次 Wolfgang Schultz 買東西，那這些資料也會一併被加入：

id	first	last	address	city	state	zip	date	item	price
10982	Wolfgang	Schultz	300 N. 1st Ave	Yuma	AZ	85002	032299	snowboard	45.00
10982	Wolfgang	Schultz	300 N. 1st Ave	Yuma	AZ	85002	082899	snow shovel	35.00
10982	Wolfgang	Schultz	300 N. 1st Ave	Yuma	AZ	85002	091199	gloves	15.00
10982	Wolfgang	Schultz	300 N. 1st Ave	Yuma	AZ	85002	100999	lantern	35.00
10982	Wolfgang	Schultz	300 N. 1st Ave	Yuma	AZ	85002	022900	tent	85.00

我們可以把它切成兩個表:

1)一為客戶資料表

2) 一為每次售出記錄表

"Customer_info" 客戶表:

customer_number	firstname	lastname	address	city	state	zip

"Purchases" 售出紀錄 :

customer_number	date	item	price

現在，每一筆由老客戶所下的交易，只有第二張表會被更動， 我們避免了多餘的資料，這是一個正規化的資料表了。

注意，現在每一個表中都有共同的欄位 "cusomer_number"。這個欄位包含唯一值才正確使用 JOIN 來聯結兩張表。使用兩張表，如果才能同時顯現公司名(customer's name)、及他們購買的物品( items) 我們使用聯結的方式：

SELECT customer_info.firstname, customer_info.lastname, purchases.item
FROM customer_info, purchases
WHERE customer_info.customer_number = purchases.customer_number;

這一個特殊的 "Join" ，我們稱為 "Inner Join" or "Equijoin" ，用在共同型態欄位使用，這是最常見的方式。

注意每一個欄位名之前總是再加上個資料表名及小數點，這不是一定要的，但他是一個很好的方式，建議你養成這樣的習慣。因為在兩張表中有時會出現相同欄位名的情形，所以建議在欄名前再加上資料表名。

以下為 ANSI SQL-92 語法：

SELECT customer_info.firstname, customer_info.lastname, purchases.item
FROM customer_info INNER JOIN purchases
ON customer_info.customer_number = purchases.customer_number;

Another example:

SELECT employee_info.employeeid, employee_info.lastname, employee_sales.comission
FROM employee_info, employee_sales
WHERE employee_info.employeeid = employee_sales.employeeid;

This statement will select the employeeid, lastname (from the employee_info table), and the comission value (from the employee_sales table) for all of the rows where the employeeid in the employee_info table matches the employeeid in the employee_sales table.

Use these tables for the exercises
items_ordered
customers

練習題

1) Write a query using a join to determine which items were ordered by each of the customers in the customers table. Select the customerid, firstname, lastname, order_date, item, and price for everything each customer purchased in the items_ordered table.

2) Repeat exercise #1, however display the results sorted by state in descending order.

Click the exercise answers link below if you have any problems.

Answers to these Exercises

有關 Outer Joins