查看下列两个语句查询结果有啥区别:
select * from t a,t b where a.age=b.age;
select * from t a where a.age =b.age;
==================================
下面进行测试
create table COMPUTE_BASE
(
COMPUTE_ID NUMBER not null,
SEQ NUMBER(3) not null,
FORMULA VARCHAR2(1),
ARGUMENT NUMBER
);
SQL> insert into compute_base values(1,1,'+',10);
1 row inserted
SQL> insert into compute_base values(1,2,'*',5);
1 row inserted
SQL> insert into compute_base values(1,3,'-',2);
1 row inserted
SQL> insert into compute_base values(2,1,'+',11);
1 row inserted
SQL> select * from COMPUTE_BASE;
COMPUTE_ID SEQ FORMULA ARGUMENT
---------- ---- ------- ----------
1 1 + 10
1 2 * 5
1 3 - 2
2 1 + 11
SQL> select * from COMPUTE_BASE a where a.compute_id=a.compute_id;
COMPUTE_ID SEQ FORMULA ARGUMENT
---------- ---- ------- ----------
1 1 + 10
1 2 * 5
1 3 - 2
2 1 + 11
SQL> select * from COMPUTE_BASE a ,compute_base b where a.compute_id=b.compute_id;
COMPUTE_ID SEQ FORMULA ARGUMENT COMPUTE_ID SEQ FORMULA ARGUMENT
---------- ---- ------- ---------- ---------- ---- ------- ----------
1 1 + 10 1 1 + 10
1 1 + 10 1 2 * 5
1 1 + 10 1 3 - 2
1 2 * 5 1 1 + 10
1 2 * 5 1 2 * 5
1 2 * 5 1 3 - 2
1 3 - 2 1 1 + 10
1 3 - 2 1 2 * 5
1 3 - 2 1 3 - 2
2 1 + 11 2 1 + 11
10 rows selected
比较可发现,当查询栏位有相同值时,会产生迪卡尔积;
SQL> update compute_base set compute_id=3 where formula='-';
1 row updated
SQL> update compute_base set compute_id=4 where formula='*';
1 row updated
SQL> commit;
Commit complete
SQL> select * from COMPUTE_BASE a where a.compute_id=a.compute_id;
COMPUTE_ID SEQ FORMULA ARGUMENT
---------- ---- ------- ----------
1 1 + 10
4 2 * 5
3 3 - 2
2 1 + 11
SQL> select * from COMPUTE_BASE a ,compute_base b where a.compute_id=b.compute_id;
COMPUTE_ID SEQ FORMULA ARGUMENT COMPUTE_ID SEQ FORMULA ARGUMENT
---------- ---- ------- ---------- ---------- ---- ------- ----------
1 1 + 10 1 1 + 10
4 2 * 5 4 2 * 5
3 3 - 2 3 3 - 2
2 1 + 11 2 1 + 11
若栏位无相同记录,刚两者的结果集没有区别,仅是显示的栏位