刚从SQL SERVER转到Oracle的新人,可能会对select语句中的where条件的(+)感到奇怪,而且带有(+)的问题,使用google也找不相应的结果。特此解惑如下:
设两张表,
create table HEK_TEST_IN
(
PID INTEGER primary key,
NDATE DATE,
NNOTE VARCHAR2(50)
)
create table HEK_TEST_INDETAIL
(
PID INTEGER primary,
FID INTEGER,
NNAME VARCHAR2(50),
NQTY FLOAT,
NNOTE VARCHAR2(50)
)
alter table HEK_TEST_INDETAIL
add constraint FK_TEST_1 foreign key (FID)
references HEK_TEST_IN (PID);
alter table HEK_TEST_INDETAIL
disable constraint fk_test_1;
自然等值连接查询(也称内连接):
SQL标准写法:
select * from hek_test_in a inner join hek_test_indetail b on a.pid=b.fid
Oracle另类写法:
select * from hek_test_in a,hek_test_indetail b where a.pid=b.fid
左连接查询:
SQL标准写法:
select * from hek_test_in a left join hek_test_indetail b on a.pid=b.fid
Oracle另类写法:
select * from hek_test_in a,hek_test_indetail b where a.pid=b.fid(+)
右连接查询
SQL标准写法:
select * from hek_test_in a right join hek_test_indetail b on a.pid=b.fid
Oracle另类写法:
select * from hek_test_in a,hek_test_indetail b where a.pid(+)=b.fid
笛卡尔乘积一样。
SQL标准写法:
select * from hek_test_in a right join hek_test_indetail b
全连接查询也一样了。
SQL标准写法:
select * from hek_test_in a full join hek_test_indetail b on a.pid=b.fid