[SQL] 내가 보려고 기록한 SQL 고급문법 - UNION,EXCEPT,INTERSECT,COALESCE,NULLIF,LISTAGG

SQL 에서 다양한 문법이 사용된다 가장 핵심은 JOIN 이라고 생각하지만 그 외 사용할경우 효과적인 문법에 대해서 알아보고자 한다.

---

UNION, EXCEPT, INTERSECT

UNION

• 합집합을 표현할 때 사용하는 문법으로, 여러 테이블의 조회결과를 하나로 합쳐 하나의 테이블로 조회하고 싶을 때 사용하는 문법이다
• 즉 여러개의 SELECT 를 하나의 SELECT 로 만들때 사용할 수 있다.
• UNION vs UNION ALL
  UNION 만 사용하면 동일한 값은 제거하고 나타내고, ALL 을 추가하면 중복을 포함하여 조회한다.

--기본적인 사용방법
SELECT col1 FROM table1
UNION (ALL)
SELECT col1 FROM table2;

예시)

• employees 테이블

name
alice
bob

• Manager 테이블

name
bob
Charlie

• UNION 결과

name
alice
bob
Charlie

• UNION ALL 결과

name
alice
bob
bob
Charlie

EXCEPT ( MINUS )

• 하나의 SELECT 결과에서 다른 SELECT 결과를 제외하여 조회하는 것.
• 즉 A-B 라고 볼 수 있다.

SELECT col1 FROM table1
EXCEPT
SELECT col1 FROM table2;

위와 동일하게 Employees, Managers 테이블을 가지고 실행한다면
그 결과는 다음과 같을 것이다.

name
alice
Charlie

INTERSECT ( 교집합 )

• 여러개의 SELECT 문 가운데 동일한 값들만 뽑아서 보여준다.

SELECT col1 FROM table1
INTERSECT
SELECT col1 FROM table2

위와 동일하게 Employees, Managers 테이블을 참조한다면 다음과 같은 결과를 얻을 수 있다.

name
bob

주의할 점.

UNION, EXCEPT, INTERSECT 의 경우 조회하는 각각의 테이블들의 결과의 필드의 수와,타입이 동일해야 Error가 발생하지 않으므로, 사용할 때 주의하여야 한다.

어떠한 상황에서 사용할 수 있을까?

함수의 정의처럼 합집합이나 교집합 차집합 등을 구할 때 사용할 수 있지만 차이점을 확인할 때 사용하기에도 적합할 수 있다. 예를들어 기존 SELECT A 를 통해 데이터를 조회하다가 로직을 변경하여 SELECT B 를 작성하였다면 두 쿼리가 동일한 결과를 얻어야 하는데 다른 결과를 얻어오는지 등을 확인할 때 사용할 수 있을 것 같다.

---

COALESCE, NULLIF

COALESCE(expr1,expr2,expr3,....)

• 인수로 받는 첫번째 expr1 부터 마지막 exprN 까지 조회를 하는데 조회를 하다 NULL이 아닌 값이 나타나면 그 값을 모두 리턴하고, 모두 NULL 이라면 NULL 을 리턴한다.
• 어디에 쓰일까? NULL 값이 있는지 조회하고 있는경우 값을 변경하고 싶을 때 주로 사용한다.

SELECT COALESCE(col1,col2,'default') FROM table1;

다음과 같은 테이블이 있을 때 COALESCE 가 어떻게 사용되는지 알아보자.

• employees 테이블

Name	Nickname
Alice	NULL
Bob	Bobby

SELECT COALESCE(Nickname,Name) AS RES FROM employees;

위와 같은 테이블이 있을 때 다음 쿼리를 사용하면 Nickname 값을 검사하고 null 값이 존재하면 Name 컬럼의 값으로 조회하도록 쿼리를 작성하였다.

RES
Alice
Bobby

이처럼 NULL 값을 조회하고, 그 값을 변경하고 싶을 때 이처럼 사용할 수 있다.

NULLIF(expr1,expr2)

• 두개의 인자를 받는데 두개의 인자를 비교하고 같으면 NULL 을 리턴하고, 같지 않다면 첫번째(expr1)의 인자를 리턴한다.

  알아두면 좋은 NULL 특성 !
  NULL 값이 있을 때 어떠한 사칙연산(+,-,/,*) 를 하더라도 반드시 NULL 이 나온다는 점을 기억하자.

SELECT NULLIF(COL1,COL2) FROM TABLE1;

• Score 테이블

Student	Score
Alice	90
Bob	95

SELECT Student,NULLIF(Score,90) as RES FROM Score;

위와 같은 테이블과 쿼리를 작성했다면 Score 와 90 의 값을 비교하여 같으면 NULL을 리턴하고, 같지 않으면 첫번째 값을 리턴하는 것을 아래의 결과에서 확인할 수 있다.

Student	RES
Alice	NULL
Bob	95

---

LISTAGG

LISTAGG

• GROUP BY 를 사용한 Aggregate 함수 중 하나로 그룹을 기준으로 인자로 사용된 컬럼의 값을 리스트 업 해준다.
• 두번째 인자로 Delimiter를 지정할 수 있으며 선택적으로 사용가능하다.
• 리스트업 결과를 특정한 값을 기준으로 정렬하여 나타내고 싶은경우 WITHIN GROUP ( ORDER BY col ) AS example 처럼 작성하여 쿼리하면 가능하다.

SELECT COL1,LISTAGG(COL2,', ')
WITHIN GROUP ( ORDER BY COL1 ) AS AGG_COL
FROM TABLE1
GROUP BY COL1;

위 사용방식 처럼 사용이 가능한데 다음 예시를 통해 어떻게 사용되는지 확인해보자.

• Courses 테이블

Student	Course
Alice	Math
Alice	Science
Bob	Math

SELECT Student,LISTAGG(Course,', ') WITHIN GROUP (ORDER BY Course) as CourseList 
FROM Courses
GROUP BY Student;

이렇게 사용하면 Student 이름으로 그룹화하여 학생이 수강하는 Course 를 리스트업하고 그 순서는 수강과목의 알파벳 순서로 하여 조회할 수 있다.