[PostgreSQL, Greenplum] 한정술어 ANY, SOME, ALL, EXISTS 연산자

지난번 포스팅에서는 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 비교 ]

PostgreSQL, Greenplum, 한정술어, any, some, 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

 

 

이번 포스팅이 많은 도움이 되었기를 바랍니다. 

행복한 데이터 과학자 되세요. :-)