LeetCode- 使用唯一标识码替换员工ID / 院系无效学生

10.使用唯一标识码替换员工ID

链接:https://leetcode-cn.com/problems/replace-employee-id-with-the-unique-identifier/

--左外连接
select 
b.unique_id as 'unique_id',
a.name as 'name'
from Employees a left join EmployeeUNI b on
a.id=b.id;
--右外连接
select 
ifnull(e.unique_id,null) as unique_id,
n.name 
from EmployeeUNI e right join Employees n 
on n.id=e.id;

11.院系无效学生

链接:https://leetcode-cn.com/problems/students-with-invalid-departments/

select
	id,
	'name'
from
	Students 
where department_id not in(
	select
		id
	from
		Departments
);

--左连接方法
select 
s.id as 'id',
s.name as 'name'
from Students s left join Departments d
on s.department_id=d.id
where d.name IS NULL;
--此处注意不能写成where d.name=NULL


--因为:
-- NULL表示不可知不确定，NULL不与任何值相等（包括其本身）

-- IS NULL 判断某个字符是否为空，并不代表空字符或者是0

-- =NULL 是判断某个值是否等于NULL

-- 总之，要判断一个数是否等于NULL只能用 IS NULL 或者 IS NOT NULL 来判断