1021mysql 全外连接

mysql 不支持全外连接，那么使用union all进行替换

-- 其他数据库

SELECT * FROM aa a
FULL OUTER JOIN bb b 
ON a.col1=b.col1
AND a.col2=b.col2
AND a.col3=b.col3
AND a.col4=b.col4
AND a.col5=b.col5
AND a.col6=b.col6
WHERE a.col1 IS NULL OR b.col1 IS NULL

 

-- mysql数据库替换方法
SELECT COUNT(col1) FROM
(
SELECT a.* FROM aa a
LEFT JOIN bb b
ON a.col1=b.col1
AND a.col2=b.col2
AND a.col3=b.col3
AND a.col4=b.col4
AND a.col5=b.col5
AND a.col6=b.col6 WHERE b.col1 IS NULL
UNION ALL
SELECT c.* FROM aa a
RIGHT JOIN bb c
ON a.col1=c.col1
AND a.col2=c.col2
AND a.col3=c.col3
AND a.col4=c.col4
AND a.col5=c.col5
AND a.col6=c.col6
WHERE a.col1 IS NULL
) AS t

 

转载于:https://www.cnblogs.com/qcfeng/p/5985106.html