1

I want to JOIN a base table with another 23 tables. But as I want to join one with another tables, I have to use LEFT JOIN. My query sample:

SELECT notif_dest_user.notif_id,
       notif_all.da,
       COALESCE(notif_trick_like.uft_id, notif_trick_comment.uft_id) AS uft_id,
       COALESCE(like_users.un, trickComment_users.un)                AS un,
       COALESCE(like_tricks.tr_n, trickComment_tricks.tr_n)          AS tr_n,
       trickComment_userForTricks.cm_tx,
       COALESCE(trickComment_users.av_ul,like_users.av_ul)                       AS av_ul
FROM notif_dest_user
       LEFT JOIN notif_all ON notif_all.notif_id = notif_dest_user.notif_id
       LEFT JOIN notif_trick_like ON notif_dest_user.notif_id = notif_trick_like.notif_id
       LEFT JOIN user_for_tricks like_userForTricks ON notif_trick_like.uft_id = like_userForTricks.uft_id
       LEFT JOIN tricks like_tricks ON like_userForTricks.tr_id = like_tricks.tr_id
       LEFT JOIN users like_users ON like_userForTricks.u_id = like_users.u_id
       LEFT JOIN notif_trick_comment ON notif_trick_comment.notif_id = notif_dest_user.notif_id
       LEFT JOIN user_for_tricks trickComment_userForTricks
         on trickComment_userForTricks.uft_id = notif_trick_comment.uft_id
       LEFT JOIN users trickComment_users ON trickComment_users.u_id = trickComment_userForTricks.u_id
       LEFT JOIN tricks trickComment_tricks ON trickComment_tricks.tr_id = trickComment_userForTricks.tr_id
where notif_dest_user.dest_u_id = 42
limit 10;

The result of that query is like this:

+----------+------------+---------+-------------+-------------+-------------+------------------------------------+
| notif_id | da         | uft_id  | un          | tr_n        | cm_tx       | av_ul                              |
+----------+------------+---------+-------------+-------------+-------------+------------------------------------+
|       10 | 1542866579 | 1799477 | NULL        | NULL        | NULL        | NULL                               |
|       11 | 1542866579 | 1799494 | Some result | Some result | Some result | pics/avatars/u2764_1504729707_3092 |
|       12 | 1542866579 | 1799386 | Some result | Some result | Some result |                                    |
|       13 | 1542866579 | 1799477 | NULL        | NULL        | NULL        | NULL                               |
|       14 | 1542866579 | 1799494 | Some result | Some result | Some result | pics/avatars/u2764_1504729707_3092 |
|       15 | 1542866579 | 1799386 | Some result | Some result | Some result |                                    |
|       16 | 1542866579 | 1799477 | NULL        | NULL        | NULL        | NULL                               |
|       17 | 1542866579 | 1799494 | Some result | Some result | Some result | pics/avatars/u2764_1504729707_3092 |
|       18 | 1542866579 | 1799386 | Some result | Some result | Some result |                                    |
|       19 | 1542866579 | 1799477 | NULL        | NULL        | NULL        | NULL                               |
+----------+------------+---------+-------------+-------------+-------------+------------------------------------+

But I don't want to get the rows that were not exist (NULL) in right-side tables (other 23 tables). In other words, I want to show sum of rows of separately INNER JOIN between the base table and other tables. Something like this:

