20.Oracle数据库SQL开发之 执行多表选择
欢迎转载,转载请标明出处:http://blog.csdn.net/notbaron/article/details/49107225
连接可以用于连接任意多个表。
连接数=查询中使用的表的总数-1
例如:
SQL> select p.name,pt.name from productsp,product_types pt where p.product_type_id=pt.product_type_id order by p.name;
NAME NAME
------------------------------ ----------
2412: The Return Video
Chemistry Book
Classical Music CD
Creative Yell CD
From Another Planet DVD
Modern Science Book
Pop 3 CD
Space Force 9 DVD
Supernova Video
Tank War Video
Z Files Video
11 rows selected.
继续查询4个表,使用3个连接,查看
已经购买过产品的顾客(purchases)
顾客的姓名(customers)
顾客购买的产品名(products)
产品类型名(product_types)
SQL>select c.first_name,c.last_name,p.name as product,pt.name as type fromcustomers c,purchases pr,products p,product_types pt wherec.customer_id=pr.customer_id and p.product_id=pr.product_id andp.product_type_id=pt.product_type_id order by p.name;
FIRST_NAMELAST_NAME PRODUCT TYPE
---------- ---------------------------------------- ----------
John Brown Chemistry Book
Cynthia Green Chemistry Book
Steve White Chemistry Book
Gail Black Chemistry Book
John Brown Modern Science Book
Cynthia Green Modern Science Book
Steve White Modern Science Book
Gail Black Modern Science Book
Steve White Supernova Video
9 rows selected.
此处中的连接条件中都使用了等于操作符。