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

Question

我是 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 年 Spring 每个部门提供的部分总数。”

我尝试了以下查询:

 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 年 Spring 没有使用 NULL 值来教授它们

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

Answer 1

对于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;