我有兩個(gè)表，我想查詢 2 個(gè)表以獲取報(bào)告。POSITION+-------------+---------------+| position_id | position_name |+-------------+---------------+| 1 | E1P1 || 2 | E1P2 || 3 | E3P3 || 4 | E4P4 |+-------------+---------------+ PEOPLE+------------+-------------+--------------------+| people_id | people_name | people_position_id |+------------+-------------+--------------------+| 1 | JOHN | 2 || 2 | MARK | 4 |+------------+-------------+--------------------+詢問SELECT position_id, position_name, people_name FROM positionRIGHT JOIN people ON people_position_id = position_id當(dāng)我使用簡(jiǎn)單查詢時(shí)，我只得到匹配的行，如何獲取所有行？我想得到這個(gè)結(jié)果+----+----------+--------+| ID | POSITION | STATUS |+----+----------+--------+| 1 | E1P1 | Empty || 2 | E1P2 | JOHN || 3 | E3P3 | Empty || 4 | E4P4 | MARK |+----+----------+--------+
1 回答
素胚勾勒不出你
TA貢獻(xiàn)1827條經(jīng)驗(yàn) 獲得超9個(gè)贊
我會(huì)在這里使用左連接：
SELECT
po.position_id,
po.position_name,
COALESCE(pe.people_name, 'EMPTY') AS STATUS
FROM position po
LEFT JOIN people pe
ON po.position_id = pe.people_position_id;
順便說一下，您當(dāng)前的右連接嘗試失敗的原因是您已將people表放在連接的右側(cè)。這意味著不匹配的位置記錄將被丟棄。這是我上面的答案，使用右連接重寫：
SELECT
po.position_id,
po.position_name,
COALESCE(pe.people_name, 'EMPTY') AS STATUS
FROM people pe
RIGHT JOIN position po
ON po.position_id = pe.people_position_id;
請(qǐng)注意，表順序已切換。大多數(shù)時(shí)候，您會(huì)看到人們使用左連接而不是右連接。
- 1 回答
- 0 關(guān)注
- 154 瀏覽
添加回答
舉報(bào)
0/150
提交
取消