Conditional Joins in MySQL

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:

  1. questions: a table consisting of question ids, timestamps, and whether or not the question has been answered.
  2. answered_questions_title: a table of answered question titles.
  3. 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

MySQL Quiz
10 Comments
  1. Ben says:

    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.)

  2. Andrei says:

    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.

  3. John 5th says:

    @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;

  4. Hazan Ilan says:

    John,
    Your selects statements in the If Clause should return only one result and not three.

  5. Hazan Ilan says:

    Andrei,
    Use the left join that I mentioned in the post. It will allow you to include more than one column to the result.

  6. Diego says:

    @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.

  7. Stefan says:

    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

  8. Hazan Ilan says:

    Hi Stefan,
    I have fixed it.
    Many thanks

  9. Rajitha says:

    Nice !!! Helpful!!!

  10. Mutebi Roy Aloysious says:

    This is cool, thanks for sharing

Leave a Reply