How to apply CASE in left join table

Question

I have one query for MySQL, and need to left join twice. There are four tables A, B, C, D, and two possibilities, if A.label is null then,

select ... from A
left join B on B.name=A.name
left join D on B.bid=D.id;

otherwise, if A.label is not null, I have

select ... from A
left join C on C.name=A.name
left join D on C.cid=D.id;

If I want to merge these two queries into one via CASE condition, what should I do?

or better yet... create a stored procedure and pass A.label as a parameter to determine which query to use? — Tiny Haitian, Mar 19 '14 at 17:50
I have not tried that yet, is the stored procedure faster for big data query? — lanyou, Mar 19 '14 at 18:04
It's always up for debate, but [click here](http://stackoverflow.com/questions/8559443/why-execute-stored-procedures-is-faster-than-sql-query-from-a-script) to see one way look at it. My suggestion for the stored procedure was purely from the angle of keeping your query from getting unnecessarily massive and keeping the readability simple. It would essentially let you say, "if @parameter = A, then run this query, else run that query." — Tiny Haitian, Mar 19 '14 at 18:50

score 1 · Answer 1 · answered Mar 19 '14 at 17:33

1

Not entirely sure I follow what you're after, but you can add to your JOIN criteria and then use COALESCE():

select COALESCE(b.name,c.name) Name
    ....
from A
left join B 
   on B.name=A.name
   AND A.label IS NULL
left join D 
   on B.bid=D.id
left join C 
   on C.name=A.name
   AND A.label IS NOT NULL
left join E
   on C.cid=E.id

answered Mar 19 '14 at 17:33

Hart CO

34,064
6
48
63

This makes the assumption that when `A.label IS NULL` then `b.name` is not `NULL`. – Gordon Linoff Mar 19 '14 at 17:34

score 0 · Answer 2 · answered Mar 19 '14 at 17:33

0

Assuming that your queries don't product duplicate rows, you can do both joins in one query. Then determine which value to use by incorporating a case statement in the select clause:

select A.*,
       (case when a.label is null then b.value else c.value end) as value,
       . . . 
from A left join
     B
     on B.name = A.name left join
     C
     on C.name = A.name left join
     D
     on C.cid = D.id;

answered Mar 19 '14 at 17:33

Gordon Linoff

1,242,037
58
646
786

It is possible to have duplicate rows. And also all of tables are huge, in this case all of the tables are loaded into memory and it will be a problem – lanyou Mar 19 '14 at 17:40
@lanyou . . . If you are using `union all`, you are getting the same duplicate rows that this would produce. With indexes on `C(name)` and `B(name)`, I wouldn't be surprised if this were the best performing. – Gordon Linoff Mar 19 '14 at 19:25
But A can be only NULL or not NULL, so I think there won't be duplicate results – lanyou Mar 20 '14 at 09:19

Barmar · Answer 3 · 2014-03-19T17:45:27.313

0

Use a UNION:

select ... from A
left join B on B.name=A.name
left join D on B.bid=D.id;
where A.label IS NULL
UNION ALL
select ... from A
left join C on C.name=A.name
left join D on C.cid=D.id
WHERE A.label IS NOT NULL

edited Mar 19 '14 at 17:45

answered Mar 19 '14 at 17:37

Barmar

741,623
53
500
612

It's also my current solution, just want to find some other better way. And I use Union All instead of Union, cuz it will be faster for big data table query – lanyou Mar 19 '14 at 17:44
If you do it in one query I think it will be inefficient, because it will join with tables that aren't needed for that row. – Barmar Mar 19 '14 at 17:46

How to apply CASE in left join table

3 Answers3