Chap03-1. Transact-SQL 기본 - SELECT

Chap03-1. Transact-SQL 기본 - SELECT

SQL(Structured Query Language: 구조화된 질의 언어)문은 데이터베이스에서 사용되는 언어이다. 그 중에서 마이크로소프트의 SQL Server에서 사용하는 SQL은 Transact-SQL이라고하며 T-SQL이라고도 한다.

T-SQL에 대한 공식문서는 docs.microsoft.com에서 각 쿼리를 상세하게 알아볼 수 있다. 이번 포스팅에서는 T-SQL에 대한 기본적인 SQL문에 대해 알아보도록 하자.

이번 포스팅에서 예제로 사용할 데이터베이스는 Chap02 - Windows10에 SQL Server 2014 Developer Edition 설치하기 에서 다운로드 받은 샘플 데이터베이스인 AdventureWorks2014 와 sqlDB를 사용한다.

따라서, 이번 포스팅에 대해 실습을 진행하기 위해서는 AdventureWorks2014 데이터베이스를 다운로드 받아 SQL Server Management Studio(이하 SSMS)에 설치가 되어있어야 하며 또한, 바로 아래에 있는 '실습에 필요한 데이터베이스 및 테이블 만들기'를 통해 sqlDB를 만들어 줘야 한다.

1. 실습에 필요한 데이터베이스 및 테이블 만들기

1.1 sqlDB 생성

USE tempdb;
GO
CREATE DATABASE sqlDB;
GO

1.2 테이블 생성

USE sqlDB;
CREATE TABLE userTbl  -- 회원 테이블
( userID    char(8) NOT NULL PRIMARY KEY,  -- 사용자 ID
  name      nvarchar(10) NOT NULL,  -- 이름
  birthYear int NOT NULL,  -- 출생년도
  addr      nchar(2) NOT NULL,  -- 지역(경기, 서울, 경남 2글자만 입력)
  mobile1   char(3),  -- 휴대폰의 국번(011, 010, etc)
  mobile2   char(8),  -- 휴대폰의 나머지 번호('-' 제외)
  height    smallint,  -- 키
  mDate     date  -- 회원 가입일
);
GO
CREATE TABLE buyTbl  -- 회원 구매 테이블
( num       int IDENTITY NOT NULL PRIMARY KEY,  -- 순번(PK)
  userID    char(8) NOT NULL  -- 아이디(FK)
    FOREIGN KEY REFERENCES userTbl(userID),
  prodName  nchar(6) NOT NULL,  -- 물품명
  groupName nchar(4),  -- 분류
  price     int NOT NULL,  -- 단가
  amount    smallint NOT NULL  -- 수량
);
GO

1.3 데이터 입력

-- userTbl
INSERT INTO userTbl VALUES('LSG', '이승기', 1987, '서울', '011',
        '11111111', 182, '2008-8-8');
INSERT INTO userTbl VALUES('KBS', '김범수', 1979, '경남', '011',
        '22222222', 173, '2012-4-4');
INSERT INTO userTbl VALUES('KKH', '김경호', 1971, '전남', '019',
        '33333333', 177, '2007-7-7');
INSERT INTO userTbl VALUES('JYP', '조용필', 1950, '경기', '011',
        '44444444', 166, '2009-4-4');
INSERT INTO userTbl VALUES('SSK', '성시경', 1979, '서울', NULL,
        NULL, 186, '2013-12-12');
INSERT INTO userTbl VALUES('LJB', '임재범', 1963, '서울', '016',
        '66666666', 182, '2009-9-9');
INSERT INTO userTbl VALUES('YJS', '윤종신', 1969, '경남', NULL,
        NULL, 170, '2005-5-5');
INSERT INTO userTbl VALUES('EJW', '은지원', 1972, '경북', '011',
        '88888888', 174, '2014-3-3');
INSERT INTO userTbl VALUES('JKW', '조관우', 1965, '경기', '018',
        '99999999', 172, '2010-10-10');
INSERT INTO userTbl VALUES('BBK', '바비킴', 1973, '서울', '010',
        '00000000', 176, '2013-5-5');
