Oracle支持
-- ORACLE
CREATE TABLE T1(
T1_A INTEGER,
T1_B INTEGER
);
CREATE TABLE T2(
T2_A INTEGER,
T2_B INTEGER
);
INSERT INTO T1 VALUES(1,0);
INSERT INTO T1 VALUES(2,0);
INSERT INTO T2 VALUES(1,0);
INSERT INTO T2 VALUES(3,0);
COMMIT ;
SELECT * FROM T1
FULL JOIN T2 ON T1.T1_A=T2.T2_A ;
-- 同
SELECT * FROM T1
FULL OUTER JOIN T2 ON T1.T1_A=T2.T2_A ;
执行结果
MySQL不支持
-- MYSQL
CREATE TABLE T1(
T1_A INTEGER,
T1_B INTEGER
);
CREATE TABLE T2(
T2_A INTEGER,
T2_B INTEGER
);
INSERT INTO T1 VALUES(1,0);
INSERT INTO T1 VALUES(2,0);
INSERT INTO T2 VALUES(1,0);
INSERT INTO T2 VALUES(3,0);
-- 报错
SELECT * FROM T1
FULL JOIN T2 ON T1.T1_A=T2.T2_A ;
-- 报错
SELECT * FROM T1
FULL OUTER JOIN T2 ON T1.T1_A=T2.T2_A ;
-- 替代写法
SELECT T1.*,T2.* FROM T1
LEFT JOIN T2 ON T1.T1_A=T2.T2_A
UNION ALL
SELECT T1.*,T2.* FROM T2
LEFT JOIN T1 ON T1.T1_A=T2.T2_A
WHERE T1.T1_A IS NULL ;
替代写法的执行结果