[SQL] SQL 연산자와 함수, 집계함수

[ 집합 연산자 Set Operator ]

집합 연산자는 SELECT 결과의 합집합, 교집합, 차집합을 구하는 연산자이다.

JOIN이 열을 옆으로 붙인다고 하면, 집합연산자는 행을 위로 합친다고 생각하면 쉽다.

• 컬럼의 갯수가 같아야하고, 첫번째 SELECT를 기준으로 순서에 따라 각 컬럼의 타입이 호환되어야한다.
  (DB가 자동 형변환을 할 수 있으면 되고, 안되면 CAST()로 맞춰줘야한다.)
• ORDER BY 는 보통 맨 마지막에 한번만 붙인다.

 

1. UNION : 합집합, 중복 제거

SELECT name FROM users_a
UNION
SELECT name FROM users_b;

• CAST 사용

SELECT CAST(id AS TEXT) FROM a
UNION ALL
SELECT CAST(created_at AS TEXT) FROM b;

2. UNION ALL : 합집합, 중복 허용

SELECT name FROM users_a
UNION ALL
SELECT name FROM users_b;

 

3. INTERSECT : 교집합

• PostgreSQL는 지원되지만 MySQL은 지원되지 않아 INNER JOIN이나 IN으로 대체한다.

SELECT name
FROM users_a
INTERSECT
SELECT name
FROM users_b;

4. EXCEPT : 차집합 

• PostgreSQL는 지원되지만 MySQL은 지원되지 않아 NOT IN이나 LEFT JOIN으로 대체한다.

SELECT name FROM users_a
EXCEPT
SELECT name FROM users_b;

---

[ 연산자 Operator ]

1. 산술 연산자 (Arithmetic)

• +, -, *, / (DB에 따라 % 지원)
• MySQL은 정수/실수 타입에 따라 결과가 달라질 수 있어 필요하면 CAST 사용한다.

SELECT 10 + 3 AS plus,
       10 - 3 AS minus,
       10 * 3 AS multiply,
       10 / 3 AS divide;

 

2. 비교 연산자 (Comparison)

• =, !=, <>, >, >=, <, <=
• != 와 <>는 “같지 않다” 의미로 동일하게 쓰인다.

SELECT * FROM users
WHERE age >= 20;

 

3. 논리 연산자 (Logical)

• AND, OR, NOT

SELECT * FROM users
WHERE age >= 20 AND gender = '여자';

SELECT * FROM users
WHERE NOT (age = 20)

<> 와 NOT
- <> : 값을 비교할때 사용(같지 않다)
- NOT : 조건을 통째로 부정할 때 사용

 

4. 범위 연산자

• BETWEEN

SELECT * FROM users
WHERE age BETWEEN 20 AND 29;

 

5. 집합 비교 연산자

• IN, ANY, ALL

WHERE id IN (1,2,3)
WHERE id IN (SELECT user_id FROM orders)

WHERE price > ANY (SELECT price FROM products)

WHERE price > ALL (SELECT price FROM products)

 

6. 문자열 패턴 매칭 연산자

• % : 0글자 이상 아무 문자열
• _ : 글자 수

SELECT * FROM users
WHERE email LIKE '%@gmail.com';

WHERE name LIKE '김%'          -- '김'으로 시작

WHERE name LIKE '%철수'         -- '철수'로 끝

WHERE email LIKE '%admin%'     -- 가운데 'admin' 포함

WHERE code LIKE 'a_c'         -- 정확히 1글자만 사이에 들어감: a?c

Elastic Search (ES)와 RDBMS의 LIKE 
1) SQL의 LIKE
 - RDBMS에서 문자열 패턴 매칭으로 검색하는 방식이다.
 - 동의어/유의어 검색이나 오타 허용 같은 기능이 없어 유연한 검색이 불가능하다.
   (근데 최근 Full-Text 검색을 지원하지만 n-gram기반이라  ES에 비해 정확도가 떨어진다.)
 - 특히 LIKE '%키워드%'처럼 앞에 %가 붙는 검색(leading wildcard) 은 인덱스를 타기 어려워 전체 스캔에 가까워져 느려질 수 있다.

2) Elastic Search (ES) 검색엔진
- ES는 RDBMS처럼 행을 하나씩 비교하는 방식이 아니라, 문서를 저장할 때 텍스트를 토큰화해서 역색인(Inverted Index)을 만들어 검색하는 검색엔진이다.
 - 검색 속도가 빠르다.
 - 동의어/유의어 검색, 오터 허용 등 유연한 검색이 가능하다.
 - 비정형 데이터의 검색이 가능하다.

 

5. NULL 관련 연산자 

• NULL은 “값이 없다”는 의미이므로, = 비교가 아니라 IS를 사용한다.

SELECT * FROM users
WHERE deleted_at IS NULL;

SELECT * FROM users
WHERE deleted_at IS NOT NULL;

 

6. 문자열 연결

• MySQL : CONCAT() 사용

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM users;

• PostgreSQL : || 사용