GO
​
-- buyTbl
INSERT INTO buyTbl VALUES('KBS', '운동화', NULL, 30, 2);
INSERT INTO buyTbl VALUES('KBS', '노트북', '전자', 1000, 1);
INSERT INTO buyTbl VALUES('JYP', '모니터', '전자', 200, 1);
INSERT INTO buyTbl VALUES('BBK', '모니터', '전자', 200, 5);
INSERT INTO buyTbl VALUES('KBS', '청바지', '의류', 50, 3);
INSERT INTO buyTbl VALUES('BBK', '메모리', '전자', 80, 10);
INSERT INTO buyTbl VALUES('SSK', '책', '서적', 15, 5);
INSERT INTO buyTbl VALUES('EJW', '책', '서적', 15, 2);
INSERT INTO buyTbl VALUES('EJW', '청바지', '의류', 50, 1);
INSERT INTO buyTbl VALUES('BBK', '운동화', NULL, 30, 2);
INSERT INTO buyTbl VALUES('EJW', '책', '서적', 15, 1);
INSERT INTO buyTbl VALUES('BBK', '운동화', NULL, 30, 2);
GO

2. SELECT 문

SELECT문은 가장 많이 사용하는 구문으로써 데이터베이스 내의 테이블에서 원하는 데이터를 추출하는 쿼리문이다.

2.1 SELECT 구문 형식

아래의 쿼리는 docs.microsoft.com에서 확인할 수 있는 SELECT문의 전체 구문이다.

-- Syntax for SQL Server and Azure SQL Database  
​
<SELECT statement> ::=    
    [ WITH { [ XMLNAMESPACES ,] [ <common_table_expression> [,...n] ] } ]  
    <query_expression>   
    [ ORDER BY { order_by_expression | column_position [ ASC | DESC ] }   
  [ ,...n ] ]   
    [ <FOR Clause>]   
    [ OPTION ( <query_hint> [ ,...n ] ) ]   
<query_expression> ::=   
    { <query_specification> | ( <query_expression> ) }   
    [  { UNION [ ALL ] | EXCEPT | INTERSECT }  
        <query_specification> | ( <query_expression> ) [...n ] ]   
<query_specification> ::=   
SELECT [ ALL | DISTINCT ]   
    [TOP ( expression ) [PERCENT] [ WITH TIES ] ]   
    < select_list >   
    [ INTO new_table ]   
    [ FROM { <table_source> } [ ,...n ] ]   
    [ WHERE <search_condition> ]   
    [ <GROUP BY> ]   
    [ HAVING < search_condition > ]

위의 전체 구문은 다양한 옵션들 때문에 복잡해 보이지만, 실질적으로 중요한 구문으로 요약하면 다음과 같다.

[ WITH { [ XMLNAMESPACES ,] [ <common_table_expression> ] } ]
SELECT select_list [ INTO new_table ]
[ FROM table_source ] [ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]

위의 요약한 구문에서 자주 사용하는 SELECT...FROM...WHERE 절만 가져오면 다음과 같이 세 줄로 나타낼 수 있다.

SELECT 컬럼이름
FROM 테이블이름
WHERE 조건

2.2 USE 구문

SELECT구문을 사용하려면 먼저 사용할 데이터베이스를 지정해야 하는 데, 이를 USE 구문을 이용해 지정 또는 변경할 수 있다.

USE 데이터베이스_이름;

예를들어, AdventureWorks2014 DB를 사용하려면 아래와 같이 쿼리 창에서 입력하면 된다.

USE AdventureWorks2014;

이렇게 USE구문을 사용해 DB를 지정해 놓으면, 다시 USE구문을 이용해 다른 DB를 사용하겠다고 지정하지 않는 이상 모든 SQL 문은 AdventureWorks2014 에서 실행된다.

2.3 SELECT와 FROM

1.1.1에서 살펴 보았듯이 SELECT 컬럼이름 FROM 테이블이름을 통해 해당 테이블에서 원하는 컬럼의 데이터를 추출할 수 있다. 컬럼이름에서 * 는 '모든 것'을 의미하며, 해당 테이블에서 모든 컬럼의 데이터를 추출한다는 의미이다.

SELECT * FROM HumanResources.Employee;

