sqlzoo第八关Using Null

最新推荐文章于 2025-01-24 06:00:00 发布

原创最新推荐文章于 2025-01-24 06:00:00 发布 · 357 阅读

CC 4.0 BY-SA版权

15 篇文章

订阅专栏

本文通过具体实例，详细解析了SQL中INNER JOIN、LEFT JOIN和RIGHT JOIN的使用方法，以及COALESCE函数在处理空值时的应用技巧。通过教师和部门数据库的案例，展示了如何列出所有教师、所有部门，以及如何处理缺失的部门信息。

第八关 Using Null

表1 老师表（teacher）

id(编号)	dept(部门)	name（名字）	phone（电话）	mobile（手机）
101	1	Shrivell	2753	07986 555 1234
102	1	Throd	2754	07122 555 1920
103	1	Splint	2293
…	…	…	…	…

表2 部门表（dept）

select name from teacher where dept is null

Note the INNER JOIN misses the teachers with no department and the departments with no teacher.

SELECT teacher.name, dept.name
 FROM teacher INNER JOIN dept
           ON (teacher.dept=dept.id)

SELECT teacher.name, dept.name
 FROM teacher left JOIN dept
           ON (teacher.dept=dept.id)

SELECT teacher.name, dept.name
 FROM teacher right JOIN dept
           ON (teacher.dept=dept.id)

Use COALESCE to print the mobile number. Use the number ‘07986 444 2266’ if there is no number given. Show teacher name and mobile number or ‘07986 444 2266’

select name,coalesce(mobile,'07986 444 2266') from teacher

Use the COALESCE function and a LEFT JOIN to print the teacher name and department name. Use the string ‘None’ where there is no department.

select t.name,coalesce(dept.name,'None') from teacher t
left join dept on (t.dept = dept.id)

select count(name),count(mobile) from teacher

Use COUNT and GROUP BY dept.name to show each department and the number of staff. Use a RIGHT JOIN to ensure that the Engineering department is listed.

select d.name,count(t.name) c from teacher t
	right join dept d on (t.dept=d.id)
	group by t.dept,d.name order by c desc