如何通過一個查詢選擇數據？

我有以下表格：

桌子xwrh9_betty_dictionary_ids

+----+----------+-------+-------------+---------------------+-------+-------+
| id | ordering | state | checked_out | checked_out_time    | lang1 | lang2 |
+----+----------+-------+-------------+---------------------+-------+-------+
|  3 |        0 |     1 |           0 | 0000-00-00 00:00:00 |    18 |    19 |
|  4 |        0 |     1 |           0 | 0000-00-00 00:00:00 |    18 |    20 |
|  5 |        0 |     1 |           0 | 0000-00-00 00:00:00 |    18 |    21 |
|  6 |        0 |     1 |           0 | 0000-00-00 00:00:00 |    18 |    22 |
+----+----------+-------+-------------+---------------------+-------+-------+

桌子xwrh9_betty_dictionary_words

+----+----------+-----------+-------+--------+----------------+
| id | language | word      | state | domein | characteristic |
+----+----------+-----------+-------+--------+----------------+
| 18 | en       | sad       |     1 | sad    | sad            |
| 19 | en       | sub       |     1 | am     | al             |
| 20 | hu       | sub2      |     1 | wed    | wed            |
| 21 | ro       | sub3      |     1 | sd     | sd             |
| 22 | en       | sds       |     1 | ss     | sd             |
| 23 | en       | alamkorta |     1 | sd     | sd             |
+----+----------+-----------+-------+--------+----------------+

我有以下查詢，是否可以優化？

SELECT b.id, b.language, b.word, b.state, b.domein, b.characteristic, c.lang1 
FROM xwrh9_betty_dictionary_ids as a  
  INNER JOIN ( SELECT lang1 FROM xwrh9_betty_dictionary_ids WHERE lang2=19) as c  
  LEFT JOIN xwrh9_betty_dictionary_words as b 
     ON ((a.lang2=b.id or b.id=c.lang1) AND b.id<>19)  
WHERE a.lang1=c.lang1 GROUP BY id;

輸出如下：

+------+----------+------+-------+--------+----------------+-------+
| id   | language | word | state | domein | characteristic | lang1 |
+------+----------+------+-------+--------+----------------+-------+
|   18 | en       | sad  |     1 | sad    | sad            |    18 |
|   20 | hu       | sub2 |     1 | wed    | wed            |    18 |
|   21 | ro       | sub3 |     1 | sd     | sd             |    18 |
|   22 | en       | sds  |     1 | ss     | sd             |    18 |
+------+----------+------+-------+--------+----------------+-------+

我不知道 mysql 語法，但似乎以下內容會給您相同的結果。

SELECT b.id, b.language, b.word, b.state, b.domein, b.characteristic, a.lang1 
FROM xwrh9_betty_dictionary_ids a 
LEFT JOIN xwrh9_betty_dictionary_words b ON b.id=a.lang1 OR b.id=a.lang2
WHERE b.id<>19
GROUP BY b.id;

引用自：https://dba.stackexchange.com/questions/20661