SELECT first_name || ' ' || last_name AS full_name
FROM users;

---

[ 함수 Function ]

1. 문자열 함수 (String)

• LENGTH, SUBSTRING, REPLACE, TRIM, LOWER, UPPER 등

SELECT email,
       LENGTH(email) AS len,
       SUBSTRING(email, 1, 5) AS prefix,
       REPLACE(email, '@', '(at)') AS replaced
FROM users;

 

2. 숫자 함수 (Number)

• ROUND, CEIL, FLOOR, ABS 등

SELECT ROUND(3.14159, 2) AS round2,
       CEIL(1.2) AS ceil_val,
       FLOOR(1.9) AS floor_val,
       ABS(-10) AS abs_val;

 

3. 날짜/시간 함수 (Date/Time)

1) 현재 날짜/시간

-- MySQL
SELECT NOW();          -- 현재 날짜+시간 (DATETIME)
SELECT CURDATE();      -- 현재 날짜 (DATE)
SELECT CURTIME();      -- 현재 시간 (TIME)
SELECT CURRENT_TIMESTAMP; -- NOW()와 유사

-- PostgreSQL
SELECT NOW();              -- 현재 날짜+시간 (timestamp with time zone)
SELECT CURRENT_DATE;       -- 현재 날짜
SELECT CURRENT_TIME;       -- 현재 시간
SELECT CURRENT_TIMESTAMP;  -- NOW()와 유사

 

2) 날짜 더하기/빼기 

• DATE_ADD(날짜, INTERVAL 숫자 DAY/MONTH/HOUR/MINUTE)
• DATE_SUB(날짜, INTERVAL 숫자 DAY/MONTH/HOUR/MINUTE)

-- MySQL
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);    -- 7일 더하기
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);  -- 1개월 빼기

SELECT NOW() + INTERVAL 3 HOUR;
SELECT NOW() - INTERVAL 10 MINUTE;


-- PostgreSQL
SELECT NOW() + INTERVAL '7 days';
SELECT NOW() - INTERVAL '1 month';
SELECT NOW() + INTERVAL '3 hours';

 

3) 날짜 차이

• DATE DIFF()

-- MySQL

SELECT DATEDIFF('2026-01-13', '2026-01-01');  -- 12
SELECT TIMESTAMPDIFF(HOUR, '2026-01-01 10:00:00', '2026-01-02 12:00:00'); -- 26 
SELECT TIMESTAMPDIFF(MINUTE, '2026-01-01 10:00:00', '2026-01-01 10:30:00'); -- 30 


-- PostgreSQL
SELECT DATE '2026-01-13' - DATE '2026-01-01'; -- 12
SELECT TIMESTAMP '2026-01-02 12:00:00'
     - TIMESTAMP '2026-01-01 10:00:00';    -- 1 day 02:00:00

4) 날짜 형식 변환(포맷)

• DATE_FORMAT(날짜, 포맷)
• TO_CHAR(날짜, 포맷)

-- MySQL
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); -- 2026-01-13 16:10:00
SELECT DATE_FORMAT(NOW(), '%Y%m%d');            -- 20260113


-- PostgreSQL
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');
SELECT TO_CHAR(NOW(), 'YYYYMMDD');

 

5) 날짜에서 일부 추출

• EXTRACT(날짜 FROM 날짜)

-- MySQL
SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW());
SELECT HOUR(NOW()), MINUTE(NOW()), SECOND(NOW());
SELECT EXTRACT(YEAR FROM NOW());
SELECT EXTRACT(MONTH FROM NOW());


-- PostgreSQL
SELECT EXTRACT(YEAR FROM NOW());
SELECT EXTRACT(MONTH FROM NOW());
SELECT EXTRACT(DAY FROM NOW());
SELECT EXTRACT(HOUR FROM NOW());

 

4. 집계 함수 (Aggregate)

• COUNT, SUM, AVG, MIN, MAX
• COUNT(*)를 제외하고 COUNT(컬럼명) 등 대부분의 집계함수는 NULL값을 자동으로 제외하고 계산된다.
• NULL값만 있다면 COUNT를 제외하고 NULL 값이 나온다.

SELECT COUNT(*) AS total,
       AVG(age) AS avg_age,
       MIN(age) AS min_age,
       MAX(age) AS max_age
FROM users;

SELECT status, COUNT(*) AS cnt
FROM users
GROUP BY status
HAVING COUNT(*) >= 10;

 

5. 조건 함수 

• CASE WHEN 조건 THEN 값 ELSE 값

SELECT id, name,
  CASE
    WHEN age < 14 THEN 'CHILD'
    WHEN age < 20 THEN 'TEEN'
    ELSE 'ADULT'
  END AS age_group
FROM users;

 

6. NULL 처리 함수

• COALESCE : 괄호 () 내 순서대로 null이 아닌 값을 우선 반환

SELECT COALESCE(nickname, name, '익명') AS nickname
FROM users;

• IFNULL : MySQL에서 사용.

SELECT IFNULL(nickname, '익명') AS nickname
FROM users;1.