1,用来连接两个表的条件称为连接条件,连接条件中的连接字段类型必须是可比的,但不必是相同的;连接条件可分为两类:等连接和不等连接。
产品名可从products表中得到,类型名称可从product_types表中得到,两个表通过外键列product_type_id彼此关联。
eg:
SQL> select * fromproducts;
PRODUCT_ID PRODUCT_TYPE_ID NAME DESCRIPTION PRICE ---------- ----------------------------------------------------------------------------------------------- ---------- 1 1 Modern Science A description of modern science 19.95 2 1 Chemistry Introduction to Chemistry 30 3 2 Supernova A star explodes 25.99 4 2 Tank War Actionmovie about a future war 13.95 5 2 Z Files Series on mysterious activities 49.99 6 2 2412: The Return Aliensreturn 14.95 7 3 Space Force 9 Adventures of heroes 13.49 8 3 From Another Planet Alien from another planetlands on Earth 12.99 9 4 Classical Music The best classical music 10.99 10 4 Pop 3 The best popular music 15.99 11 4 CreativeYell Debut album 14.99 PRODUCT_ID PRODUCT_TYPE_ID NAME DESCRIPTION PRICE ---------- ----------------------------------------------------------------------------------------------- ---------- 12 1 Their greatest hits 13.49 已选择12行。
SQL> select * fromproduct_types;
PRODUCT_TYPE_ID NAME --------------- ---------- 1 Book 2 Video 3 DVD 4 CD 5 Magazine
SQL> select p.product_id, p.name,pt.name from products p, Product_types pt where p.product_type_id =pt.product_type_id;(在P的product_id,name和pt的name中匹配相关联的事项得出下表)
PRODUCT_ID NAME NAME ---------- ---------------------------------------- 1 ModernScience Book 2 Chemistry Book 3 Supernova Video 4 Tank War Video 5 Z Files Video 6 2412: TheReturn Video 7 SpaceForce 9 DVD 8 FromAnother Planet DVD 9 ClassicalMusic CD 10 Pop 3 CD 11 Creative Yell CD
已选择11行。
SQL> select p.product_id, p.name,pt.name from products p, Product_types pt where p.product_type_id =pt.product_type_id and p.product_id = 3;(pt.product_type_id and p.product_id =3即为外键列)(在P的product_id,name和 pt的name中匹配P中product_id= 3的条目)
PRODUCT_ID NAME NAME ---------- ---------------------------------------- 3 Supernova Video
SQL> select * from products p,Product_types pt where p.product_type_id =pt.product_type_id;
PRODUCT_ID PRODUCT_TYPE_ID NAME DESCRIPTION PRICEPRODUCT_TYPE_ID NAME ---------- ----------------------------------------------------------------------------------------------- ------------------------- ---------- 1 1 Modern Science A description of modern science 19.95 1 Book 2 1 Chemistry Introduction to Chemistry 30 1 Book 3 2 Supernova A star explodes 25.99 2 Video 4 2 Tank War Actionmovie about a future war 13.95 2 Video 5 2 Z Files Series on mysterious activities 49.99 2 Video 6 2 2412: The Return Aliensreturn 14.95 2 Video 7 3 Space Force 9 Adventures of heroes 13.49 3 DVD 8 3 From Another Planet Alien from another planetlands on Earth 12.99 3 DVD 9 4 Classical Music The best classical music 10.99 4 CD 10 4 Pop 3 The best popular music 15.99 4 CD 11 4 CreativeYell Debut album 14.99 4 CD
已选择11行。
2,使用表别名eg:
SQL> select p.product_id,p.product_type_id, p.name, p.description, price, pt.name fromproducts p, Product_types pt where p.product_type_id =pt.product_type_id;
PRODUCT_ID PRODUCT_TYPE_ID NAME DESCRIPTION PRICE NAME ---------- ----------------------------------------------------------------------------------------------- -------------------- 1 1 Modern Science A description of modern science 19.95 Book 2 1 Chemistry Introduction to Chemistry 30 Book 3 2 Supernova A star explodes 25.99 Video 4 2 Tank War Actionmovie about a future war 13.95 Video 5 2 Z Files Series on mysterious activities 49.99Video 6 2 2412: The Return Aliensreturn 14.95Video 7 3 Space Force 9 Adventures of heroes 13.49 DVD 8 3 From Another Planet Alien from another planetlands on Earth 12.99 DVD 9 4 Classical Music The best classical music 10.99 CD 10 4 Pop 3 The best popular music 15.99 CD 11 4 CreativeYell Debut album 14.99 CD (price不带前缀因为两个表中只有一个表有price)已选择11行。
3,笛卡尔积
如果在多表查询中不指定连接条件,就会导致将一个表中的所有行都连接到另一个表中的所有行,这种情况称为笛卡尔积。返回的结果记录总数是两个表中记录数的乘积。eg:
SQL> select p.product_id,p.product_type_id, p.name, p.description, price, pt.name fromproducts p, Product_types pt;(数个表中相同的索引关键字的值进行合并顺序显示,且返回的记录结果总数时数个表的乘积)4,执行多于两个表的查询
eg:
SQL> select c.first_name || ' ' ||c.last_name, p.name, pt.name from product_types pt, customers c,products p, purchases pu where p.product_type_id =pt.product_type_id and c.customer_id = pu.customer_id andpu.product_id = p.product_id;
5,连接类型
左外连接:将左表中多余的项目也添加到结果记录集中。
eg:(左表12行 右表5行记录)
SQL>select * from products p left joinproduct_types p on p.product_type_id =pt.product_type_id;
(以左边的表为基准 与右边的表进行合并 若左表和右表没有匹配的项目该项目依旧出现在合并后的表格内 只不过没有右表的数据与之匹配 )
SQL>select * from products p right joinproduct_types p on p.product_type_id = pt.product_type_id;(以右表为基准与左边的表进行合并 若右表和左表没有匹配的项目 该项目依旧出现在合并后的表格内 只不过没有右表的数据与之匹配但是左表内没有与右表相匹配信息的条目则不出现在合并的表中)
右外连接:将右表中多余的项目也添加到结果记录集中。
自连接:同一个表连接到自身。
eg:
SQL>select *from employee e, employee mwhere m.employee_id = e.manager_id;