만약, 해당 테이블에서 필요한 컬럼만을 가져올 경우 아래와 같이 * 대신에 해당 컬럼이름을 적어주면 된다.

SELECT Name,GroupName FROM HumanResources.Department;

2.4 시스템 저장 프로시저를 이용해 테이블 및 컬럼 확인하기

간혹 실무에서는 워낙 많은 DB와 테이블로 인해 찾고자 하는 DB와 테이블, 그리고 컬럼 명이 헷갈리거나 기억이 나지 않을 때가 있다. 이때 SQL Server가 제공하는 저장 프로시저를 이용해 확인할 수 있는 데, 저장 프로시저 중 sp_라고 붙은 것들은 SQL Server가 제공하는 시스템 저장 프로시저이다.

다음의 예제는 저장 프로시저를 이용해 1.1.3의 쿼리에서 처럼, HumanResources.Department 테이블과 Name, GroupName 컬럼을 찾는 예제이다.

1) 쿼리 창을 연 뒤 아래와 같이 sp_helpdb 프로시저를 이용해 어떠한 DB가 있는지 확인한다.

EXECUTE sp_helpdb;

2) USE 문을 이용해 AdventureWorks2014 DB를 지정한다.

USE AdventureWorks2014;

3) sp_tables 를 이용해 AdventureWorks2014 DB의 테이블들을 확인한다.

EXECUTE sp_tables @table_type="'TABLE'";

4) sp_columns를 이용해 HumanResources.Department테이블에 어떠한 컬럼들이 있는 확인한다.

EXECUTE sp_columns
    @table_name = 'Department',
    @table_owner = 'HumanResources';

2.5 SELECT ... FROM ... WHERE

2.5.1 기본적인 WHERE 절

WHERE절은 데이터를 조회할 때 특정한 조건을 주고, 조건에 맞는 데이터만 추출할 때 사용한다.

SELECT 컬럼이름 FROM 테이블이름 WHERE 조건식;

아래의 예제는 '1. 실습에 필요한 DB 및 테이블 만들기'에서 만든 sqlDB의 userTbl을 이용해 name='김경호'인 고객을 찾는 쿼리문이다.

SELECT * FROM userTbl WHERE name='김경호';

2.5.2 조건 연산자 사용

WHERE 절에는 =, <, >, <=, >=, != 등과 같은 조건 연산자를 사용하여 조건식을 만들 수 있다. 아래의 예제는 birthYear > 1980이상인 고객을 찾는 쿼리문이다.

SELECT * FROM userTbl WHERE birthYear > 1980;

2.5.3 관계 연산자 사용

WHERE 절에서는 NOT, AND, OR 등과 같은 관계 연산자를 사용할 수 있다. 아래의 예제는 birthYear >= 1970 OR height >= 182인 고객을 찾는 쿼리문이다.

SELECT * FROM userTbl WHERE birthYear >= 1970 OR height >= 182;

2.5.4 BETWEEN ... AND, IN(), LIKE

BETWEEN...AND 는 숫자와 같은 연속적인 값에서 특정 범위에 부합하는 데이터를 가져오는 데 사용할 수 있다. 아래의 예제는 키가 180 ~ 183 인 고객을 찾는 쿼리문이다.

-- SELECT * FROM userTbl WHERE height >= 180 AND height <= 183;
SELECT * FROM userTbl WHERE height BETWEEN 180 AND 183;

만약 컬럼의 데이터 형식이 문자열이나 이산적인(Discrete)한 데이터를 찾을 때는 BETWEEN...AND을 사용할 수 없다. 이러한 경우에는 IN()을 사용할 수 있다. 아래의 예제는 '경남', '전남', '경북'인 고객을 찾는 쿼리문이다.

-- SELECT name, addr FROM userTbl WHERE addr='경남' OR addr='전남' OR addr='경북';
SELECT name, addr FROM userTbl WHERE addr IN ('경남', '전남', '경북');

LIKE는 특정 문자열이 지정된 패턴과 일치하는 데이터를 찾을 때 사용한다. 아래의 예제는 성이 '김'씨인 고객을 찾는 쿼리문이다. LIKE '김%'는 문자열의 첫 조건은 '김'이고 %는 그 뒤의 문자는 어떤 것이든 허용한다는 의미이다.

