FULL OUTER JOIN syntax issue in MySQL (left union right)

Question

I am trying to do a FULL OUTER JOIN in MySQL and as we all know that is not possible with their syntax. So, like any normal tinkerer I am using:

(T1 LEFT OUTER JOIN T2) UNION (T1 RIGHT OUTER JOIN T2)

Now here's the catch! Each of the individual joins (left and right) work like a charm, but together they give an error (1064: syntax error) and I am prompted to check my MySQL Server manual for correct syntax.

Here's the code:

(SELECT 
    *
FROM
    result LEFT OUTER JOIN 
    ((SELECT * FROM SQA AS sqa JOIN SA AS sa USING (sa_oid) WHERE sqa.sq_oid = 1)
    AS tmp1) ON tmp1.sa_oid = result.re_as

WHERE
    result.re_p = 1 AND 
    result.re_s = 1 AND
    result.re_q = 1)

UNION

(SELECT 
    *
FROM
    result RIGHT OUTER JOIN 
    ((SELECT * FROM SQA AS sqa JOIN SA AS sa USING (sa_oid) WHERE sqa.sq_oid = 1)
    AS tmp2) ON tmp2.sa_oid = result.re_as

WHERE
    result.re_p = 1 AND 
    result.re_s = 1 AND
    result.re_q = 1)

Again, the two left and right joins work individually but seem to generate a syntax error if put together with the UNION operator. Any ideas? Are there name collisions? Or is a FULL OUTER JOIN not possible in this case?

Any feedback is very much appreciated! Also, please excuse the triviality of the question (if it is trivial).

EDIT:

"Error Code: 1064
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 'ON tmp2.sa_oid = result.re_as

    WHERE
        result.re_p = 1 AND 
        result.re' at line 20"

what's the full syntax error message, and where is mysql claiming it's occuring in your query? — Marc B, Jan 25 '13 at 17:20

Hogan · Accepted Answer · 2013-01-25T17:32:18.757

1

Try this:

(SELECT 
    *
FROM
    result LEFT OUTER JOIN 
    (SELECT * FROM surveyquestion_answers AS sqa JOIN surveyanswer AS sa USING (sa_oid) WHERE sqa.sq_oid = 1)
    AS tmp1 ON tmp1.sa_oid = result.re_answer

WHERE
    result.re_page = 1 AND 
    result.re_survey = 1 AND
    result.re_question = 1)

UNION

(SELECT 
    *
FROM
    result RIGHT OUTER JOIN 
    (SELECT * FROM surveyquestion_answers AS sqa JOIN surveyanswer AS sa USING (sa_oid) WHERE sqa.sq_oid = 1)
    AS tmp2 ON tmp2.sa_oid = result.re_answer

WHERE
    result.re_page = 1 AND 
    result.re_survey = 1 AND
    result.re_question = 1)

edited Jan 25 '13 at 17:32

answered Jan 25 '13 at 17:15

Hogan

69,564
10
76
117

1

union all isn't going to solve a syntax error. it just tells mysql to allow "duplicate" rows to show in the result. – Marc B Jan 25 '13 at 17:19
how should I know? do I look like the OP? – Marc B Jan 25 '13 at 17:23
The semicolon doesn't add much either as it is intended as a full query and not a script. I don't think it will make any difference. P.S. I just tested it... it doesn't. Thanks for your suggestions anyway! – Tibbers Jan 25 '13 at 17:30
Syntax error is gone, however, the FULL OUTER JOIN is incorrect (behaves like a normal left outer join). That may very well be due to my semantics, though. Thanks! I'll consider this problem solved ;-). – Tibbers Jan 25 '13 at 17:41

FULL OUTER JOIN syntax issue in MySQL (left union right)

1 Answers1