mysql - 在第三张表上加入空值的表

Question

我正在研究电子词典，我有 2 个表：

第一个表：术语

+----+--------------+
| 编号 | 术语文本 |
+----+--------------+
| 1 | 你好 |
| 2 | 你好吗？|
| 3 | 你好 |
+----+--------------+

第二个表：字典

<pre>
+-----------+------------+
| term_id_1 | term_id_2  |
+-----------+------------+
| 1         | 3          | /* hello - hola
| 2         | NULL       | /* how are you? - NULL
+-----------+------------+
</pre>

我想这样显示：

<pre>


+--------------+-------------+
| term_text_1  | term_text_2 |
+--------------+-------------+
| hello        | hola        | 
| how are you? | NULL        | 
+--------------+-------------+

</pre>

我试图使用以下查询获取数据：

<pre>
SELECT t1.term_text_1, t2.term_text_2
FROM dictionary d, term t1, term t2
WHERE d.term_id_1 = t1.id
AND d.term_id_2 = t2.id
</pre>

但我只得到价值不为空的地方！

标签： mysqlsql

Answer 1

您可以使用 LEFT JOIN

SELECT t1.term_text as term_text_1, t2.term_text as term_text_2
FROM dictionary d
LEFT JOIN term t1 ON d.term_id_1 = t1.id
LEFT JOIN term t2 ON d.term_id_2 = t2.id

Demo