SELECT name, height FROM userTbl WHERE name LIKE '김%';

LIKE는 % 외에도 다양한 패턴을 사용할 수 있는데 자세한 내용은 docs.microsoft.com을 참고하면 된다. 만약 한 글자와 매치하려면 _를 사용할 수 있다. 아래의 예제는 이름이 '종신'인 사람을 찾는 쿼리문이다.

SELECT name, height FROM userTbl WHERE name LIKE '_종신';

2.5.5 ANY/ALL/SOME, 하위쿼리(SubQuery, 서브쿼리)

서브쿼리는 쿼리문 안에 쿼리문이 들어 있는 것을 말한다. 예를 들어 '김경호' 보다 키가 큰 고객을 조회한다고 할 때, WHERE 절에 '김경호'의 키 '177'을 직접 써줄 수 있다.

SELECT name, height FROM userTbl WHERE height > 177;

하지만, 이렇게 직접 '김경호'의 키를 입력하는 것은 미리 '김경호'의 키가 177이라는 것을 알고 있어야 한다. 이러한 경우에 서브쿼리를 이용해 '김경호'의 키를 조회하여 WHERE 절에 넣어줄 수 있다.

SELECT name, height FROM userTbl
    WHERE height > (SELECT height FROM userTbl WHERE name = '김경호');  -- 177

이번에는 서브쿼리를 이용해 지역이 '경남'인 사람의 키보다 크거나 같은 고객들을 조회해보자.

SELECT name, height FROM userTbl
    WHERE height > (SELECT height FROM userTbl WHERE addr = '경남');  -- 170, 173

위의 쿼리를 실행하게 되면 다음과 같은 에러가 난다.

메시지 512, 수준 16, 상태 1, 줄 1
하위 쿼리에서 값을 둘 이상 반환했습니다. 하위 쿼리 앞에 =, !=, <, <=, >, >= 등이 오거나 하위 쿼리가 하나의 식으로 사용된 경우에는 여러 값을 반환할 수 없습니다.

그 이유는 SELECT height FROM userTbl WHERE addr = '경남'이 170과 173이라는 두 개의 값을 반환하기 때문에 발생하는 에러이다.

이러한 경우 ANY/SOME/ALL를 사용해 해결할 수 있다. ANY와 SOME은 동일한 기능을 한다. 아래의 예제들을 통해 ANY와 ALL의 차이를 알아보도록 하자.

ANY를 이용해 위의 쿼리를 실행하면 다음과 같은 결과가 나온다.

SELECT name, height FROM userTbl
    WHERE height > ANY (SELECT height FROM userTbl WHERE addr = '경남');  -- 173, 170

위의 결과에서도 알 수 있듯이 ANY를 사용하면 키가 170보다 큰 고객이 출력되는 것을 알 수 있다. 따라서, ANY는 SELECT height FROM userTbl WHERE addr = '경남'이 반환하는 170과 173 중에서 하나라도 만족하는 값을 가져오기 때문에 170 이상인 고객이 출력되는 것이다.

ALL은 ANY와는 다르게 반환하는 값들 모두를 만족하는 데이터를 가져온다. 따라서, 170과 173을 모두 만족하는 173보다 큰 고객들을 가져온다.

SELECT name, height FROM userTbl
    WHERE height > ALL (SELECT height FROM userTbl WHERE addr = '경남');

2.6 ORDER BY

ORDER BY 결과를 출력할 때 출력되는 순서를 오름차순/내림차순으로 조절할 수 있는 구문이다. 아래의 예제는 고객들을 가입한 순서로 출력하는 쿼리이다.

SELECT name, mDate FROM userTbl ORDER BY mDate;

ORDER BY는 기본값(default)로 오름차순(ASC)으로 설정되어 있으므로, 만약 내림차순으로 정렬하려면 아래와 같이 DESC를 추가해주면 된다.

SELECT name, mDate FROM userTbl ORDER BY mDate DESC;

ORDER BY 구문은 WHERE절 뒤에 위치해야한다. 아래의 예제는 키가 173 이상인 고객을 가입을 순으로 정렬한 쿼리이다.

