CODEWITHSUNDEEP
mysqlsqljoin
Jakob
Jakob

Reputation: 4854

why does my full join query fail?

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?

Upvotes: 0

Views: 263

Answers (3)

gbn
gbn

Reputation: 432190

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

Upvotes: 0

Michael Berkowski
Michael Berkowski

Reputation: 270609

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

Upvotes: 2

Chris
Chris

Reputation: 1680

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.

Upvotes: 0

Related Questions