SQL note4

SELECT cust_id
-> FROM Orders
-> WHERE order_num IN (SELECT order_num
-> FROM OrderItems
-> WHERE prod_id = ‘RGAN01’);

对于能嵌套的子查询的数目没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询。

SELECT cust_name,cust_state,
-> (SELECT COUNT(*)
-> FROM Orders
-> WHERE Orders.cust_id = Customers.cust_id)AS orders
-> FROM Customers
-> ORDER BY cust_name;

=======================================================
SQL 最强大的功能之一就是能在数据查询的执行中联结（join）表。
联结是利用SQL的SELECT能执行的最重要的操作。

SELECT vend_name,prod_name,prod_price
-> FROM Vendors,Products
-> WHERE Vendors.vend_id = Products.vend_id;

在一条SELECT语句中联结几个表时，相应的关系是在运行中构造的。

SELECT vend_name,prod_name,prod_price
-> FROM Vendors INNER JOIN Products
-> ON Vendors.vend_id = Products.vend_id;

SELECT cust_name,cust_contact
-> FROM Customers,Orders,OrderItems
-> WHERE Customers.cust_id=Orders.cust_id
-> AND OrderItems.order_num=Orders.order_num
-> AND prod_id=’RGAN01’;

======================================================
表别名只在查询执行中使用，与列别名不一样，表别名不返回到客户端。

自联结
SELECT c1.cust_id,c1.cust_name,c1.cust_contact
-> FROM Customers AS c1,Customers AS c2
-> WHERE c1.cust_name=c2.cust_name
-> AND c2.cust_contact=’Jim Jones’;

自然联结

外联结

SELECT Customers.cust_id,Orders.order_num
-> FROM Customers RIGHT OUTER JOIN Orders
-> ON Customers.cust_id = Orders.cust_id;

使用聚集函数的联结
SELECT Customers.cust_id,
-> COUNT(Orders.order_num)AS num_ord
-> FROM Customers INNER JOIN Orders
-> ON Customers.cust_id = Orders.cust_id
-> GROUP BY Customers.cust_id;