SELECT name, height, mDate 
FROM userTbl 
WHERE height >= 173
ORDER BY mDate;

OBER BY 절은 SQL Server의 성능을 떨어뜨릴 수 있기 때문에 꼭 필요한 경우가 아니라면 되도록 사용하지 않는 것이 좋다.

2.7 DISTINCT, TOP(N), TABLESAMPLE 절

2.7.1 DISTINCT

DISTINCT는 해당 컬럼의 중복된 데이터는 제외하고 유니크(unique)한 데이터만 가져오는 구문이다. Python의 set 자료형과 비슷하다고 할 수 있다. 아래의 예제는 DISTINCT를 이용해 userTbl의 addr 컬럼의 중복을 제외한 데이터를 가져오는 쿼리이다.

SELECT DISTINCT addr FROM userTbl;

2.7.2 TOP

TOP(N)은 SELECT 구문에서 상위 N개만 출력하는 구문이다. 아래의 예제는 AdventureWorks2014 데이터베이스에서 Sales.CreditCard 테이블에서 만기일이 얼마 남지 않은 'Vista' 카드의 CreditCardID 상위 10개만 출력하는 쿼리이다.

USE AdventureWorks2014;
SELECT TOP(10) CreditCardID FROM Sales.CreditCard
    WHERE CardType = 'Vista'
    ORDER BY ExpYear, ExpMonth;

TOP 구문은 변수, 수식을 사용할 수 있는 데, 아래의 예제는 전체 중에서 상위 0.1%만 출력하는 쿼리문이다.

SELECT TOP(0.1) PERCENT CreditCardID FROM Sales.CreditCard
    WHERE CardType = 'Vista'
    ORDER BY ExpYear, ExpMonth;

2.7.3 TABLESAMPLE

TABLESAMPLE은 출력될 테이블의 데이터 중에서 특정 개수만큼 샘플링(sampling)해서 추출하는 구문이다. 아래의 예제는 AdventureWorks2014 DB의 Sales.SalesOrderDetail 테이블에서 5%를 샘플링해서 추출하는 쿼리이다.

SELECT * FROM Sales.SalesOrderDetail TABLESAMPLE(5 PERCENT);

2.8 OFFSET과 FETCH NEXT

2.8.1 OFFSET

OFFSET은 데이터를 반환하기 전에 건너뛸 행 수를 지정하는 구문이다. OFSSET은 SQL Server 2012에서 새롭게 추가된 기능이다. OFFSET을 사용하기 위해서는 반드시 ORDER BY 절과 함께 사용이 가능하다. 아래의 예제는 sqlDB의 userTbl테이블에서 birthYear로 오름차순 정렬 후 OFFSET을 이용해 4번째 행까지 건너뛰고 5번째 행부터 출력하는 쿼리문이다.

USE sqlDB;
SELECT userID, name, birthYear FROM userTbl
    ORDER BY birthYear
    OFFSET 4 ROWS;

2.8.2 FETCH NEXT

FETCH NEXT는 OFFSET 에서 출력될 데이터 행의 개수를 지정하는 구문이다. 아래의 예제는 2.8.1에서 3번째 행까지만 출력하도록 하는 쿼리문이다.

USE sqlDB;
SELECT userID, name, birthYear FROM userTbl
    ORDER BY birthYear
    OFFSET 4 ROWS
    FETCH NEXT 3 ROWS ONLY;

2.9 SELECT INTO

SELECT INTO 구문은 테이블을 복사할 때 주로 사용되며, 사용 방법은 다음과 같다.

SELECT 복사할컬럼명 INTO 새로운테이블명 FROM 기존테이블

아래의 예제는 sqlDB의 buyTbl테이블의 userID, prodName컬럼을 buyTbl2로 복사하는 쿼리문이다.

위의 그림에서도 확인할 수 있듯이, buyTbl의 Primary Key와 Foreign Key와 같은 제약 조건은 buyTbl2에 복사되지 않는것을 확인할 수 있다.

3. 집계 함수, GROUP BY, HAVING,

GROUP BY와 HAVING 절은 SELECT 문에서 사용되며, 아래의 형식과 같다.