+----------+------------+---------+-------------+-------------+-------------+-------------------------------------+
| notif_id | da         | uft_id  | un          | tr_n        | cm_tx       | av_ul                               |
+----------+------------+---------+-------------+-------------+-------------+-------------------------------------+
|    10000 | 1542866579 | 1799408 | some result | some result | some result | pics/avatars/u63067_1517939524_6583 |
|    10001 | 1542866579 | 1799554 | some result | some result | some result | pics/avatars/u87332_1521754761_3673 |
|    10002 | 1542866579 | 1799422 | some result | some result | some result | pics/avatars/u63067_1517939524_6583 |
|    10003 | 1542866579 | 1799408 | some result | some result | some result | pics/avatars/u63067_1517939524_6583 |
|    10004 | 1542866579 | 1799554 | some result | some result | some result | pics/avatars/u87332_1521754761_3673 |
|    10005 | 1542866579 | 1799422 | some result | some result | some result | pics/avatars/u63067_1517939524_6583 |
|    10006 | 1542866579 | 1799408 | some result | some result | some result | pics/avatars/u63067_1517939524_6583 |
|    10007 | 1542866579 | 1799554 | some result | some result | some result | pics/avatars/u87332_1521754761_3673 |
|    10008 | 1542866579 | 1799422 | some result | some result | some result | pics/avatars/u63067_1517939524_6583 |
|    10009 | 1542866579 | 1799408 | some result | some result | some result | pics/avatars/u63067_1517939524_6583 |
+----------+------------+---------+-------------+-------------+-------------+-------------------------------------+
  • 1
    trickComment_userForTricks.cm_tx field in output list is a single-selected (coalesce is not used), so use this field as a mark. To remove NULL-contained records replace LEFT JOIN user_for_tricks trickComment_userForTricks with INER JOIN. – Akina Nov 25 '18 at 18:12
  • This cause remove all rows in left-side columns that doesn't have same id in user_for_tricks trickComment_userForTricks !! – Doctor Henry Nov 26 '18 at 5:22
  • 1
    This cause remove all rows in left-side columns that doesn't have same id in user_for_tricks trickComment_userForTricks ... and all those rows will have cm_tx IS NULL... and you want to remove them from your output... I do not see the contradiction. – Akina Nov 26 '18 at 6:29
  • No. I want to remove rows exists in left-side base table, but not exists at least in one of right-side tables. It's a few different than what you say – Doctor Henry Nov 26 '18 at 7:29
  • 1
    I want to remove rows exists in left-side base table, but not exists at least in one of right-side tables. If so you must use INNER join for all joins... but this task differs from initial task. It's a few different than what you say It's a few different than what you say in a question text. But it is your task, and you can do everything you want with it, it is. In any case - you use LEFT JOIN to add NULLs, and then you want to remove this NULLs added by LEFT JOIN... it's strange. – Akina Nov 26 '18 at 7:37
2

Two options:

1) If you want to filter out rows that have no match in the right table, skip the LEFT keyword and use the regular (inner) join.

2) If you want to filter out rows that have all expressions evaluated to NULL, use regular WHERE clause, like:

SELECT ...
FROM ...
WHERE COALESCE(notif_trick_like.uft_id, notif_trick_comment.uft_id) IS NOT NULL
OR COALESCE(like_users.un, trickComment_users.un) IS NOT NULL
OR COALESCE(like_tricks.tr_n, trickComment_tricks.tr_n) IS NOT NULL
OR trickComment_userForTricks.cm_tx IS NOT NULL
OR COALESCE(trickComment_users.av_ul,like_users.av_ul) IS NOT NULL
  • The second option can be used. But isn't there another option that don't need pass conditions in 'WHERE' and 'IS NOT NULL' ? I think this way is some risky. Because right-side tables might have NULL values inside. – Doctor Henry Nov 25 '18 at 9:48
  • Right-side table has at least one not-null field (the ID). You can filter on it. Actually it depends on what you want. It's not 100% clear from the question. – filiprem Nov 25 '18 at 9:58
  • Thanks. You're right. But the another problem is query response time. When I add this where clause, I take longer (about 2x) time to execute query. – Doctor Henry Nov 25 '18 at 10:04
  • @DoctorHenry do you have indexes on the join fields? It can help in some cases. If you need help on query tuning, post the query plan here. EXPLAIN FORMAT=JSON should be ok – filiprem Nov 25 '18 at 12:32
  • My tables' indices were defined well. you can see here: api.myjson.com/bins/a3xni – Doctor Henry Nov 25 '18 at 13:26
1

If you are trying to avoid the NOT NULL rows, this is the pattern:

SELECT ...
    FROM a
    LEFT JOIN b ON ...
    WHERE b.id IS NULL;

This extends to multiple LEFT JOINs and multiple tests AND'd together in the WHERE.

As already mentioned, avoiding the NULL rows, change LEFT JOIN to JOIN.

(A side note: The keywords INNER and OUTER are optional and ignored by MySQL.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.