[PostgreSQL, Greenplum] 한정술어 ANY, SOME, ALL, EXISTS 연산자
Greenplum and PostgreSQL Database 2021. 5. 30. 23:40지난번 포스팅에서는 PostgreSQL, Greenplum에서 두 개의 SELECT 문 결과에 대한 합집합(UNION, UNION ALL), 교집합(INTERSECT), 차집합(EXCEPT) 에 대해서 알아보았습니다. (참고 ==> https://rfriend.tistory.com/659 )
이번 포스팅에서는 Sub-Query의 결과를 WHERE 문에서 비교 조건으로 하여 사용할 수 있는 한정 술어 연산자로서 ANY, SOME, ALL, EXISTS 연산자(operator)에 대해서 알아보겠습니다.
1. ANY, SOME 연산자
2. ALL 연산자
3. EXISTS, NOT EXISTS 연산자
[ Sub-Query 결과를 비교 조건으로 사용하는 한정 술어 ANY/ SOME vs. ALL 비교 ]
먼저, 예제로 사용할 간단한 테이블 두 개를 만들어보겠습니다. 'cust_master' 테이블은 id, age, gender 의 세 개 칼럼으로 구성되어 있고, cust_amt 테이블은 id, amt 의 두 개 칼럼으로 구성되어 있으며, 두 테이블은 id 를 공통으로 가지고 있어 서로 연결이 가능합니다. (** 이번 포스팅에서는 JOIN 은 사용하지 않고, 대신 한정 술어를 사용해서 JOIN 결과와 유사한 결과를 얻어보겠습니다.)
-----------------------------------------------------------------------------------
-- 한정술어: ANY/ SOME, ALL, EXISTS, NOT EXISTS operators
-----------------------------------------------------------------------------------
-- creating sample tables
DROP TABLE IF EXISTS cust_master;
CREATE TABLE cust_master (
id int
, age int
, gender text
) DISTRIBUTED RANDOMLY;
INSERT INTO cust_master VALUES
(1, 45, 'M')
, (2, 34, 'F')
, (3, 30, 'F')
, (4, 28, 'M')
, (5, 59, 'M')
;
DROP TABLE IF EXISTS cust_amt;
CREATE TABLE cust_amt (
id int
, amt int
) DISTRIBUTED RANDOMLY;
INSERT INTO cust_amt VALUES
(1, 500)
, (2, 200)
, (3, 750)
, (8, 900)
, (9, 350)
;
(1) ANY, SOME 연산자
ANY 한정술어 연산자는 Sub-Query 의 결과 값들 중에서 어떤 값이라도 =, <>, !=, <, <=, >, >= 등의 비교 연산자의 조건을 만족하면 TRUE 를 반환하며, 그렇지 않은 경우 FALSE 를 반환합니다. SOME 한정 술어 연산자는 ANY 연산자와 동일한 기능을 수행합니다.
Sub-Query 는 반드시 1개의 칼럼만 반환해야 합니다.
아래의 예에서는 cust_amt 테이블에서 amt > 300 인 조건을 만족하는 id 와 동일한('=') id 를 가진 값을 cust_master 테이블에서 SELECT 해본 것입니다. (JOIN 문을 사용해도 됩니다만, ANY 연산자를 사용해서 아래처럼도 가능합니다. PostgreSQL이 내부적으로 query optimization을 해서 JOIN 문을 쓰던 ANY/ SOME 연산자를 쓰던 성능은 비슷합니다.)
WHERE 조건문에 IN 연산자를 사용할 경우에는 ANY/SOME 연산자에서 같이 사용했던 비교 연산자 ('=') 가 없는 차이점이 있습니다.
WHERE 조건절에서 ANY, SOME 연산자에 비교연산자(=, <>, !=, <, <=, >, >=) 가 같이 사용되었을 경우의 의미는 포스팅 초반의 표를 참고하세요.
--------------------------------
-- ANY, SOME operator
--------------------------------
-- ANY operator compares a value to a set of values returned by a subquery.
--The ANY operator must be preceded by one of the following comparison operator =, <=, >, <, > and <>
--The ANY operator returns true if any value of the subquery meets the condition, otherwise, it returns false.
SELECT id
FROM cust_amt
WHERE amt > 300
ORDER BY id;
--id
--1
--3
--8
--9
-- ANY OPERATOR
SELECT *
FROM cust_master
WHERE id = ANY (
SELECT id
FROM cust_amt
WHERE amt > 300
);
--id age gender
--1 45 M
--3 30 F
-- SOME OPERATOR
SELECT *
FROM cust_master
WHERE id = SOME (
SELECT id
FROM cust_amt
WHERE amt > 300
);
-- IN
SELECT *
FROM cust_master
WHERE id IN (
SELECT id
FROM cust_amt
WHERE amt > 300
);
(2) ALL 연산자
ALL 한정술어 연산자는 Sub-Query의 결과의 '모든 값과 비교' 하여 '모든 값이 조건을 만족하면 TRUE, 그렇지 않으면 FALSE'를 반환합니다.
WHERE 조건절에서 ALL 연산자에 비교연산자(=, <>, !=, <, <=, >, >=) 가 같이 사용되었을 경우의 의미는 포스팅 초반의 표를 참고하세요. 가령 아래의 예에서 "WHERE age > ALL (sub-query)" 는 "WHERE age > sub-query의 MAX" 값과 같은 의미입니다. 아래의 예에서는 Sub-Query의 avg_age 가 32, 44 이므로 이중에서 MAX 값인 44보다 age가 큰 값을 cust_master 테이블에서 조회를 하겠군요.
---------------------------
-- the ALL operator
---------------------------
-- the PostgreSQL ALL operator compares a value with a list of values returned by a subquery.
SELECT gender, avg(age) AS avg_age
FROM cust_master
GROUP BY gender;
--gender avg_age
--F 32.0000000000000000
--M 44.0000000000000000
SELECT *
FROM cust_master
WHERE age > ALL (
SELECT avg(age) AS avg_age
FROM cust_master
GROUP BY gender
);
--id age gender
--1 45 M
--5 59 M
(3) EXISTS, NOT EXISTS 연산자
EXISTS 연산자는 Sub-Query의 결과에서 값이 존재하는지를 평가하는 블리언 연산자입니다. 만약 Sub-Query의 결과에 단 1개의 행이라도 값이 존재하다면 EXISTS 연산자의 결과는 TRUE 가 되며, Sub-Query의 결과가 한 개의 행도 존재하지 않는다면 FALSE 가 됩니다.
아래의 예에서는 cust_master 테이블과 cust_amt 의 두 개 테이블을 같이 사용해서, cust_amt 테이블의 amt > 400 인 조건을 만족하고 cust_master 와 cust_amt 테이블에서 id 가 서로 같은 값이 존재(EXISTS) 하는 cust_master 의 모든 칼럼 값을 가져온 것입니다. (JOIN 문을 사용하지 않고도 EXISTS 문을 사용해서 아래처럼 쓸 수도 있답니다. 성능은 비슷.)
NOT EXISTS 연산자는 EXISTS 연산자를 사용했을 때와 정반대의 값을 반환합니다.(TRUE, FALSE 가 서로 정반대임).
---------------------------
-- EXISTS operator
---------------------------
-- The EXISTS operator is a boolean operator that tests for existence of rows in a subquery.
-- If the subquery returns at least one row, the result of EXISTS is TRUE.
-- In case the subquery returns no row, the result is of EXISTS is FALSE.
SELECT *
FROM cust_master AS m
WHERE EXISTS (
SELECT 1
FROM cust_amt AS a
WHERE m.id = a.id
AND a.amt > 400
)
ORDER BY id;
--id age gender
--1 45 M
--3 30 F
----------------------------------
-- NOT EXISTS operator
----------------------------------
-- in case the subquery returns no row, the result is of NOT EXISTS is TRUE
SELECT *
FROM cust_master AS m
WHERE NOT EXISTS (
SELECT 1
FROM cust_amt AS a
WHERE m.id = a.id
AND a.amt > 400
)
ORDER BY id;
--id age gender
--2 34 F
--4 28 M
--5 59 M
이번 포스팅이 많은 도움이 되었기를 바랍니다.
행복한 데이터 과학자 되세요. :-)