[ WITH { [ XMLNAMESPACES ,] [ <common_table_expression> ] } ]
SELECT select_list [ INTO new_table ]
[ FROM table_source ] [ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]

GROUP BY 절과 HAVING절은 집계 함수와 같이 사용되기 때문에 집계함수에 대해 알아야 한다. 이번에는 집계 함수, GROUP BY, HAVING절에 대해 알아보도록 하자.

3.1 집계 함수 (Aggregate Function)

집계 함수는 주로 GROUP BY 절과 함께 쓰이며 각 컬럼의 집합에 대한 특정 계산(합계, 평균 등)을 수행하고 값을 반환하며, 자주 사용하는 집계함수는 다음과 같다.

함수 명	설명
AVG()	평균을 구함
MIN()	최소값을 구함
MAX()	최대값을 구함
COUNT()	데이터의 개수를 셈
COUNT_BIG()	데이터의 개수를 셈, 단 결과값 bigint형임
STDEV()	표준편차를 구함
VAR()	분산을 구함

아래의 예제는sqlDB의 buyTbl 테이블에서 amount 컬럼의 평균을 구한 뒤 '평균구매개수'를 집계함수의 AVG를 이용해 구하는 쿼리문이다. 쿼리문에서 AVG(amount) as [평균구매개수]는 amount의 평균을 구한 뒤 반환된 값을 as를 통해 [평균구매개수]로 부르겠다는 의미이며, 이를 별칭(alias)라 한다.

SELECT AVG(amount) as [평균구매개수] FROM buyTbl;

3.2 GROUP BY

GROUP BY절은 하나 이상의 컬럼을 기준으로 그룹화 하는 역할을 한다. GROUP BY 절은 위에서 살펴봤듯이, 주로 집계 함수와 같이 사용된다. 아래의 예제는 buyTbl에서 고객(userID)별로 제품을 구매한 개수를 GROUP BY 절과 집계함수의 SUM()을 이용해서 구한 쿼리이다.

SELECT userID, SUM(amount) as [총구매개수] 
FROM buyTbl
GROUP BY userID;

3.3 HAVING 절

HAVING 절을 설명하기 전에 먼저, 다음의 쿼리문을 보자. 아래의 쿼리문은 총 구매액이 1000 이상인 고객을 조회하는 쿼리문으로써 WHERE절에 집계함수를 사용해 검색조건을 지정해줬다.

SELECT userID AS [사용자], SUM(price*amount) AS [총구매액]
    FROM buyTbl
    WHERE SUM(price*amount) > 1000
    GROUP BY userID;

하지만, 위의 쿼리문을 실행할 경우 아래와 같은 에러 메시지가 나타난다.

집계가 HAVING 절이나 SELECT 목록에 포함된 하위 쿼리 내에 없으면 WHERE 절에 나타날 수 없습니다. 또한 집계 중인 열은 외부 참조입니다.

위의 에러메시지를 보면 집계 함수는 WHERE절에서 사용할 수 없다는 에러 문구를 확인할 수 있다. 따라서, WHERE 절 대신에 다른 것을 사용해야 하는데 그것이 바로 HAVING 절이다.

HAVING 절은 그룹 또는 집계함수에 대한 검색 조건을 지정하는 역할을 하는데 HAVING절은 반드시 GROUP BY 절 뒤에 나와야 한다. 위의 쿼리문을 HAVING절을 이용해 나타내면 아래와 같이 나타낼 수 있다.

SELECT userID AS [사용자], SUM(price*amount) AS [총구매액]
    FROM buyTbl
    GROUP BY userID
    HAVING SUM(price*amount) > 1000
    ORDER BY SUM(price*amount);

3.4 ROLLUP()/GROUPING_ID()/CUBE() 함수

3.4.1 ROLLUP()

총합 또는 중간 합계가 필요할 경우 GROUP BY 절과 함께 ROLLUP을 사용하면 된다. 아래의 쿼리문은 ,buyTbl에서 groupName별로 합계 및 총합을 구하는 쿼리문이다.

SELECT groupName, SUM(price*amount) AS [비용]
    FROM buyTbl
    GROUP BY ROLLUP (groupName);

