mysql - 如何检查 JOIN 的结果是否为 NULL？

Question

这是我的表结构:

-- categories
+----+-------------+
| id |     name    |
+----+-------------+
| 1  | political   |
| 2  | cultural    |
| 3  | social      |
| 4  | historical  |
+----+-------------+

-- tags
+----+-------------+-----------+-------------+-----------------+
| id |     name    | parent_id | category_id |  total_used_num |
+----+-------------+-----------+-------------+-----------------+
| 1  | president   | NULL      | 1           | 43              |
| 2  | society     | NULL      | 3           | 345             |
| 3  | Trump       | 1         | 1           | 5               |
| 4  | book        | 5         | 2           | 5473            |
| 5  | library     | NULL      | 2           | 433             |
+----+-------------+-----------+-------------+-----------------+

这是我的查询:

SELECT t1.name, 
       CONCAT(t2.name, ',', cat.name) t_p 
FROM   tags t1 
       LEFT JOIN tags t2 
              ON t1.parent_id = t2.id 
       INNER JOIN categories cat 
               ON t1.category_id = cat.id 
ORDER  BY total_used_num DESC, 
          t1.id 

/* output
+----+-------------+--------------------------+
| 1  | president   | NULL                     |
| 2  | society     | NULL                     |
| 3  | Trump       | president,political      |
| 4  | book        | library,cultural         |
| 5  | library     | NULL                     |
+----+-------------+--------------------------+

看到了吗？ CONCAT 与 NULL 的结果是 NULL。无论如何，我怎样才能避免这种情况？我还想让那个逗号 , 成为条件。如果两个字段都不是 NULL，那么逗号应该在那里。这是预期结果:

+----+-------------+--------------------------+
| 1  | president   | political                |
| 2  | society     | social                   |
| 3  | Trump       | president,political      |
| 4  | book        | library,cultural         |
| 5  | library     | cultural                 |
+----+-------------+--------------------------+

我该怎么做？

Answer 1

只需使用concat_ws():

SELECT t1.name, 
       CONCAT_WS(',', t2.name, cat.name) as t_p 
FROM tags t1 LEFT JOIN
     tags t2 
     ON t1.parent_id = t2.id INNER JOIN
     categories cat 
     ON t1.category_id = cat.id 
ORDER BY total_used_num DESC, t1.id ;