如果未找到行，则为列显示空值 [英] Display null for column if no row found

问题描述

我目前有以下 SQL 语句:

I currently have the following SQL statement:

SELECT s.first_name, s.last_name, tgs.group_identifier
FROM staff s
JOIN staff_training st
ON s.staff_id = st.staff_id
JOIN training_group_staff tgs
ON st.staff_training_id = tgs.staff_training_id
WHERE st.staff_course_id = '164'

training_group_staff 只包含 staff_training_id 和 training_group_id

此语句按预期工作并返回所有参加课程 164 并且已被放入一个组(用 group_identifier 字段标识)的员工姓名.

This statement works as expected and returns all staff names attending course 164 AND have been placed into a group (identified with the group_identifier field).

我试图做的是显示培训课程 164 上的所有用户，如果没有选择组(training_group_staff 中不会有特定 staff_training_id 的行) 然后为该列返回 null.

What I am attempting to do is display all users on training course 164 and if no group selected (there won't be a row for the specific staff_training_id in training_group_staff) then return null for that column.

视觉示例

当前语句返回:

first_name  |  last_name  |  group_identifier
 Jim        |   Jones     |   3
 Harry      |   Jones     |   6

我试图返回的是:

first_name  |  last_name  |  group_identifier
 Jim        |   Jones     |   3
 Harriet    |   Smith     |   NULL   // No row in training_group_staff
 Harry      |   Jones     |   6

我尝试了多个连接，但似乎返回相同的结果 - 在一个查询中是否可能获得所需的输出?

I have tried several joins, however seem to return the same results - is the desired output possible in one query?

推荐答案

尝试使用 LEFT JOIN.应该是你想要的.

Try with LEFT JOIN. Should be what you're after.

这篇关于如果未找到行，则为列显示空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！