3.4.2 GROUPING_ID()

GROUPING_ID()는 조회된 결과가 데이터인지 아니면 집계함수에 의한 결과값인지 구분해주는 역할을 하며, GROUPING ID()의 결과가 0이면 데이터를 의미, 1이면 집계함수의 결과로 인해 추가된 것이다. 아래의 쿼리는 3.4.1에서 GROUPING_ID()를 추가해 해당 row가 집계함수에 의해 나온 값인지 확인하는 쿼리이다.

SELECT groupName, SUM(price*amount) AS [비용], 
    GROUPING_ID(groupName) AS [추가행여부]
    FROM buyTbl
    GROUP BY ROLLUP (groupName);

3.4.3 CUBE()

CUBE()는 3.4.1의 ROLLUP()과 비슷한 역할을 하지만, CUBE()가 다차원의 데이터를 요약하는데 더 적합하다. 예를들어 아래의 데이터와 같은 cubeTbl 테이블이 있다고 가정하자.

제품	색상	수량
컴퓨터	검정	11
컴퓨터	파랑	22
모니터	검정	33
모니터	파랑	44

USE sqlDB;
CREATE TABLE cubeTbl (prodName NCHAR(3), color NCHAR(2), amount INT);
GO
INSERT INTO cubeTbl VALUES('컴퓨터', '검정', 11);
INSERT INTO cubeTbl VALUES('컴퓨터', '파랑', 22);
INSERT INTO cubeTbl VALUES('모니터', '검정', 33);
INSERT INTO cubeTbl VALUES('모니터', '파랑', 44);

cubeTbl에 대해 제품별(prodName) 합계 및 색상별 합계를 조회하고 싶을 때 CUBE()를 사용할 수 있다.

SELECT prodName, color, SUM(amount) AS [수량합계]
    FROM cubeTbl
    GROUP BY CUBE (color, prodName);

4. WITH 절과 CTE

WITH 절은 CTE(Common Table Expression)을 표현하는 구문이다. CTE는 뷰(view), 파생 테이블, 임시 테이블 등을 대신할 수 있으며, 비재귀적(Non-Recursive), 재귀적(Recursive) CTE가 있다.

4.1 비재귀적 CTE

비재귀적 CTE는 단순한 형태로, 복잡한 쿼리문을 단순화 할때 사용한다. 비재귀적 CTE 형식은 다음과 같다.

WITH CTE_테이블이름(컬럼명)
AS
(
    <쿼리문>
)
SELECT 컬럼명 FROM CTE_테이블이름;

위의 형식에서 알 수 있듯이, '복잡한 쿼리문을 WITH 구문으로 감싸서, 해당 쿼리의 결과를 CTE_테이블이름으로 정의하겠다'라는 의미라고 할 수 있다.

아래의 쿼리문을 WITH절을 이용해 나타내보도록 하자. 먼저 , 아래의 쿼리문은 buyTbl에서 총구매액을 구하는 쿼리이다.

SELECT userID AS [사용자], SUM(price*amount) AS [총구매액]
    FROM buyTbl GROUP BY userID;

위의 쿼리문을 WITH절을 이용하여 나타내면 다음과 같다.

WITH abc (userID, [총구매액])
AS
    ( SELECT userID, SUM(price*amount)
        FROM buyTbl GROUP BY userID 
    )
SELECT * FROM abc;

4.1.1 중복 CTE

아래와 같이 CTE는 중복이 허용된다.

WITH
    AAA (컬럼들)
        AS ( AAA의 쿼리문 ),
    BBB (컬럼들)
        AS ( BBB의 쿼리문 ),
    CCC (컬럼들)
        AS ( CCC의 쿼리문 )
 SELECT * FROM [AAA 또는 BBB 또는 CCC]

위의 쿼리에서 BBB의 쿼리문에서는 AAA를 참조할 수 있고, CCC의 쿼리에서는 AAA, BBB를 참조할 수 있지만, 반대로 AAA에서 BBB, CCC를 참조하지 못하며, BBB에서 CCC를 참조하지 못한다. 즉, 아직 정의 되지 않은 CTE를 미리 참조할 수 없다.

