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 using JOINS:

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.is_answered = 1)
LEFT JOIN unanswered_questions_title u ON (q.q_id = u.q_id AND 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.

Now, using subquery:

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 first solution does not handle this situation well either.)

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,
Author: Ilan Hazan

Advertisements

1 Comment

  1. March 9, 2010 at 10:22 am

    […] Conditional Joins in MySQL (scvinodkumar.wordpress.com) […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: