关于左连接、右连接、内连接的问题
DROP TABLE IF EXISTS atab;
DROP TABLE IF EXISTS btab;
DROP TABLE IF EXISTS ctab;
DROP TABLE IF EXISTS dtab;
CREATE TABLE atab (
ID NUMBER(11) NOT NULL ,
BID NUMBER(11) DEFAULT NULL,
CID NUMBER(11) DEFAULT NULL,
DID NUMBER(11) DEFAULT NULL,
NAME VARCHAR2(20)DEFAULT NULL,
PRIMARY KEY (ID)
);
INSERT INTO atab(ID,BID,CID,DID,NAME) VALUES (1,1,1,1,'AAAAAAAA');
INSERT INTO atab(ID,BID,CID,DID,NAME) VALUES (2,1,3,2,'BBBBBBBB');
INSERT INTO atab(ID,BID,CID,DID,NAME) VALUES (3,2,1,2,'CCCCCCCC');
CREATE TABLE btab (
ID NUMBER(11) NOT NULL ,
CID NUMBER(11) DEFAULT NULL,
DID NUMBER(11) DEFAULT NULL,
NAME VARCHAR2(20) DEFAULT NULL,
PRIMARY KEY (ID)
);
INSERT INTO btab(ID,CID,DID,NAME) VALUES (1,1,1,'BTAB000000');
INSERT INTO btab(ID,CID,DID,NAME) VALUES (2,2,2,'BTAB111111');
CREATE TABLE ctab (
ID NUMBER(11) NOT NULL ,
NAME VARCHAR2(20) DEFAULT NULL,
PRIMARY KEY (ID)
);
INSERT INTO ctab(ID,NAME) VALUES (1,'CTAB0000');
INSERT INTO ctab(ID,NAME) VALUES (2,'CTAB1111');
INSERT INTO ctab(ID,NAME) VALUES (3,'CTAB2222');
CREATE TABLE dtab (
ID NUMBER(11) NOT NULL ,
TYPE CHAR(1) DEFAULT NULL,
NAME VARCHAR2(20) DEFAULT NULL,
PRIMARY KEY (ID)
);
INSERT INTO dtab(ID,TYPE,NAME) VALUES (1,'Y','DTAB11');
INSERT INTO dtab(ID,TYPE,NAME) VALUES (2,'N','DTAB22');
SELECT a.ID, b.ID, c.ID, d.ID, a.NAME, b.NAME, c.NAME, d.NAME, D.TYPE
FROM ATAB a, BTAB b, CTAB c, DTAB d
WHERE a.bid = b.id (+) AND a.did = d.id AND b.cid = c.id (+)
AND ((b.did = d.id AND d.id = 2) OR d.TYPE = 'Y' );
如何将上面这条 SQL 语句 转换成 left join 、inner join 的模式,