Why Does MySQL Report a Syntax Error on Full Outer Join

Why does MySQL report a syntax error on FULL OUTER JOIN?

There is no FULL OUTER JOIN in MySQL. See 7.2.12. Outer Join Simplification and 12.2.8.1. JOIN Syntax:

You can emulate FULL OUTER JOIN using
UNION (from MySQL 4.0.0 on):

with two tables t1, t2:

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id

with three tables t1, t2, t3:

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
LEFT JOIN t3 ON t2.id = t3.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
LEFT JOIN t3 ON t2.id = t3.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
RIGHT JOIN t3 ON t2.id = t3.id

SQL Full Join syntax error

MySQL doesn't support FULL OUTER JOIN

You can do this though.

SELECT * FROM t1 LEFT JOIN t2 ON t1.Name = t2.Name
UNION
SELECT * FROM t1 RIGHT JOIN t2 ON t1.Name = t2.Name
;

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

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)

Is there a reason MySQL doesn't support FULL OUTER JOINS?

MySQL lacks a lot of functionality that other databases have*. I think they have a pretty huge backlog of ideas and not enough developers to implement them all.

This feature was requested in 2006 and is still not implemented. I guess it has low priority because you can work around it by combining LEFT and RIGHT OUTER JOIN with a UNION ALL. Not pleasant, but it does the trick. Change this:

SELECT *
FROM table1
FULL OUTER JOIN table2
ON table1.table2_id = table2.id

to this:

SELECT *
FROM table1
LEFT JOIN table2
ON table1.table2_id = table2.id
UNION ALL
SELECT *
FROM table1
RIGHT JOIN table2
ON table1.table2_id = table2.id
WHERE table1.table2_id IS NULL

---

* To be fair to MySQL, they also have some features that many other databases don't have.

How to perform full outer joins on multiple tables

You don't want a FULL JOIN. You want a LEFT JOIN:

select s.name, sp.name 
from student s left outer join
     plays p
     on s.usn = p.usn left outer join
     sport sp
     on p.sport_id = sp.sport_id;

A left join keeps all rows in the first table and matching rows in the subsequent tables -- which is exactly what you want.

FULL JOIN is rarely needed. I write a lot of SQL and months go by without my using full join in any database.

In this example, a FULL JOIN would be used if you also wanted all sports that have no students. That is NULL values could appar in any column.

postgresql: join syntax error when joining 2 select statements

Please use below query,

SELECT ctgov.sponsors.name, ctgov.sponsors.nct_id, ctgov.sponsors.id, 
    ctgov.studies.phase, proj_cdek_standard_orgs.cdek_synonyms.id, 
    proj_cdek_standard_orgs.cdek_synonyms.name
FROM ctgov.sponsors, ctgov.studies, proj_cdek_standard_orgs.cdek_synonyms
WHERE ctgov.sponsors.nct_id=ctgov.studies.nct_id
and proj_cdek_standard_orgs.cdek_synonyms.name = ctgov.sponsors.name;

But the right way is to use traditional joins,

SELECT ctgov.sponsors.name, ctgov.sponsors.nct_id, ctgov.sponsors.id, 
    ctgov.studies.phase, proj_cdek_standard_orgs.cdek_synonyms.id, 
    proj_cdek_standard_orgs.cdek_synonyms.name
FROM ctgov.sponsors
INNER JOIN ctgov.studies
ON (ctgov.sponsors.nct_id=ctgov.studies.nct_id)
INNER  JOIN proj_cdek_standard_orgs.cdek_synonyms
ON (proj_cdek_standard_orgs.cdek_synonyms.name = ctgov.sponsors.name);

You can change it to LEFT or FULL OUTER JOIN according to your requirement.

Need Full Outer Join without having Cross Join

You want a full join, but with row_number() to identify the matches:

select t1.month month_tab1, t1.id id_tab1, t2.month month_tab2, t2.id id_tab2
from (
    select t.*, row_number() over(partition by month order by id) rn from table1 t
) t1 
full join (
    select t.*, row_number() over(partition by month order by id) rn from table2 t) t2       
on t2.month = t1.month and t2.rn = t1.rn

---

Related Topics