Conditional Joins in MySQL

Posted on February 7, 2010, 1:43 pm, by Hazan Ilan, under Cool MySQL Queries.

One way to do a “Conditional Join” in MySQL is by using a “LEFT JOIN”. Create a “LEFT JOIN” for each condition and combine the results into one column using an “IF” statement by the “SELECT” expression. Here’s an example:

Suppose you have three tables:

questions: a table consisting of question ids, timestamps, and whether or not the question has been answered.
answered_questions_title: a table of answered question titles.
unanswered_questions_title: a table of unanswered question titles.

(yeah, I know the example isn’t 100% realistic, but it’s only for demonstration purposes ;-))

Now, suppose you need to find the 50 latest arrival questions with their titles. We will need a conditional join that combines either answered or unanswered titles with the questions table.

 CREATE TABLE questions(
                q_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
                q_is_answered TINYINT(1) UNSIGNED,
                q_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
 
 CREATE TABLE answered_questions_title(
                q_id INT UNSIGNED,
                q_title VARCHAR(255)
);
 
 CREATE TABLE unanswered_questions_title(
                q_id INT UNSIGNED,
                q_title VARCHAR(255)
);

The query would be:

SELECT
       q.q_id,
       q.q_timestamp,
       IF(q.q_is_answered, a.q_title, u.q_title) as q_title
FROM
      questions q
      LEFT JOIN answered_questions_title a
              ON (q.q_id = a.q_id AND q.q_is_answered = 1)
      LEFT JOIN unanswered_questions_title u
              ON (q.q_id = u.q_id AND q.q_is_answered = 0);

Explanation:
By inserting the “is_answered” condition to the ON clause, we restrict the “LEFT JOIN” to this condition. The result contains the desired values with the matching condition, and null values elsewhere. The title is now spread between two columns: a.q_title, and u.q_title. We use the IF clause to combine them into one column, q_title.

Note:
Another possible way to do a “Conditional Join” using MySQL is with a “UNION” (a “UNION” is the result of two or more conditions results). However, using “Union” is bad for performance when you need only top results with an “ORDER BY” and “LIMIT”.

I’m always happy to receive your comments,
Ilan Hazan

10 Comments

Ben says:
February 25, 2010 at 3:20 am
Consider using subqueries:
SELECT
q_id,
q_timestamp,
IF(q_is_answered,
SELECT q_title FROM answered_questions WHERE q_id=q.q_id,
SELECT q_title FROM unanswered_questions WHERE q_id=q.q_id) as q_title
FROM
questions q
I believe this is clearer, shorter and will run faster.
(It also assumes that each of the subqueries will return a single value.
The original solution does not handle this situation well either.)
Andrei says:
March 7, 2010 at 5:31 pm
This case is not a solution for followed query:
SELECT journalists_replay.id AS replay_id, journalists_replay.date AS replay_date, journalists_replay.user_id AS user_id, journalists_replay.user_type AS user_type, journalists_replay.text AS replay_text, IF(journalists_replay.user_type=’journalists’, SELECT(CONCAT_WS(‘ ‘, TRIM(journalists.name), TRIM(journalists.surname)) as user_name, journalists.photo as user_photo, journalists.posts as user_posts WHERE journalists_replay.user_id = jounalists.id, SELECT(CONCAT_WS(‘ ‘, TRIM(prs.name), TRIM(prs.surname)) as user_name, prs.photo as user_photo, prs.posts as user_posts) FROM journalists_replay WHERE journalists_replay.post_id = ’69’ AND journalists_replay.enabled ORDER BY journalists_replay.date DESC;
I can’t find a solution for this.
Best regards.
John 5th says:
January 6, 2011 at 2:06 pm
@Andrei
I have reformated you query but I guess it’s not correct at all:
SELECT
journalists_replay.id AS replay_id,
journalists_replay.date AS replay_date,
journalists_replay.user_id AS user_id,
journalists_replay.user_type AS user_type,
journalists_replay.text AS replay_text,
IF (
journalists_replay.user_type = ’journalists’,
SELECT(
CONCAT_WS(‘ ‘, TRIM(journalists.name), TRIM(journalists.surname)) as user_name,
journalists.photo as user_photo,
journalists.posts as user_posts
WHERE
journalists_replay.user_id = jounalists.id
,
SELECT (
CONCAT_WS(‘ ‘, TRIM(prs.name), TRIM(prs.surname)) as user_name,
prs.photo as user_photo,
prs.posts as user_posts )
FROM
journalists_replay
WHERE
journalists_replay.post_id = ’69′ AND journalists_replay.enabled
ORDER BY
journalists_replay.date DESC;
Hazan Ilan says:
January 10, 2011 at 4:22 pm
John,
Your selects statements in the If Clause should return only one result and not three.
Hazan Ilan says:
January 10, 2011 at 4:25 pm
Andrei,
Use the left join that I mentioned in the post. It will allow you to include more than one column to the result.
Diego says:
January 27, 2011 at 1:16 pm
@Andrei, if you do understand that query as you formatted it… my most sincere congratulations: you are a genious. Just in case you don’t know, linefeeds in sql queries do not affect the results. Not using linefeeds, on the other hand, does affect our braincells and our eyes.
Stefan says:
February 23, 2011 at 11:15 am
Hi, thanks for the example.
Just to remark: the SQL Query has typing errors…
Following the correct code:
SELECT
q.q_id,
q.q_timestamp,
IF(q.q_is_answered, a.q_title, u.q_title) AS q_titleunanswered_questions_title
FROM
questions q
LEFT JOIN answered_questions_title a
ON (q.q_id = a.q_id AND q.q_is_answered = 1)
LEFT JOIN unanswered_questions_title u
ON (q.q_id = u.q_id AND q.q_is_answered = 0);
Greetings
Stefan
Hazan Ilan says:
February 24, 2011 at 1:46 am
Hi Stefan,
I have fixed it.
Many thanks
Rajitha says:
November 9, 2011 at 12:42 pm
Nice !!! Helpful!!!
Mutebi Roy Aloysious says:
June 1, 2013 at 1:19 pm
This is cool, thanks for sharing