MySQL query with multiple full joins

Question

I have a query which gets all the jobs from a database, some jobs don't have a languagepair and I need to get those too while still getting the languagepair information for jobs witch have a languagepair, I understand this is done with a full join but full joins do not exist in mySQL, I read about it and I need to do some sort of UNION.

If I get NULLS as source & target for jobs that do not have a languagepair it is good.

This is the query I have at the moment:

SELECT jobName, source.name AS source, target.name AS target FROM (
        (SELECT jobs.name AS jobName, lp.sourceId, lp.targetId FROM jobs **JOIN languagePairs** lp
            ON lp.id = jobs.languagePairId)
            UNION  
        (SELECT jobs.name AS jobName, lp.sourceId, lp.targetId FROM collectiveJobs JOIN jobs ON jobs.id = collectiveJobs.jobId
            **JOIN languagePairs lp** on jobs.languagePairId = lp.id
            WHERE collectiveJobs.freelancerId = 1)
        ) AS jobs **JOIN languages** source ON source.id = sourceId **JOIN languages** target ON target.id = targetId;

I think but I am not sure the full joins need to happen at the bold joins. There also needs to be some sort of checking for null (I think) in the query.

Off course I could do this programmatically but it would be nice to have 1 query for it.

DB schema:

create table languages
(
    id    int auto_increment primary key,
    name  varchar(255) not null
)

create table languagePairs
(
    id                  int auto_increment
        primary key,
    sourceId            int not null,
    targetId            int not null,
    constraint languagePair_sourceId_targetId_uindex
        unique (sourceId, targetId),
    constraint languagePair_language_id_fk_source
        foreign key (sourceId) references languages (id),
    constraint languagePair_language_id_fk_target
        foreign key (targetId) references languages (id)
)

create table jobs
(
    id                      int auto_increment
        primary key,
    name                    varchar(255)                         null,
    freelancerId            int                                  null,
    languagePairId          int                                  null,
    constraint jobs_freelancers_id_fk
        foreign key (freelancerId) references freelancers (id),
    constraint jobs_languagePairs_id_fk
        foreign key (languagePairId) references languagePairs (id)
)

create table collectiveJobs
(
    id           int auto_increment
        primary key,
    jobId        int      not null,
    freelancerId int      not null,
    constraint collectiveJobs_freelancerId_jobId_uindex
        unique (freelancerId, jobId),
    constraint collectiveJobs_freelancers_id_fk
        foreign key (freelancerId) references freelancers (id),
    constraint collectiveJobs_jobs_id_fk
        foreign key (jobId) references jobs (id)
)

create table freelancers
(
    id    int auto_increment primary key
)

Sample data:

INSERT INTO datamundi.jobs (id, name, freelancerId, languagePairId) VALUES (1, 'Job 1', 1, 1);
INSERT INTO datamundi.jobs (id, name, freelancerId, languagePairId) VALUES (2, 'Job 2', 1, null);

If I execute the query only Job 1 gets shown.

MySQL version on development machine: mysql Ver 8.0.19 for Linux on x86_64 (MySQL Community Server - GPL)

MySQL version on production server: mysql Ver 8.0.17 for Linux on x86_64 (MySQL Community Server - GPL)

All help is truly appreciated.

Answer 1

This works with all LEFT joins, I am sorry, I should have tried first.

SELECT jobName, source.name AS source, target.name AS target FROM (
        (SELECT jobs.name AS jobName, lp.sourceId, lp.targetId FROM jobs LEFT JOIN languagePairs lp
            ON jobs.languagePairId = lp.id)
            UNION  
        (SELECT jobs.name AS jobName, lp.sourceId, lp.targetId FROM collectiveJobs JOIN jobs ON jobs.id = collectiveJobs.jobId
            LEFT JOIN languagePairs lp on jobs.languagePairId = lp.id
            WHERE collectiveJobs.freelancerId = 1)
        ) AS jobs LEFT JOIN languages source ON source.id = sourceId LEFT JOIN languages target ON target.id = targetId;

Not sure why I taught I needed a FULL JOIN...

Answer 2

I can't really delve into your specific example, but the good news is you are using MySQL 8.x. The workaround for a FULL OUTER JOIN between two tables (a and b) in MySQL is:

select * from a left join b on <predicate>
union
select * from a right join b on <predicate>

Now if you need to join complex selects instead of simple tables, them CTEs come to your rescue. For example, if the left side were a comple SELECT you would do:

with s as ( <complex-select-here> )
select * from s left join b on <predicate>
union
select * from s right join b on <predicate>

If both are complex SELECTs then:

with s as ( <complex-select-here> ),
     t as ( <complex-select-here> )
select * from s left join t on <predicate>
union
select * from s right join t on <predicate>

No sweat.