oracleʹÓÃusing¹Ø¼ü×Ö

תÔØ×Ô£ºhttp://www.2cto.com/database/201503/384694.html

oracleʹÓÃusing¹Ø¼ü×Ö

sql/92±ê×¼¿ÉÒÔʹÓÃusing¹Ø¼ü×ÖÀ´¼ò»¯Á¬½Ó²éѯ£¬µ«ÊÇÖ»ÊÇÔÚ²éѯÂú×ãÏÂÃæÁ½¸öÌõ¼þʱ£¬²ÅÄÜʹ

ÓÃusing¹Ø¼ü×Ö½øÐмò»¯¡£
1.²éѯ±ØÐëÊǵÈÖµÁ¬½Ó¡£
2.µÈÖµÁ¬½ÓÖеÄÁбØÐë¾ßÓÐÏàͬµÄÃû³ÆºÍÊý¾ÝÀàÐÍ¡£
ÀýÈ磺ʹÓÃusing¹Ø¼ü×Ö£¬ÈçÏ£º

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

select emptno,ename,sal,deptno,dname from emp e inner join dept d using(deptno); SQL> select e.empno,e.ename,e.sal,deptno,d.dname from 2 emp e inner join dept d using(deptno); EMPNO ENAME SAL DEPTNO DNAME ---------- ---------- ---------- ---------- -------------- 7369 SMITH 800 20 RESEARCH 7499 ALLEN 1600 30 SALES 7521 WARD 1250 30 SALES 7566 JONES 2975 20 RESEARCH 7654 MARTIN 1250 30 SALES 7698 BLAKE 2850 30 SALES 7782 CLARK 2450 10 ACCOUNTING 7788 SCOTT 3000 20 RESEARCH 7839 KING 5000 10 ACCOUNTING 7844 TURNER 1500 30 SALES 7876 ADAMS 1100 20 RESEARCH 7900 JAMES 1800 30 SALES 7902 FORD 3000 20 RESEARCH 7934 MILLER 1300 10 ACCOUNTING 7935 XIAOXUE 5000 20 RESEARCH

ÒÑÑ¡Ôñ 15 ÐС£
ÈçÉÏÊöµÄ½á¹ûÓë×ÔÈ»Á¬½ÓµÄ½á¹ûÏàͬ¡£

ʹÓÃusing¹Ø¼ü×Ö¼ò»¯Á¬½Óʱ£¬ÐèҪעÒâÒÔϼ¸µã£º
1.ʹÓÃemp±íºÍdept±íÖеÄdeptnoÁнøÐÐÁ¬½Óʱ£¬ÔÚusing×Ó¾äºÍselect×Ó¾äÖУ¬¶¼²»ÄÜΪdeptnoÁÐÖ¸¶¨±íÃû»ò±í±ð Ãû¡£
2.Èç¹ûÔÚÁ¬½Ó²éѯʱʹÓÃÁËÁ½¸ö±íÖÐÏàͬµÄ¶à¸öÁУ¬ÄÇô¾Í¿ÉÒÔÔÚusing×Ó¾äÖÐÖ¸¶¨¶à¸öÁÐÃû£¬ÐÎʽÈçÏ£º

?

1 2	select... from table1 inner join table2 using(column1,column2)

ÉÏÊöµÄÓï¾äÏ൱ÓÚÏÂÃæµÄÓï¾ä£º

?

1 2 3

select... from table1 inner join table2 on table1.column1=table2.column2 and table1.column2=table2.column2;

Èç¹û¶Ô¶à¸ö±í½øÐмìË÷£¬¾Í±ØÐë¶à´ÎʹÓÃusing¹Ø¼ü×Ö½øÐÐÖ¸¶¨£¬ÐÎʽÈçÏ£º

?

1 2 3

select... from table1 inner join table2 using(column1) inner join table3 using(column2);

ÉÏÊöµÄÓï¾äÏ൱ÓÚÏÂÃæµÄÓï¾ä£º

?

1 2 3

select... from table1,table2,table3 where table1.column1=table2.column1 and table2.column2=table3.table2;