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,
CREATE TABLE unanswered_questions_title(
q_id INT UNSIGNED,
The query using JOINS:
IF(q.q_is_answered, a.q_title, u.q_title) as q_title
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);
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_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
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.)
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