mysql - 完全外连接中的 SQL 错误 1064

Question

我得到了错误

"You have an error in your SQL syntax; check the manual thatcorresponds to your MySQL server version for the right syntax to usenear 'full outer join adm_student_academic a on a.rollno=p.usernamewhere ps.degree='B' at line 1"

我使用的查询如下:

SELECT p.username, p.firstname, a.matric_obtain_marks
FROM  adm_student_academic a,
      profile_student AS ps,
      PROFILE p FULL OUTER JOIN adm_student_academic a
      ON a.rollno=p.username
WHERE ps.degree='BS(CS)' AND ps.batch = 'Fall 2010'
ORDER BY p.username

下面的例子描述了我想要达到的结果:

假设你有两个表，每个表只有一列，数据如下:

A : 1 2 3 4
B : 3 4 5 6

我想要的结果如下:

a | 1 2 3 4 null

b | null null 3 4 6

Answer 1

MySQL不支持full outer join，但是可以用left outer join的并集来模拟:

SELECT a.*, b.* FROM TableA a
LEFT OUTER JOIN TableB b ON (...)
UNION
SELECT a.*, b.* FROM TableB b
LEFT OUTER JOIN TableA a ON (...)