why does my full join query fail?

Question

I have this query:

SELECT *, default_insurance.company AS company
FROM (default_insurance)
JOIN default_gruppe_rel
     ON default_gruppe_rel.uid = default_insurance.uid
JOIN default_profiles
     ON default_insurance.uid = default_profiles.id
WHERE `kontakt` = '1'

Now I get a resultset from that, but If I want to get results where there are not join matches , and only change this query by changing the first join type to FULL like this

SELECT *, default_insurance.company AS company
FROM (default_insurance)
FULL JOIN default_gruppe_rel
    ON default_gruppe_rel.uid = default_insurance.uid
JOIN default_profiles
    ON default_insurance.uid = default_profiles.id
WHERE `kontakt` = '1'

I get an error saying: 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 JOIN default_gruppe_rel ON default_gruppe_rel.uid = default_insurance.uid J' at line 1

why is that? is the FULL JOIN unkown to my MYSQL version? Has it been deprecated?

There is no FULL OUTER JOIN in MySQL, a topic well-covered here. See: [MySQL Full Outer Join Syntax Error](http://stackoverflow.com/questions/2384298/mysql-full-outer-join-syntax-error) — Dan J, Sep 08 '11 at 20:08

score 2 · Accepted Answer · answered Sep 08 '11 at 20:07

2

MySQL does not support a FULL JOIN. See the MySQL docs for a few workarounds as provided in the user comments.

answered Sep 08 '11 at 20:07

Michael Berkowski

267,341
46
444
390

score 0 · Answer 2 · answered Sep 08 '11 at 20:07

0

FULL JOIN only works on some RDBMS...try LEFT OUTER JOIN/LEFT JOIN in order to get all records in the first table and all matches from the subsequent table.

You can also try a CROSS JOIN, which isn't really a join, but rather returns the cartesian product (ie, all possible combinations) of the 'joined' tables.

answered Sep 08 '11 at 20:07

Chris

1,401
4
17
28

1

-1, Also @Jakob, LEFT join is definitely not the same as full join! – Johan Sep 08 '11 at 20:11
@Johan - would you care to elaborate, or would you say that it should be fairly obvious from reading the manual :) ? – Jakob Sep 08 '11 at 20:39
A full join is almost the same as a (left join UNIONed with a right join). (The same in 99.99% of all cases) A left join is a very different thing, because it only gives half of the data. See here for more info: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html – Johan Sep 08 '11 at 20:42
"CROSS JOIN... isn't really a join, but rather returns the cartesian product" -- can you expand on what you mean please. e.g. I wouldn't expect 'Cartesian product' to mean much to non-mathematicians ;) – onedaywhen Sep 09 '11 at 09:58

score 0 · Answer 3 · answered Sep 08 '11 at 20:14

0

...LEFT JOIN...
UNION ALL
...RIGHT JOIN...

answered Sep 08 '11 at 20:14

gbn

422,506
82
585
676

Surely you mean `UNION` rather than `UNION ALL`. – onedaywhen Sep 09 '11 at 09:56
@onedaywhen: yep, unless the full outer join would be with NULL checks to trap non-matching on either side – gbn Sep 09 '11 at 10:06

why does my full join query fail?

3 Answers3