MySQL - How to Join Different Tables Based on Condition (Switch Join - Select Tables on Condition)

Sometimes in a single query, it is required to join different tables based on a condition in one of the tables.

For example, you need to get all persons participating in a contest as individuals or as members of a team. Contest table points either to Team or Person table depending on the participant type:

Persons TeamPersons ContestParticipants
Id Team_Id Participant_Id
Name Person_Id Participant_Type

SQL:

  -- Persons who can participate as individuals or team members
  CREATE TABLE persons (id INT, name VARCHAR(30));
 
  -- Persons who participate as a team
  CREATE TABLE teamPersons (team_id INT, person_id INT);
 
  -- Type 'P' points to Person, type 'T' to teamPersons (team_id)
  CREATE TABLE contestParticipants (participant_id INT, participant_type CHAR);

Sample Data:

   -- There are 3 persons: Dan, Tom and Steve
   INSERT INTO persons VALUES (1, 'Dan'), (2, 'Tom'), (3, 'Steve');
 
   -- Dan and Tom are in Team 1 
   INSERT INTO teamPersons VALUES (1, 1), (1, 2);
 
   -- Team 1 and Steve participate in contest
   INSERT INTO contestParticipants VALUES (1, 'T'), (3, 'P');

So how to get all persons participating in the contest in a single SQL query?

Approach 1 - UNION

You can use UNION clause to combine the results from several queries into one:

   SELECT p.name
   FROM contestParticipants c, persons p
   WHERE participant_type = 'P' AND participant_id = p.id
   UNION ALL
   SELECT p.name
   FROM contestParticipants c, teamPersons tp, persons p
   WHERE participant_type = 'T' AND participant_id = tp.team_id AND tp.person_id = p.id;

The query returns:

name
Steve
Dan
Tom

Explanation:

  • The first SELECT query selects persons participating as individuals, and it directly joins ContestParticipants and Persons tables
  • The second SELECT query selects teams and defines its members. It joins ContestParticipant with TeamPersons and then Persons tables

Approach 2 - LEFT OUTER JOIN

It is possible to get the same result without UNION:

   SELECT IFNULL(p.name, p2.name)
   FROM contestParticipants c
     LEFT OUTER JOIN persons p ON (c.participant_id = p.id AND c.participant_type = 'P')
     LEFT OUTER JOIN teamPersons tp ON (c.participant_id = tp.team_id AND c.participant_type = 'T')
     LEFT OUTER JOIN persons p2 ON tp.person_id = p2.id;

The query returns:

name
Dan
Tom
Steve

Explanation:

Firstly you get names of individuals by joining ContestParticipant and Person tables, but you use LEFT OUTER JOIN so Team rows remains:

   SELECT *
   FROM contestParticipants c
     LEFT OUTER JOIN persons p ON (c.participant_id = p.id AND c.participant_type = 'P')

Result:

participant_id participant_type p.id p.name
1 T NULL NULL
3 P 3 Steve

Then we get ID of persons participating as team members:

   SELECT *
   FROM contestParticipants c
     LEFT OUTER JOIN persons p ON (c.participant_id = p.id AND c.participant_type = 'P')
     LEFT OUTER JOIN teamPersons tp ON (c.participant_id = tp.team_id AND c.participant_type = 'T')

Result:

participant_id participant_type p.id p.name tp.team_id tp.person_id
1 T NULL NULL 1 1
1 T NULL NULL 1 2
3 P 3 Steve NULL NULL

The we get person names participating as team members:

   SELECT *
   FROM contestParticipants c
     LEFT OUTER JOIN persons p ON (c.participant_id = p.id AND c.participant_type = 'P')
     LEFT OUTER JOIN teamPersons tp ON (c.participant_id = tp.team_id AND c.participant_type = 'T')
     LEFT OUTER JOIN persons p2 ON tp.person_id = p2.id;

Result:

participant_id participant_type p.id p.name tp.team_id tp.person_id p2.id p2.name
1 T NULL NULL 1 1 1 Dan
1 T NULL NULL 1 2 2 Tom
3 P 3 Steve NULL NULL NULL NULL

Finally you use IFNULL(p.name, p2.name) to select the person name as an individual or team member.

How To - Most Popular Queries and Expressions

Resources