Full Outer Join in mysql with null values to the right If empty

Question

I have a criteria where I need to make full outer join in MySQL. Here are my conditions:

Condition 1) Consider Table A and I need to join with Table B If rows are present in both tables.

Condition 2) Consider Table A and I need to join with Table B with Null values If the rows are absent in Table B.

I checked a article of Full outer join here link, what should I use in this scenario. Please suggest sample query.

Take a look at left join. You want everything from table A and matching from table B. `A left join B` takes everything from A. If a row in A matches B then it takes that corresponding row from B. Otherwise, the "B part" of the resulting table is all null. This is equivalent to `B right join A` — mattyx17, May 22 '20 at 18:25
from the description in your question it is totally unclear WHY you think you need a Full Outer Join. Maybe you should provide some sample data, with desired output, about what you are trying to do ? — Luuk, May 22 '20 at 18:26
I have a master table Table A and Transaction Table table B.When user uploads data master table gets filled and transaction Table is empty.Eventually transaction table gets loaded.For every new upload the master table needs to be truncated and matched with transaction table.This is my requirement. — Mr Ajay, May 22 '20 at 18:32

score 0 · Answer 1 · answered May 22 '20 at 18:27

0

You are describing a LEFT JOIN, not a FULL JOIN:

select . . .
from a left join
     b
     on . . .

You are lucky, because MySQL does not support FULL JOIN. But then again, you don't need it.

answered May 22 '20 at 18:27

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 2 · answered May 22 '20 at 18:51

Got it to run using the below query

(SELECT 
        *
    FROM
        Sow t1
            LEFT JOIN
        child_sow t2 ON t1.employee_id = t2.employee_id
        and t1.project_id = t2.project_id
    WHERE
        t2.employee_id IS NULL
        and t1.project_id in (1,2,3))

Full Outer Join in mysql with null values to the right If empty

2 Answers2