【PostgreSQL 17】8 子查询

一、相关运算符 IN、ALL、ANY/SOME、EXISTS

IN

最好不要有null，

SELECT
	*
FROM
	departments
WHERE
	department_id IN (10, 20, 30)
;

SELECT
	*
FROM
	departments
WHERE
	department_id IN (
		SELECT
			department_id
		FROM
			employees
		WHERE
			hire_date >= date '2008-01-01'
	)
;

ALL

SELECT
	*
FROM
	employees
WHERE
	salary > ALL (
		SELECT
			salary
		FROM
			employees
		WHERE
			department_id = 80
	)
;

等价于

SELECT
	*
FROM
	employees
WHERE
	salary > (
		SELECT
			Max(salary)
		FROM
			employees
		WHERE
			department_id = 80
	)
;

ANY / SOME

SELECT
	*
FROM
	employees
WHERE
	salary > ANY(
		SELECT
			salary
		FROM
			employees
		WHERE
			department_id = 80
	)
;

EXISTS

-- EXISTS

SELECT
	*
FROM
	departments d
WHERE
	EXISTS(
		SELECT null
		FROM
			employees
		WHERE
			department_id = d.department_id
		AND
			hire_date > date '2008-01-01'
	)
;

二、子查询

SELECT *
FROM employees
WHERE salary >= (
	SELECT AVG(salary)
	FROM employees
);

SELECT
	d.department_name, 
	ds.total_sal
FROM
	departments d
JOIN (
	SELECT
		department_id,
		SUM(salary) AS total_sal
	FROM
		employees
	GROUP BY
		department_id
) ds
ON
	d.department_id = ds.department_id
;

三、关联子查询

SELECT
	d.department_name,
	(
		SELECT 
			SUM(salary)
		FROM
			employees
		WHERE
			department_id = d.department_id
	)
FROM
	departments d
;

SELECT
	*
FROM
	employees e
WHERE
	salary > (
		SELECT
			AVG(salary)
		FROM
			employees
		WHERE
			department_id = e.department_id
	)
;

四、LATERAL 横向子查询

报错，此时是找不到d的

加上lateral

SELECT
	d.department_name,
	t.first_name,
	t.salary
FROM
	departments d
JOIN LATERAL(
	SELECT
		first_name,
		salary
	FROM
		employees
	WHERE
		department_id = d.department_id
	ORDER BY
		salary DESC
	LIMIT 3
) t
ON true
;

参考资料

[1] 不剪发的Tony老师【PostgreSQL开发指南】第22~26节