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:
- 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);
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.
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,