LEFT OUTER JOIN vs SUBSELECT in MySQL

Question

I have a say table table1 that has 3 columns column1, column2 and column3 .

column1 and column2 are FOREIGN KEY with two other tables. However, the data in column3 refers to n number of tables.

For example, let's take a look at Facebook. To display actions, it can maintain a table that can have user1 photoliked photo1 or user1 statusliked status1 . Therefore, in this case, column3 cannot be a FOREIGN KEY with a specific table.

Now there are two ways to get real data -

The first way is

 SELECT user_id, verb_id, CASE WHEN verb_id = photoliked THEN (SELECT photo_name FROM photos WHERE photo_id = column3) -- getting the desired data from the third column WHEN verb_id = statusliked THEN (SELECT status FROM statustable WHERE status_id = column3) ELSE '' END AS performedon FROM table1 JOIN table2 ON user_id = user_id -- joining the first column JOIN table3 ON verb_id = verb_id -- joining the second column 

The second way is

 SELECT user_id, verb_id, CASE WHEN verb_id = photoliked THEN p.photo_name WHEN verb_id = statusliked THEN s.status ELSE '' END AS performedon FROM table1 JOIN table2 ON user_id = user_id -- joining the first column JOIN table3 ON verb_id = verb_id -- joining the second column LEFT JOIN photos p ON p.photo_id = column3 -- joining the column3 with specific table LEFT JOIN statustable s ON s.status_id = column3 

Question

Which of the two ways is better to get the data? and which of the two queries is cheaper?

Answer 1

The second will be faster, and the reason for the first is what are called correlated subqueries. Subqueries are correlated with records from the main query. Therefore, subqueries must be executed once for each corresponding record in the main query. In your case, it cannot run the subquery until it determines the value of verb_id in the main request. These are many requests to run.

The EXPLAIN of the first request should indicate this problem. This is usually a red flag when you see this in EXPLAIN.

Answer 2

I think the JOIN will be faster since it runs once for the request, and I will also try to filter verb_id in the JOIN

 SELECT user_id, verb_id, COALESCE(p.photo_name, s.status) AS performedon FROM table1 JOIN table2 ON user_id = user_id -- joining the first column JOIN table3 ON verb_id = verb_id -- joining the second column LEFT JOIN photos p ON verb_id = 'photoliked' AND p.photo_id = column3 -- joining the column3 with specific table LEFT JOIN statustable s ON verb_id = 'statusliked' AND s.status_id = column3 

Answer 3

You can use this approach:

 SELECT t.user_id, t.verb_id, p.photo_name AS performedon FROM table1 AS t JOIN table2 AS t2 ON t2.user_id = t.user_id JOIN table3 AS t3 ON t3.verb_id = t.verb_id JOIN photos AS p ON p.photo_id = t.column3 AND t.verb_id = 'photoliked' UNION ALL SELECT t.user_id, t.verb_id, s.status FROM table1 AS t JOIN table2 AS t2 ON t2.user_id = t.user_id JOIN table3 AS t3 ON t3.verb_id = t.verb_id JOIN statustable AS s ON s.status_id = t.column3 AND t.verb_id = 'statusliked' ;