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