SQL error 1064 in full outer join

Question

I get the error

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

The query I used is as follows:

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

The result I want to achieve is depicted in the following example:

Suppose you have two Tables, with a single column each, and data as follows:

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

and I want the result as follows:

a | 1 2 3 4 null

b | null null 3 4 6

Answer 1

MySQL doesn't support full outer join, but it can be simulated with a union of left outer joins:

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 (...)