在ORACLE9i之前，oracle语法基础是SQL/86标准，9i及之后的版本中支持SQL/92标准。
基表信息：products、purchases和product_types
SQL> select * from products; PRODUCT_ID PRODUCT_TYPE_ID NAME DESCRIPTION PRICE
---------- --------------- ------------------------------ -------------------------------------------------- ----------
Modern Science A description of modern science 19.95
Chemistry Introduction to Chemistry
Supernova A star explodes 25.99
Tank War Action movie about a future war 13.95
Z Files Series on mysterious activities 49.99
: The Return Aliens return 14.95
Space Force Adventures of heroes 13.49
From Another Planet Alien from another planet lands on Earth 12.99
Classical Music The best classical music 10.99
Pop The best popular music 15.99
Creative Yell Debut album 14.99
My Front Line Their greatest hits 13.49 已选择12行。 SQL> select * from product_types; PRODUCT_TYPE_ID NAME
--------------- ----------
Book
Magazine
CD
DVD
Video
test 已选择6行。 SQL> SELECT * FROM PURCHASES; PRODUCT_ID CUSTOMER_ID QUANTITY
---------- ----------- ---------- SQL>
1、使用Oracle连接语法(SQL/86版)
SQL> select p.name,pt.name type from products p,product_types pt where p.product_type_id=pt.product_type_id order by p.name; NAME TYPE
------------------------------ ----------
: The Return Video
Chemistry Book
Classical Music CD
Creative Yell CD
From Another Planet DVD
Modern Science Book
Pop CD
Space Force DVD
Supernova Video
Tank War Video
Z Files Video 已选择11行。
2、使用SQL/92引入INNER JOIN和ON重写上面的查询
SQL> select p.name,pt.name type from products p inner join product_types pt on p.product_type_id=pt.product_type_id order by p.name; NAME TYPE
------------------------------ ----------
: The Return Video
Chemistry Book
Classical Music CD
Creative Yell CD
From Another Planet DVD
Modern Science Book
Pop CD
Space Force DVD
Supernova Video
Tank War Video
Z Files Video 已选择11行。
3、SQL/92使用using代替on进行简化连接：
SQL> select p.name,pt.name type from products p inner join product_types pt using (product_type_id); NAME TYPE
------------------------------ ----------
Modern Science Book
Chemistry Book
Z Files Video
Tank War Video
Supernova Video
: The Return Video
Space Force DVD
From Another Planet DVD
Classical Music CD
Pop CD
Creative Yell CD 已选择11行。 SQL>
------------USING子句不能使用表别名，否则报错：
SQL> select p.name,pt.name type,product_type_id from products p inner join product_types pt using (p.product_type_id);
select p.name,pt.name type,product_type_id from products p inner join product_types pt using (p.product_type_id)
*
第 行出现错误:
ORA-: 此处只允许简单的列名
4、同上，如果希望在查看product_type_id的值，则在SELECT子句中只能指定该列名，不能再在该列名前加表的别名
SQL> select p.name,pt.name type,product_type_id from products p inner join product_types pt using (product_type_id); NAME TYPE PRODUCT_TYPE_ID
------------------------------ ---------- ---------------
Modern Science Book
Chemistry Book
Z Files Video
Tank War Video
Supernova Video
: The Return Video
Space Force DVD
From Another Planet DVD
Classical Music CD
Pop CD
Creative Yell CD 已选择11行。 SQL> select p.name,pt.name type,pt.product_type_id from products p inner join product_types pt using (product_type_id);
select p.name,pt.name type,pt.product_type_id from products p inner join product_types pt using (product_type_id)
*
第 行出现错误:
ORA-: USING 子句的列部分不能有限定词 SQL>
5、使用SQL/92执行多表内连接
SQL/:
SQL> select c.first_name,c.last_name,p.name as product,pt.name as type from customers c,purchases pr,products p,product_types pt
where c.customer_id=pr.customer_id and p.product_id=pr.product_id and p.product_type_id=pt.product_type_id order by p.name; FIRST_NAME LAST_NAME PRODUCT TYPE
---------- ---------- ------------------------------ ----------
Cynthis Green Chemistry Book
Steve White Chemistry Book
Cynthis Green Modern Science Book
Steve White Modern Science Book
Steve White Supernova Video SQL/:
SQL> select c.first_name,c.last_name,p.name as product,pt.name as type from customers c inner join purchases pr using(customer_id)
inner join products p using (product_id) inner join product_types pt using (product_type_id) order by p.name; FIRST_NAME LAST_NAME PRODUCT TYPE
---------- ---------- ------------------------------ ----------
Cynthis Green Chemistry Book
Steve White Chemistry Book
Cynthis Green Modern Science Book
Steve White Modern Science Book
Steve White Supernova Video SQL>
6、使用SQL/92执行多列内连接
SELECT……
FROM TABLE1 INNER JOIN TABLE2
ON TABLE1.COLUMN1 = TABLE2.COLUMN1
AND TABLE1.COLUMN2=TABLE2.COLUMN2;
------------使用using进一步简化
SELECT ……
FROM TABLE1 INNER JOIN TABLE2
USING (COLUMN1,COLUMN2);
7、Oracle特有语法(+)执行外连接，而SQL/92标准使用left/right/full outer join执行外连接
不能使用(+)执行全外连接
SQL> select p.name,pt.name from products p, product_types pt
where p.product_type_id=pt.product_type_id(+) order by p.name; NAME NAME
------------------------------ ----------
: The Return Video
Chemistry Book
Classical Music CD
Creative Yell CD
From Another Planet DVD
Modern Science Book
My Front Line
Pop CD
Space Force DVD
Supernova Video
Tank War Video
Z Files Video 已选择12行。
7.1--使用SQL/92标准left outer join重写上面的查询
SQL> select p.name,pt.name from products p left outer join product_types pt using (product_type_id) order by p.name; NAME NAME
------------------------------ ----------
: The Return Video
Chemistry Book
Classical Music CD
Creative Yell CD
From Another Planet DVD
Modern Science Book
My Front Line
Pop CD
Space Force DVD
Supernova Video
Tank War Video
Z Files Video 已选择12行。
7.2--全外连接（两个表中有空值的行全列出）
SQL> select p.name,pt.name from products p full outer join product_types pt using(product_type_id) order by p.name; NAME NAME
------------------------------ ----------
: The Return Video
Chemistry Book
Classical Music CD
Creative Yell CD
From Another Planet DVD
Modern Science Book
My Front Line
Pop CD
Space Force DVD
Supernova Video
Tank War Video
Z Files Video
Magazine
test 已选择14行。
8、使用SQL/92执行自连接
SQL> select * from employees; EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME TITLE SALARY
----------- ---------- ---------- ---------- -------------------- ----------
James Smith CEO
Ron Johnson Sales Manager
Fred Hobbs Salesperson
Susan Jones Salesperson SQL> select w.last_name || ' works for ' || m.last_name from employees w, employees m where w.manager_id=m.employee_id; W.LAST_NAME||'WORKSFOR'||M.LAST
-------------------------------
Johnson works for Smith
Jones works for Johnson
Hobbs works for Johnson SQL> select w.last_name || ' works for ' || m.last_name from employees w inner join employees m on w.manager_id=m.employee_id; W.LAST_NAME||'WORKSFOR'||M.LAST
-------------------------------
Johnson works for Smith
Jones works for Johnson
Hobbs works for Johnson
9、使用SQL/92执行自连接
select * from product_types,products; --SQL/82标准
select * from product_types cross join products;--SQL/92标准