MySQL - 基础题练习题 - 查询篇(7)

数据在本专栏的第一篇博客里

sql99标准的连接查询，左连接就很方便

8.1.查询编号>3的女神的男朋友信息，如果有则列出详细的信息，如果没有，则用null填充

SELECT
	be.id,
	be.NAME,
	bo.* 
FROM
	beauty be
	LEFT JOIN boys bo ON be.boyfriend_id = bo.id 
WHERE
	be.id > 3;

8.2.查询哪个城市没有部门

SELECT
	city,
	department_name 
FROM
	locations l
	LEFT JOIN departments d ON l.location_id = d.location_id 
WHERE
	department_name IS NULL;

8.3.查询部门名称为SAL或IT的员工信息

SELECT
	e.*,
	department_name 
FROM
	employees e
	LEFT JOIN departments d ON e.department_id = d.department_id 
WHERE
	department_name IN ( 'SAL', 'IT' );