How filter Not NULL rows after multiple LEFT JOIN?

Question

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 |
+----------+------------+---------+-------------+-------------+-------------+-------------------------------------+

Answer 1

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

Answer 2

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.)