How to do full outer joins in mysql?

Question

I am receiving the following error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'outer join on PERSON.idnum=ADVISOR.student full outer join DIGITAL on ADVISOR.st' at line 1

When trying to run the following code:

select fname, lname, rname, advisor_name, smaddr FROM PERSON FULL OUTER JOIN on PERSON.idnum=ADVISOR.student FULL OUTER JOIN DIGITAL on ADVISOR.student=DIGITAL.idnum ;

I tired using union but not sure if I am doing it right because I am still getting a syntax error.

select fname, lname , rname , advisor_name , smaddr from PERSON full outer join on PERSON.idnum=ADVISOR.student left outer join DIGITAL on ADVISOR.student=DIGITAL.idnum 
 union 
select fname, lname , rname , advisor_name , smaddr from PERSON full outer join on PERSON.idnum=ADVISOR.student right outer join DIGITAL on ADVISOR.student=DIGITAL.idnum;

This table needs to output fname, rname, lname, advisor name, and twitter account from the following tables and if a person does not have an advisor paired with them or something then it will return null:

PERSON
+-------+---------+----------+-------------------------+---------+----------+
| idnum | lname   | fname    | rname                   | private | linkblue |
+-------+---------+----------+-------------------------+---------+----------+
| 22222 | Clemens | Timothy  | clemens.timothy@uky.edu |       0 | tgcl258  |
| 40256 | South   | William  | south.william@uky.edu   |       1 | weso123  |
| 55555 | North   | Dan      | north.dan@uky.edu       |       0 | ddno453  |
| 56732 | Cox     | Courtney | NULL                    |       1 | cco546   |
| 68123 | Smith   | Terry    | smith.terry@uky.edu     |       1 | tlsm321  |
+-------+---------+----------+-------------------------+---------+----------+
ADVISOR
+---------+---------+---------------+---------------+-------+-------+
| student | advisor | student_name  | advisor_name  | sdate | edate |
+---------+---------+---------------+---------------+-------+-------+
|   40256 |   40256 | William South | Abby Tanner   | NULL  | NULL  |
|   68123 |   68123 | Terry Smith   | Nicole Taylor | NULL  | NULL  |
+---------+---------+---------------+---------------+-------+-------+
DIGITAL
+-------+----------+------------------------------+
| idnum | smtype   | smaddr                       |
+-------+----------+------------------------------+
| 22222 | facebook | facebook.com/clemons.timothy |
| 40256 | facebook | facebook.com/south.william   |
| 68123 | facebook | facebook.com/smith.terry     |
| 22222 | twitter  | twitter.com/clemons.timothy  |
| 40256 | twitter  | twitter.com/south.william    |
| 68123 | twitter  | twitter.com/smith.terry      |
+-------+----------+------------------------------+

Why do you think you need to do a `full outer join`? It is almost never necessary. You should provide sample data and desired results. — Gordon Linoff, Feb 22 '20 at 18:57

Gordon Linoff · Answer 1 · 2020-02-22T22:18:37.223

I am answering because Omari's answer is about the worst possible way to implement something resembling a full join:

It unnecessarily duplicates data.
It then incurs extra overhead for removing the duplicates.
It doesn't even match full join because of the duplicate elimination and handling of null values.

All that said, you describe this:

This table needs to output fname, rname, lname, advisor name, and twitter account from the following tables and if a person does not have an advisor paired with them or something then it will return null

This description is a left join, not a full join:

select p.fname, p.lname , p.rname , a.advisor_name, d.smaddr
from person p left join
     advisor a
     on p.idnum = a.student left join
     digital on
     on p.idnum = d.idnum ;

It is very hard to imagine how advisor.student, advisor.advisor or digital.idnum could NOT refer to a valid person. If they did, then a full join might be appropriate.

score 0 · Accepted Answer · answered Feb 22 '20 at 19:06

you have not mentioned your table that you want to join to in your first join

   SELECT
        fname,
        lname ,
        rname ,
        advisor_name ,
        smaddr
    FROM
        PERSON
    FULL OUTER JOIN ON ADVISOR 
        PERSON.idnum = ADVISOR.student
    FULL OUTER JOIN DIGITAL ON
        ADVISOR.student = DIGITAL.idnum ;

If the db does not support full join then do union

select
    fname,
    lname ,
    rname ,
    advisor_name ,
    smaddr
from
    PERSON
left join ADVISOR on
    PERSON.idnum = ADVISOR.student
union select
    fname,
    lname ,
    rname ,
    advisor_name ,
    smaddr
from
    PERSON
left join DIGITAL on
    ADVISOR.student = DIGITAL.idnum ;

How to do full outer joins in mysql?

2 Answers2