mysql - SQL如何为另一个表生成输出后列出NULL值

我是SQL的新手，但我有以下疑问-

3张桌子：

mysql> describe course;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| course_id | varchar(8)   | NO   | PRI |         |       |
| title     | varchar(50)  | YES  |     | NULL    |       |
| dept_name | varchar(20 ) | YES  | MUL | NULL    |       |
| credits   | decimal(2,0) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+


mysql> describe section;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| course_id    | varchar(8)   | NO   | PRI |         |       |
| sec_id       | varchar(8)   | NO   | PRI |         |       |
| semester     | varchar(6)   | NO   | PRI |         |       |
| year         | decimal(4,0) | NO   | PRI | 0       |       |
| building     | varchar(15)  | YES  | MUL | NULL    |       |
| room_number  | varchar(7)   | YES  |     | NULL    |       |
| time_slot_id | varchar(4)   | YES  |     | NULL    |       |
| capacity     | int(11)      | YES  |     | 30      |       |
+--------------+--------------+------+-----+---------+-------+

describe department;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| dept_name | varchar(20)   | NO   | PRI |         |       |
| building  | varchar(15)   | YES  |     | NULL    |       |
| budget    | decimal(12,2) | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+

问题是“列出每个部门在2008年春季提供的部门总数。”

我已经尝试过以下查询：

 SELECT dept_name,SUM(sec_id) AS Total_offerings
 FROM section natural
 left outer join course
 WHERE semester='Spring' and year=2008
 GROUP By dept_name;

但是结果不包含具有Null值的部门名称。

任何人都可以帮助我如何在输出中添加部门名称，即使在2008年春季没有使用NULL值来教它们时

The output looks like this:
+-------------+----------------+
| dept_name   | total_offering |
+-------------+----------------+
| Accounting  | 7              |
| Astronomy   | 4              |

最佳答案

对于LEFT JOIN，您需要将过滤器置于连接条件上，否则只是正常的INNER JOIN

SELECT dept_name, COUNT(sec_id) AS Total_offerings
FROM department
JOIN course
  ON  department.dept_name = course.dept_name
LEFT JOIN section
  ON section.course_id = course.course_id
 AND section.semester = 'Spring'
 AND section.year = 2008
GROUP By dept_name;