다음의 예제 쿼리는 중복 CTE를 이용한 쿼리문이다.

WITH
AAA(userID, total)
    AS
        (SELECT userID, SUM(price*amount) FROM buyTbl GROUP BY userID),
BBB(sumtotal)
    AS
        (SELECT SUM(total) FROM AAA),
CCC(sumavg)
    AS
        (SELECT sumtotal / (SELECT count(*) FROM buyTbl) FROM BBB)
SELECT * FROM CCC;
​

4.2 재귀적 CTE

재귀적 CTE는 자기자신을 반복적으로 호출하는 CTE를 말한다. 재귀적 CTE의 기본 형식은 다음과 같다.

WITH CTE_테이블이름(컬럼명)
AS
(
    <쿼리문1: SELECT * FROM 테이블 A>
    UNION ALL
    <쿼리문2: SELECT * FROM 테이블 A JOIN CTE_테이블이름>
)
SELECT * FROM CTE_테이블이름;

위의 형식에서 '<쿼리문1>'을 앵커 멤버(Anchor Member: AM)이라 하고, '<쿼리문2>'를 재귀 멤버(Recursive Member:RM)라 하며, 작동원리는 다음과 같다.

1. 루틴의 최초 호출에 해당하는 <쿼리문1>을 실행하며, 기본값으 0으로 초기화한다.

2. <쿼리문2>를 실행한다. 기본값을 '기본값 + 1'로 증가시키며, SELECT의 결과가 빈것 아니라면, CTE_테이블이름을 재귀적으로 호출한다.

3. 2번을 계속 반복하며, SELECT의 결과가 아무것도 없을 경우 재귀호출이 중단된다.

4. 외부의 SELECT문을 실행해 앞 단계에서 누적된 결과(UNION ALL)를 가져온다.

4.2.1 예제 테이블 만들기

재귀적 CTE를 이해하기 위해 다음의 조직도를 테이블로 만들어 알아보자.

USE sqlDB;
CREATE TABLE empTbl (emp NCHAR(3), manager NCHAR(3), department NCHAR(3));
GO
​
INSERT INTO empTbl VALUES ('나사장', NULL, NULL);
INSERT INTO empTbl VALUES ('김재무', '나사장', '재무부');
INSERT INTO empTbl VALUES ('김부장', '김재무', '재무부');
INSERT INTO empTbl VALUES ('이부장', '김재무', '재무부');
INSERT INTO empTbl VALUES ('우대리', '이부장', '재무부');
INSERT INTO empTbl VALUES ('지사원', '이부장', '재무부');
INSERT INTO empTbl VALUES ('이영업', '나사장', '영업부');
INSERT INTO empTbl VALUES ('한과장', '이영업', '영업부');
INSERT INTO empTbl VALUES ('최정보', '나사장', '정보부');
INSERT INTO empTbl VALUES ('윤차장', '최정보', '정보부');
INSERT INTO empTbl VALUES ('이주임', '윤차장', '정보부');

이렇게 위의 트리구조의 조직도를 테이블 형태로 나타내게 되면, 테이블만 봤을 때는 위와 같은 조직도를 생각해내기에는 어렵다. 이때 재귀적 CTE를 통해 각 직급별 레벨(level)을 붙여주면 조직도를 파악할 수 있다.

4.2.2. 재귀적 CTE

4.2.1 에서 만든 empTbl 테이블을 이용해 각 직급별 level을 붙여주는 재귀적 CTE를 만들어 보자.

WITH empCTE (empName, mgrName, dept, level)
AS
(
    SELECT emp, manager, department, 0
        FROM empTbl
        WHERE manager IS NULL  -- 나사장
    UNION ALL
    SELECT AA.emp, AA.manager, AA.department, BB.level+1
        FROM empTbl AS AA INNER JOIN empCTE AS BB
         ON AA.manager = BB.empName
)
SELECT * FROM empCTE ORDER BY dept, level;

5. 마무리

이번 포스팅에서는 T-SQL의 기본인 SELECT문에 대해 알아보았다. SELECT문은 기본이지만 실무에서도 정말 많이 쓰이는 구문이기 때문에 반드시 알고있어야 한다.