关键字:INNER JOIN , ON
Customers表
CustomerID FirstName LastName
-------------------------------------------------------
1 William Smith
2 Natalie Lopez
3 Brenda Harper
4 Adam Petrie
Orders表
OrderID CustomerID Quantity PricePerItem
---------------------------------------------------------------------------
1 1 4 2.50
2 2 10 1.25
3 2 12 1.50
4 3 5 4.00
内连接:
SELECT *
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
结果
CustomerID FirstName LastName CustomerID OrderID Quantity PricePerItem
1 William Smith 1 1 4 2.50
2 Natalie Lopez 2 2 10 1.25
2 Natalie Lopez 2 3 12 1.50
3 Brenda Harper 3 4 5 4.00
依据CustomersID进行内连接的交集。前缀用于区分两个不同的表的相同的列名CustomersID。
A INNER JOIN B 和 B INNER JOIN A结果一样,区别是:前者先显示A表中的列,后者是先显示B表中的列。
内连接的另一种实现方式:FROM和 WHERE
SELECT *
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
问题:上面的示例中CustomerID重复输出,原因是INNER JOIN返回两个表在CustomerID相等的行,并完整地输出(SELECT *)。
解决:指定想要选择的列,通过在FROM和INNER JOIN的后面插入关键字AS从而指定表的别名,则在SELECT和ON都可以使用别名来指定表。
SELECT
C.CustomerID AS‘Cust ID’,
C.FirstName AS‘First Name’,
C.LastName AS‘Last Name’,
O.OrderID AS‘Order ID’,
O.Quantity AS‘Qty’,
O.PricePerItem AS‘Price’
FROM Customer AS C
INNER JOIN Order AS O
ON C.CustomerID =O.CustomerID
在Oracle里面不使用AS来指定表的别名,FROM TableName AliasTableName...
SELECT
C.CustomerID AS‘Cust ID’,
C.FirstName AS‘First Name’,
C.LastName AS‘Last Name’,
O.OrderID AS‘Order ID’,
O.Quantity AS‘Qty’,
O.PricePerItem AS‘Price’
FROM Customer C
INNER JOIN Order O
ON C.CustomerID =O.CustomerID