SQL自然联接（NATURAL JOIN）

来源：互联网发布：grid.base.js 编辑：程序博客网时间：2024/04/30 09:16

SQL> create table lm_t10(id number,name number);

Table created.

SQL> create table lm_t11(id number,name number);

Table created.

SQL> insert into lm_t10 values(1,2);

1 row created.

SQL> insert into lm_t10 values(1,3);

1 row created.

SQL> insert into lm_t11 values(1,2);

1 row created.

SQL> insert into lm_t11 values(1,55);

1 row created.

SQL> commit;

Commit complete.

1.纯自然联接 NATURAL JOIN

SQL> select * from lm_t10 natural join lm_t11;

NAME ID DI1
---------- ---------- ----------
2 1 1

SQL> select l.name   from lm_t10 join lm_t11 l using(name) where name = 3; --不要添加表别名或者表名
select l.name   from lm_t10 join lm_t11 l using(name) where name = 3
       *
ERROR at line 1:
ORA-25154: column part of USING clause cannot have qualifier

2.使用USING

SQL> select * from lm_t10 join lm_t11 using(id) where id = 1;

        ID       NAME       NAME
---------- ---------- ----------
         1          3          2
         1          2          2
         1          3         55
         1          2         55

3.使用ON

SQL> select * from lm_t10 join lm_t11 on(lm_t10.id = lm_t11.di1 and lm_t10.name=lm_t11.name) where id=1;

ID NAME DI1 NAME
---------- ---------- ---------- ----------
1 2 1 2

0 0