#where或having后面: #标量子查询(单行子查询) #列子查询(多行子查询) #行子查询(多行多列)

特点:子查询放在小括号内，一般放在条件的右侧，标量子查询一般配备单行操作符使用 单行操作符：<> >= <= < > 列子查询：一般搭配着多行操作符使用 多行操作符：in、any、some、all

#标量子查询 #案例：谁的工资比ABEL高的员工信息

SELECT *
FROM  employees
WHERE salary>(

	SELECT salary
	FROM employees
	WHERE last_name='Abel'
);

#案例：返回job_id与141号员工相同，salary比143号员工多的员工的姓名、job_id和工资

SELECT
	last_name,job_id,salary
FROM employees
WHERE job_id=(
	SELECT job_id
	FROM employees
	WHERE employee_id=141
)
AND salary>(
	SELECT salary
	FROM employees
	WHERE employee_id=143
);

#案例：返回工资最少的员工的last_name、job_id和salary

SELECT	last_name,job_id,salary
FROM employees
WHERE	salary=(
	SELECT MIN(salary)
	FROM employees
);

#案例：查询最低工资大于50号部门最低工资的部门id和其最低工资

SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING	MIN(salary)>(
	SELECT MIN(salary)
	FROM employees
	WHERE department_id=50
);

#非法使用标量子查询

SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING	MIN(salary)>(
	SELECT salary   #单行操作符只能搭配标量子查询，而这是列子查询
	FROM employees
	WHERE department_id=50
);