기본적으로 알아야 할 명령어

1. 기본적으로 알아야 할 명령어 • 사용하는 데이터베이스 변경하기 • --USE 키워드 뒤에 데이터베이스 명을 적어서 현재 세션의 데이터베이스를 • --변경할 수 있다. • USE Northwind • go • USE Master • go란 무엇인가? • go는 하나의 배치 작업을 구분한다. • 여러 개의 SQL문에 대해 go가 없다면 이 모든 문장은 SQL Server에서 • 함께 처리한다. <<보충>> sqlhwan@hotmail.com

2. SELECT문장 - 1 • SELECT문은 데이터베이스 내의 원하는 데이터를 추출 할 수 있는 문장 • 기본구문 : Northwind 데이터베이스내의 Customers테이블을 조회하기 • SELECT * • FROM Customers • 조건주기 : CustomerID가 ALFKI인 데이터만 조회하기 • SELECT * • FROM Customers • WHERE CustomerID = 'ALFKI' • 특정 컬럼만 조회 : Customers에서 CustomerID, CompanyName만 조회하기 • SELECT CustomerID, CompanyName • FROM Customers • 테이블 별칭 지정 : Customers테이블에 A라는 별칭을 설정해서 조회하기 • --as의 사용 • SELECT * • FROM Customers as A • go • --as의 생략 • SELECT * • FROM Customers A sqlhwan@hotmail.com

3. SELECT문장 - 2 • 컬럼 별칭 지정 : Customers에서 CustomerID를 CustID별칭으로 조회 • --as 사용하기 • SELECT CustomerID as CustID • FROM Customers • go • --as 생략하기 • SELECT CustomerID CustID • FROM Customers • go • --as 생략하고 = 사용하기 • SELECT CustID = CustomerID • FROM Customers • []의 사용 : Order Details 테이블 조회 • --에러발생 • SELECT * FROM Order Details • go • --빈칸이 포함된 컬럼명에도 동일하게 적용된다. • SELECT * FROM [Order Details] sqlhwan@hotmail.com

4. SELECT문장 - 3 • INTO사용 : SELECT INTO를 사용해서 Customers를 Custs로 복사 • SELECT * INTO • FROM Customers • go • SELECT * FROM Custs • DISTINCT사용 : SELECT에 열거된 컬럼들의 중복되는 행 제거하기 • SELECT DISTINCT City, Country • FROM Custs • FROM절의 생략 : 일반 상수, 함수의 조회 • --현재 시간 • SELECT GETDATE() • go • --수식 계산 • SELECT (3 * 4) + (6 * 2) sqlhwan@hotmail.com

5. WHERE절의 활용 - 1 • WHERE절은 테이블내의 데이터들을 걸러내는 역할을 한다. • WHERE 사용 : Customers에서 City가 London인 데이터만 조회 • SELECT * • FROM Customers • WHERE City = 'London' • 같지 않은 조건 : Customers에서 CustomerID가 ALFKI가 아닌 데이터 조건 • SELECT * • FROM Customers • WHERE CustomerID <> 'ALFKI' • OR 사용 : Customers에서 City가 London이거나 Madrid인 데이터만 조회 • SELECT * • FROM Customers • WHERE City = 'London' OR City = 'Madrid' • AND 사용 : Customers에서 Country가 Mexico이고 PostalCode가 05033인 • 데이터 조회 • SELECT * • FROM Customers • WHERE Country = 'Mexico' • AND PostalCode = '05033' sqlhwan@hotmail.com

6. WHERE절의 활용 - 2 • AND와 OR의 혼합 사용 : Customers에서 City가 London이거나 Madrid이고 • ContactTitle이 Sales Manager인 데이터 조회 • --원치 않는 결과 • SELECT * • FROM Customers • WHERE City = 'London' OR City = 'Portland' • AND ContactTitle = 'Sales Manager' • go • --원치 않는 결과 • SELECT * • FROM Customers • WHERE ContactTitle = 'Sales Manager' • AND City = 'London' OR City = 'Portland' • go • --OR절과 AND절이 혼합으로 사용될 때는 괄호를 사용한다. • SELECT * • FROM Customers • WHERE (City = 'London' OR City = 'Portland') • AND ContactTitle = 'Sales Manager' • --사유 : SQL Server는 AND절을 묶어서 같이 처리하고 나중에 OR절을 처리 • -- AND와 OR가 있을 때는 WHERE절의 순서가 처리 방법에 영향을 준다. • -- 올바른 처리를 위해 괄호를 꼭 사용하도록 한다. sqlhwan@hotmail.com

7. WHERE절의 활용 – 3 • 비교연산자 사용 : Customers에서 CustomerID가 BLAUS인 데이터 조회 • SELECT * • FROM Customers • WHERE CustomerID = 'BLAUS' • 비교연산자 사용 : Orders에서 Freight가 50이상인 데이터 조회 • SELECT * • FROM Orders • WHERE Freight >= 50 • 비교연산자 사용 : Orders에서 Freight가 50미만인 데이터 조회 • SELECT * • FROM Orders • WHERE Freight < 50 • 비교연산자 사용 : Orders에서 Freight가 50이상 80이하인 데이터 조회 • SELECT * • FROM Orders • WHERE Freight >= 50 AND Freight <= 80 • BETWEEN 사용 : Orders에서 Freight가 50이상 80이하인 데이터 조회 • SELECT * • FROM Orders • WHERE Freight BETWEEN 50 AND 80 sqlhwan@hotmail.com

8. WHERE절의 활용 - 4 • 조건의 부정 : Customers에서 CustomerID가 ALFKI가 아닌 데이터 검색 • --조건에 대한 부정은 조건 앞에 NOT을 붙이면 된다. • SELECT * • FROM Customers • WHERE NOT CustomerID = 'ALFKI‘ • 조건의 부정 : Orders에서 Freight가 50 이상이 아닌 데이터 조회 • SELECT * • FROM Orders • WHERE NOT Freight >= 50 • go • --위의 쿼리는 Freight가 50미만인 데이터만 조회하는 것과 동일한다. • SELECT * • FROM Orders • WHERE Freight < 50 • 수식의 사용 : Orders에서 OrderID가 6의 배수인 데이터 조회 • SELECT * FROM Orders • WHERE OrderID % 6 = 0 • --※ 검색컬럼에 수식 연산이 오면 해당 컬럼의 인덱스를 사용할 수 없다. sqlhwan@hotmail.com

9. WHERE절의 활용 - 5 • LIKE 사용 : Customers에서 CustomerID가 C로 시작하는 데이터 조회 • --%는 모든 문자들을 나타낸다. • SELECT * • FROM Customers • WHERE CustomerID LIKE 'C%' • LIKE 사용 : Customers에서 CustomerID내에 OM이 존재하는 데이터 조회 • SELECT * • FROM Customers • WHERE CustomerID LIKE '%OM%' • --※ 첫문자에 검색패턴(%)이 오면 해당 컬럼의 인덱스를 사용할 수 없다. • LIKE 사용 : Customers에서 CustomerID의 두번째 글자가 O인 데이터 조회 • --_는 한 문자내에서 모든 문자가 올 수 있음을 나타낸다. • SELECT * • FROM Customers • WHERE CustomerID LIKE '_O%' • LIKE 사용 : Customers에서 CusomterID의 첫번째 글자가 A나 D인 데이터 • --[]안의 문자는 한 문자로서 열거된 문자중에 해당하는 값만 찾는다. • SELECT * • FROM Customers • WHERE CustomerID LIKE '[AD]%' sqlhwan@hotmail.com

10. WHERE절의 활용 - 6 • LIKE 사용 : Customers에서 CustomerID의 두번째 글자가 A부터 D사이인 • 데이터 • --[]안의 문자 사이에 –는 문자의 범위를 지정한다. • SELECT * • FROM Customers • WHERE CustomerID LIKE '_[A-D]%' • LIKE 사용 : Customers에서 CustomerID의 두번째 글자가 O가 아닌 데이터 • SELECT * • FROM Customers • WHERE CustomerID LIKE '_[^O]%' • --※ 첫문자에 검색패턴(_)이 오면 해당 컬럼의 인덱스를 사용할 수 없다. • LIKE 사용 : Customers에서 CusomterID의 두번째 글자가 N 네번째 글자가 • U인 데이터 조회 • SELECT * • FROM Customers • WHERE CustomerID LIKE '_N_U%' • LIKE의 부정 : Customers에서 CustomerID의 두번째 글자가 O가 아닌 모든 • 데이터 • SELECT * • FROM Customers • WHERE CustomerID LIKE '_[O]%' sqlhwan@hotmail.com

11. WHERE절의 활용 - 7 • IN의 사용 : Customers에서 City가 London, Madrid, Bern인 데이터 조회 • SELECT * • FROM Customers • WHERE City IN ('London', 'Madrid', 'Bern') • NULL의 검색 : Customers에서 Region이 NULL인 데이터 검색 • --NULL의 검색은 IS NULL연산을 사용한다. • SELECT * • FROM Customers • WHERE Region IS NULL • NULL의 검색 : Customers에서 Region이 NULL이 아닌 데이터 검색 • --NULL이 아닌 데이터 검색은 IS NOT NULL연산을 사용한다. • SELECT * • FROM Customers • WHERE Region IS NOT NULL sqlhwan@hotmail.com

12. WHERE절의 활용 - 8 • 여러가지 조건들의 혼합 • SELECT * • FROM Customers as A • WHERE A.City IN ('London', 'Madrid', 'Bern') • AND (A.CustomerID LIKE '[CE]%' OR A.Country = 'Spain') • AND A.CompanyName >= 'E' sqlhwan@hotmail.com

13. ORDER BY절의 사용 - 1 • ORDER BY는 데이터를 정렬하기 위해 사용된다. • ORDER BY는 DESC(내림차순)와 ASC(오름차순)의 두 가지 정렬 방법이 있다 • ASC, DESC를 표시하지 않으면 기본적으로 ASC로 정렬이 된다. • ORDER BY 사용하기 : Customers테이블을 CustomerID순으로 정렬 • SELECT * • FROM Customers • ORDER BY CustomerID • ORDER BY 사용하기 : Customers에서 Country이름이 A로 시작하는 데이터 • 에 대해서 Country는 내림차순, City는 오름차순, CustomerID는 내림차순 • 으로 정렬 • SELECT Country, City, CustomerID, * • FROM Customers • WHERE Country LIKE 'A%' • ORDER BY Country DESC, City ASC, CustomerID DESC sqlhwan@hotmail.com

14. ORDER BY절의 사용 - 2 • ORDER BY 사용하기 : Customers에서 Country와 City를 붙여서 하나의 • 컬럼으로 조회하고 나머지 정보들을 조회 • SELECT A.Country + '' + A.City as 'CountryCity', • A.* • FROM Customers as A • ORDER BY A.Country + A.City • go • --또는 다음과 같이 SELECT되는 곳의 위치를 숫자로 지정할 수 있다. • --위치는 SELECT절의 왼쪽부터 1로 시작된다. • SELECT A.Country + '' + A.City as 'CountryCity', • A.* • FROM Customers as A • ORDER BY 1 sqlhwan@hotmail.com

15. INNER JOIN의 사용 - 1 • INNER JOIN은 두개의 테이블에 대해 조건에 만족하는 결과들끼리 결합시 • 키는 방법이며 일반적인 JOIN을 뜻한다. • 값이 같은 데이터끼리 결합 : Customers와 Orders에서 CustomerID가 같은 • 데이터끼라 결합 • --오류 : 두 개의 테이블 모두 CustomerID가 있으므로 테이블명이나 별칭 입력필수 • SELECT CustomerID, OrderID • FROM Customers JOIN Orders • ON Customers.CustomerID = Orders.CustomerID • go • --FROM절 뒤에 조인될 테이블들이 오고 두 테이블 사이에 JOIN문장이 온다. • --그 뒤에 ON을 적어주고 JOIN의 조건을 열거한다. • --만약에 SELECT *를 하게 된다면 두테이블의 모든 결과가 출력된다. • SELECT Customers.CustomerID, Orders.OrderID • FROM Customers JOIN Orders • ON Customers.CustomerID = Orders.CustomerID • go • --Customers쪽의 결과만 보고 싶다면 Customers.*를 하도록 한다. • --별칭을 사용한다면 더 쉽게 사용할 수 있다. • SELECT A.*, B.EmployeeID • FROM Customers as A JOIN Orders as B • ON A.CustomerID = B.CustomerID sqlhwan@hotmail.com

16. INNER JOIN의 사용 - 2 • Customers와 Orders에서 CustomerID가 같은 데이터끼리 결합 이해하기 Customers as A Orders as B JOIN Condition : ON A.CustomerID = B.CustomerID JOIN Result • 양쪽 테이블에서 같은 CustomerID를 • 가진 데이터들끼리 결합한다. • A테이블의 PARIS는 B에 없으므로 조인 • 후 사라지게 된다. • CustomerID가 ALFKI인 데이터는 A에는 • 한 건, B에는 두 건이므로 조인 시 A • 쪽의 데이터가 두 건이 되어 B와 동일 • 한 레코드 수를 만들어 결합하게 된다. sqlhwan@hotmail.com

17. INNER JOIN의 사용 - 3 • 세 개의 테이블의 결합 : Customers와 Orders에서 CustomerID가 동일한 • 데이터를 Orders와 Order Details에서 OrderID가 동일한 데이터끼리 결합 • SELECT A.CustomerID, B.OrderID, C.ProductID • FROM Customers A JOIN Orders B • ON A.CustomerID = B.CustomerID • JOIN [Order Details] C • ON B.OrderID = C.OrderID • --세 개의 테이블 결합은 두 개의 테이블 결합이 두 번 일어나는 것과 동일 • --먼저 A와 B가 결합되고 해당 결합 결과를 하나의 테이블로 생각한다. • --A와 B의 결합결과 테이블에 다시 C의 테이블이 결합된다고 생각하면 된다. • 여러 테이블의 결합 : 세 개의 테이블을 결합 한 것처럼 한 개씩의 테이 • 블을 차례로 더 해 간다면 쉽게 수행 할 수 있다. • JOIN이 필요한 이유 : 관계형 데이터베이스에서는 각 테이블에 독립된 • 정보들을 담고 있고 해당 정보들을 원래의 정보로 조합하기 위해서는 각 • 테이블들의 PK(Primary Key)와 FK(Foreign Key)를 이용해 정보를 조합하 • 는데 이 때 데이터들을 조합하기 위해 테이블들에 JOIN을 사용해야 한다. sqlhwan@hotmail.com

18. INNER JOIN의 사용 - 4 • JOIN의 사용 : Orders테이블과 Employees테이블을 EmployeeID가 동일한 • 데이터끼리 조인을 해서 Orders의 OrderID와 Employees의 FirstName이 • 나오도록 한다. • SELECT A.OrderID, B.FirstName • FROM Orders A JOIN Employees B • ON A.EmployeeID = B.EmployeeID • JOIN과 WHERE의 동시 사용 : Employees 중에서 FirstName이 M으로 시작하 • 는 모든 직원들이 접수한 Orders내용을 조회 • SELECT A.* • FROM Orders A JOIN Employees B • ON A.EmployeeID = B.EmployeeID • WHERE B.FirstName LIKE 'M%' sqlhwan@hotmail.com

19. OUTER JOIN의 사용 - 1 • OUTER JOIN은 두개의 테이블중에 어느 한쪽의 결과는 모두 나오도록 테이 • 블들을 결합하는 방법이다. • OUTER JOIN을 수행하면 모든 결과가 나오게 되어 있는 쪽의 테이블의 • 데이터들과 매치되지 않는 반대편쪽의 데이터는 NULL값으로 결합된다. • LEFT OUTER JOIN의 수행 : Customers의 모든 데이터에 대해 Orders 정보 • 를 보여주도록 LEFT OUTER JOIN을 수행 • --OUTER JOIN 앞에 명시된 LEFT는 OUTER JOIN연산자를 기준으로 왼쪽의 • --테이블이 주체가 된다는 것을 뜻한다. 그러므로 WHERE조건으로 데이터를 • --거르지 않는 한 Customers의 데이터는 모두 나오게 된다. • SELECT * • FROM Customers A LEFT OUTER JOIN Orders B • ON A.CustomerID = B.CustomerID • go • --다음의 문장을 수행해서 위의 LEFT OUTER JOIN의 결과와 결과행수를 비교 • SELECT * • FROM Customers A JOIN Orders B • ON A.CustomerId = B.CustomerID • --첫 번째 쿼리의 결과행수는 832이고 두 번째 쿼리의 결과행수는 830이다. • --이는 Customers의 CustomerID중에 주문을 한 건도 하지 않은 CustomerID가 • --있음을 말한다. sqlhwan@hotmail.com

20. OUTER JOIN의 사용 - 2 • RIGHT OUTER JOIN의 수행 : Customers의 모든 데이터에 대해 Orders 정보 • 를 보여주도록 RIGHT OUTER JOIN을 수행 • --OUTER JOIN 앞에 명시된 RIGHT는 OUTER JOIN연산자를 기준으로 오른쪽의 • --테이블이 주체가 된다는 것을 뜻한다. • SELECT * • FROM Orders A RIGHT OUTER JOIN Customers B • ON A.CustomerID = B.CustomerID • --Customers가 RIGHT OUTER JOIN의 오른쪽에 있으므로 모든 결과가 나오게 된다. • FULL OUTER JOIN의 수행 : Customers와 Orders에 대해 FULL OUTER JOIN을 • 수행 • --FULL OUTER JOIN은 FULL OUTER JOIN연산자의 양쪽의 모든 테이블들이 • --주체가 된다. • SELECT * • FROM Customers A FULL OUTER JOIN Orders B • ON A.CustomerID = B.CustomerID • OUTER JOIN과 INNER JOIN이 한 문장에 여러 번 사용해야 할 때도 하나씩 • 결합해 간다면 원하는 결과를 쉽게 추려 낼 수 있을 것이다. sqlhwan@hotmail.com

21. OUTER JOIN의 사용 - 3 • LEFT, RIGHT, FULL OUTER JOIN의 수행 결과 Subjects as B Students as A LEFT OUTER JOIN RIGHT OUTER JOIN FULL OUTER JOIN sqlhwan@hotmail.com

22. OUTER JOIN의 사용 - 4 • OUTER JOIN과 INNER JOIN이 한 문장에 여러 번 사용해야 할 때도 하나씩 • 결합해 간다면 원하는 결과를 쉽게 추려 낼 수 있을 것이다. • OUTER JOIN은 언제 사용되어 지나? OUTER JOIN은 주로 데이터 집계시 존 • 재하지 않는 집계를 0건으로 집계하기 위해 사용되어 진다. sqlhwan@hotmail.com

23. CROSS JOIN의 사용 - 1 • CROSS JOIN은 두 테이블간에 조건 없는 결합을 만들게 된다. • CROOS JOIN을 사용하게 되면 두 테이블의 건수를 곱한 만큼의 결과가 • 만들어 진다. • 한쪽 테이블의 한 건에 데이터에는 반대쪽 테이블의 데이터 건수만큼의 • 데이터가 결합되어 진다. • CROSS JOIN의 수행 : Customers테이블과 Orders테이블을 CROSS JOIN • --TOP 10은 결과의 데이터 건수를 10건으로 제한하게 된다. • --만약에 TOP 10을 생략하고 수행하게 된다면 수행하는데 많은 시간이 • --걸릴 것이다. • SELECT TOP 10 * • FROM Customers A CROSS JOIN Orders B sqlhwan@hotmail.com

24. SELF JOIN의 사용 - 1 • SELF JOIN은 같은 두 개의 테이블을 결합함을 뜻하는 것이다. • 특별히 SELF JOIN 구문이 있는 것이 아니라 JOIN 연산자 좌우로 같은 • 테이블이 있으면 SELF JOIN이다. • SELF JOIN의 수행 : Customers에서 CustomerID로 SELF JOIN을 수행 • SELECT * • FROM Customers A JOIN Customers B • ON A.CustomerID = B.CustomerID • 이외로 많이 사용되는 조인의 방법으로서 특정 데이터의 순위를 구하거나 • 재귀관계의 테이블을 위해 사용되어 진다. sqlhwan@hotmail.com

25. GROUP BY와 집계함수의 사용 - 1 • GROUP BY는 데이터를 특정 그룹별로 묶도록 해준다. • GROUP BY와 집계함수들을 사용해서 특정 그룹별 집계를 수행할 수 있다. • DISTINCT역시 GROUP BY와 동일하게 중복 행을 제거하지만 데이터를 집계 • 할 수는 없다. • GROUP BY의 수행 : Customers테이블을 City별로 그룹화하기 • SELECT City • FROM Customers • GROUP BY City • 두 개 이상 컬럼 GROUP BY 수행 : Customers에서 City, ContactTitle이 • 같은 데이터끼리 그룹화 하기 • --GROUP BY의 절대 원칙은 GROUP BY에 열거된 컬럼들만이 SELECT절에 함수 • --사용 없이 올 수 있다는 것이다. • SELECT City, ContactTitle • FROM Customers • GROUP BY City, ContactTitle sqlhwan@hotmail.com

26. GROUP BY와 집계함수의 사용 - 2 • GROUP BY와 COUNT() : Customers에서 각 City별 Customer의 수 집계 • SELECT City, COUNT(*) • FROM Customers • GROUP BY City • GROUP BY와 MAX() : Orders에서 각 CustomerID별 최근의 주문일 조회 • SELECT CustomerID, MAX(OrderDate) • FROM Orders • GROUP BY CustomerID • GROUP BY와 MIN() : Orders에서 각 CustomerID별 최초의 주문일 조회 • SELECT CustomerID, MIN(OrderDate) • FROM Orders • GROUP BY CustomerID • GROUP BY와 SUM() : Orders에서 각 CustomerID별 Freight의 합 • SELECT CustomerID, SUM(Freight) • FROM Orders • GROUP BY CustomerID • GROUP BY와 AVG() : Orders에서 각 CustomerID별 Freight의 평균 • SELECT CustomerID, AVG(Freight) • FROM Orders • GROUP BY CustomerID sqlhwan@hotmail.com

27. GROUP BY와 집계함수의 사용 - 3 • GROUP BY와 집계함수의 이해하기 Orders as B GROUP BY CustID 수행 CustID별 주문수(COUNT(*)) CustID별 최근 주문일(MAX(OrderDate)) CustID별 최초 주문일(MIN(OrderDate)) CustID별 Price의 합(SUM(Price)) CustID별 Price의 평균(AVG(Price)) sqlhwan@hotmail.com

28. GROUP BY와 집계함수의 사용 - 4 • 각 고객별 주문 건수 집계하기 : Customers테이블과 Orders테이블을 사용 • SELECT A.CustomerID, COUNT(*) • FROM Customers A JOIN Orders B • ON A.CustomerID = B.CustomerID • GROUP BY A.CustomerID • go • --Northwind의 Customers를 변경하지 않았다면 실제 데이터는 총 91건이다. • --하지만 위의 결과는 89건이다. 이유는 CustomerID가 PARIS, FISSA인 • --데이터에 대해 한 건의 Order도 없기 때문이다. • --다음을 실행해서 두 건의 데이터가 없음을 확인하자. • SELECT A.CustomerID, COUNT(*) • FROM Customers A JOIN Orders B • ON A.CustomerID = B.CustomerID • WHERE A.CustomerID IN ('PARIS', 'FISSA') • GROUP BY A.CustomerID • --결과가 한 건도 없다. 이 것은 Orders 테이블에 위 두 ID에 대한 데이터가 • --한 건도 없기 때문에 두 건은 조인에 참여하지 못했기 때문이다. sqlhwan@hotmail.com

29. GROUP BY와 집계함수의 사용 - 5 • 각 고객별 주문 건수 집계하기 : OUTER JOIN을 사용하기 • --OUTER JOIN을 사용하게 되면 주문이 존재하지 않는 고객의 데이터의 • --집계도 구할 수가 있다. • SELECT A.CustomerID, COUNT(*) • FROM Customers A LEFT OUTER JOIN Orders B • ON A.CustomerID = B.CustomerID • WHERE A.CustomerID IN ('PARIS', 'FISSA') • GROUP BY A.CustomerID • go • --결과는 PARIS와 FISSA에 대해 각 1건씩이 집계 되었다. • --이는 틀린 결과로서 COUNT(*)를 했기 때문이다. • --올바른 결과를 위해서는 B쪽이 주문된 실제 내역이므로 COUNT되는 것을 • --*가 아닌 B쪽 테이블의 특정 컬럼을 이용하도록 한다. • SELECT A.CustomerID, COUNT(B.CustomerID) • FROM Customers A LEFT OUTER JOIN Orders B • ON A.CustomerID = B.CustomerID • WHERE A.CustomerID IN ('PARIS', 'FISSA') • GROUP BY A.CustomerID • --COUNT에 *가 아닌 컬럼을 넣어 주었을 때 해당 컬럼이 NULL일 경우는 0으로 집계 • --다른 집계 함수와 다른게 COUNT만이 NULL을 0으로 집계하며, 다른 SUM • --AVG, MAX, MIN등은 NULL을 집계에서 무시하게 된다. • --이는 NULL이 알 수 없는 값이기 때문이다. sqlhwan@hotmail.com

30. GROUP BY와 집계함수의 사용 - 6 • 각 집계 함수의 NULL처리 : COUNT • --UNION ALL은 데이터를 수평으로 결합할 수 있는 방법이다. • --다음 예제들은 서브쿼리와 UNION ALL이 사용되었지만 이에 대한 생각은 • --잠시 접어두고 집계함수만 보기로 하자. • SELECT * • FROM (SELECT NULL as A • UNION ALL • SELECT 5 as A • UNION ALL • SELECT 10 as A) as A • go • --결과를 수행하면 총 3건의 데이터가 있다. • --다음을 통해서 COUNT(*)와 COUNT(A)를 비교해 보자 • SELECT COUNT(*), COUNT(A) • FROM (SELECT NULL as A • UNION ALL • SELECT 5 as A • UNION ALL • SELECT 10 as A) as A • --COUNT(*)는 해당 데이터 로우에 컬럼들이 모두 NULL값을 가졌다고 해도 • --1건으로 계산되어 진다. 하지만, COUNT(컬럼)을 했을 때 해당 컬럼이 NULL • --값을 가지고 있다면 NULL은 0으로 집계 되어 진다. sqlhwan@hotmail.com

31. GROUP BY와 집계함수의 사용 - 7 • 각 집계 함수의 NULL처리 : SUM, AVG, MAX, MIN • SELECT SUM(A), AVG(A), MAX(A), MIN(A) • FROM (SELECT NULL as A • UNION ALL • SELECT 5 as A • UNION ALL • SELECT 10 as A) as A • go • --결과를 수행하면 MAX와 MIN은 NULL을 무시함을 쉽게 확인 할 수 있다. • --NULL은 알 수 없는 값이므로 가장 크다, 가장 작다를 말 할 수 없다. • --AVG는 평균을 구하는 것인데, 만약 NULL인 값을 가진 데이터를 0으로 • --포함시켰다면 결과는 5가 되어야 한다. 하지만, NULL은 아예 제외됐다. • --SUM역시 NULL을 0으로 사용하지 않았다. 다음을 실행하면 SUM에서 • --NULL이 0으로 포함되지 않았다는 것을 알 수 있다. • SELECT SUM(A), AVG(A), MAX(A), MIN(A), COUNT(A) • FROM (SELECT NULL as A • UNION ALL • SELECT 5 as A • UNION ALL • SELECT 10 as A) as A • WHERE A IS NULL • --COUNT만이 NULL값을 0으로 집계하는 것을 알 수 있다. • --이런 사항때문에 때로는 AVG가 문제가 될 수도 있다. sqlhwan@hotmail.com

32. GROUP BY와 집계함수의 사용 - 8 • 각 집계 함수의 NULL처리 : AVG • --AVG함수를 사용할 때 NULL값 역시 평균에 포함시켜야 할 경우가 있다. • --이럴경우는 NULL값은 0으로 포함시켜야 한다. • SELECT AVG(A), SUM(A)/COUNT(A), SUM(A)/COUNT(*) • FROM (SELECT NULL as A • UNION ALL • SELECT 5 as A • UNION ALL • SELECT 10 as A) as A • go • --결과를 수행하면 첫 번째 평균과 두 번째 평균은 동일한 값을 갖는다. • --이 두 결과는 NULL을 무시한 것이기 때문이다. • --세 번째 컬럼의 결과는 SUM(A)를 COUNT(*)로 나누는데 SUM(A)에는 • --NULL값이 포함되지 않았지만, 해당 SUM을 3으로 나누게 되었으므로 • --NULL값이 0으로 포함되었을 경우와 동일한 결과가 나타난다. • --세 번째 컬럼과 동일결과를 다음과 같은 ISNULL을 사용해서 구할 수 있다. • SELECT AVG(ISNULL(A,0)) • FROM (SELECT NULL as A • UNION ALL • SELECT 5 as A • UNION ALL • SELECT 10 as A) as A sqlhwan@hotmail.com

33. SUB QUERY의 사용 - 1 • 쿼리문 안에 또 다른 쿼리가 있는 것을 말한다. • 서브쿼리는 막강한 쿼리문을 만들기 위한 아주 중요한 요소이다. • 서브쿼리 안에 또 다른 서브쿼리를 중첩시킬 수 있으므로 여러 문장의 • 쿼리를 하나의 쿼리문으로 만들 수 있다. • 서브쿼리의 종류 • SELECT절에 사용되는 서브쿼리 • SELECT절에 사용되는 상관 서브쿼리 • FROM절에 사용되는 서브쿼리(파생된 테이블) • WHERE절에 사용되는 서브쿼리 • WHERE절에 사용되는 상관 서브쿼리 • 상관서브쿼리는 한 문장의 쿼리내에서 외부 쿼리의 결과에 상관 되어서 • 처리 되는 서브쿼리를 뜻한다. • 파생된 테이블은 FROM절에 사용되어진 서브쿼리로서 뷰와도 같다. sqlhwan@hotmail.com

34. SUB QUERY의 사용 - 2 • SELECT절 서브쿼리 • SELECT절에 괄호로 묶인 새로운 쿼리문을 만든다. • 서브쿼리에서는 단일값을 돌려줘야 한다. • SELECT절 서브쿼리의 사용 : Orders의 각 Order의 Freight와 총 Freight • 와 Freight중 최고 금액을 출력하는 쿼리 • DECLARE @MAXFreight as int, @TotFreight as int • SELECT @MAXFreight = MAX(Freight), @TotFreight = SUM(Freight) • FROM Orders • SELECT OrderID, Freight, @MAXFreight, @TotFreight • FROM Orders • go • --위의 두 문장으로 된 쿼리를 다음과 같이 한 문장으로 바꿀 수 있다. • SELECT OrderID, Freight, • (SELECT MAX(Freight) FROM Orders) as MAXFreight, • (SELECT SUM(Freight) FROM Orders) as TotFreight • WHERE Orders • --두 문장을 한 문장으로 줄였지만 실제로 성능에 도움을 주지 못한다. • 단일값 : 한 줄의 로우, 한 개의 컬럼, 또는 여러 개의 컬럼을 하나로 • 합쳐서 한 개의 값으로 만들어진 값을 말한다. sqlhwan@hotmail.com

35. SUB QUERY의 사용 - 3 • SELECT절 서브쿼리가 틀린 경우 : 두 줄 이상의 로우를 돌려줄 경우 • --다음은 오류가 발생된다. • SELECT OrderID, Freight, (SELECT Freight FROM Orders) as FriehgtList • FROM Orders • go • --Freight에 집계함수를 사용해서 한 개의 값만을 돌려주도록 해야 한다. • SELECT OrderID, Freight, (SELECT Freight FROM Orders) as FriehgtList • FROM Orders • SELECT절 서브쿼리가 틀린 경우 : 두 개 이상의 값을 돌려줄 경우 • --다음은 오류가 발생된다. • SELECT OrderID, Freight, (SELECT MAX(Freight), MIN(Freight) • FROM Orders) • FROM Orders • go • --위의 쿼리는 다음과 같은 방식으로 변경함으로써 서브쿼리에서 • --두 개의 컬럼 값을 하나의 컬럼 값으로 돌려 줄 수 있다. • SELECT OrderID, Freight, • (SELECT CAST(MAX(Freight) as varchar(10)) + '|' + • CAST(MIN(Freight) as varchar(10)) FROM Orders) as [MIN|MAX] • FROM Orders sqlhwan@hotmail.com

36. SUB QUERY의 사용 - 4 • SELECT절 상관서브쿼리 • SELECT절 서브쿼리가 메인쿼리의 컬럼 값을 참조한다. •  단일값을 돌려줘야 한다. • SELECT절 상관서브쿼리의 사용 : Orders테이블을 조회하는데, 조인을 사용 • 하지 않고 주문한 고객의 CompanyName을 보여주고자 한다. CompanyName은 • Customers테이블에 존재한다. • SELECT (SELECT CompanyName FROM Customers as B • WHERE B.CustomerID = A.CustomerID) as CompanyName, • A.* • FROM Orders A • go • --위와 같은 쿼리를 조인으로 해결한다면 다음과 같다. • SELECT B.CompanyName, A.* • FROM Orders A JOIN Customers B • ON A.CustomerID = B.CustomerID • --상관 서브쿼리를 사용할 때도 SELECT절의 서브쿼리처럼 단일 값을 돌려줘야 • --한다. 여기서는 Customers와 Orders의 관계가 CustomerID를 사용해서 1:N의 • --관계이기 때문에 오류가 발생하지 않는다. • --만약에 Customers테이블에 동일한 CustomerID가 두 개 이상 존재한다면 • --위와 같은 상관 서브쿼리는 오류가 발생한다. sqlhwan@hotmail.com

37. SUB QUERY의 사용 - 5 • SELECT절 상관서브쿼리가 틀린 경우 : Customers테이블의 복사본을 만들어 • CustomerID를 중복시킨 후 Orders의 주문별 CompanyName을 알아보는 상관 • 서브쿼리를 만들어 본다. • IF OBJECT_ID('Custs') IS NOT NULL DROP TABLE Custs • SELECT * INTO Custs FROM Customers • --CustomerID가 ALFKI인 데이터가 두 건이 되도록 INSERT를 수행한다. • INSERT Custs • SELECT * FROM Custs WHERE CustomerID = 'ALFKI' • go • --상관 서브쿼리를 실행한다. 오류가 발생될 것이다. • SELECT (SELECT CompanyName FROM Custs as B • WHERE B.CustomerID = A.CustomerID) as CompanyName, A.* • FROM Orders as A • go • --이 쿼리는 일부 실행결과를 내보내게 되는데 이유는 상관 서브쿼리가 외부 • --결과에 영향을 받기 때문이다. 다음과 같은 쿼리는 오류가 없다. • SELECT (SELECT CompanyName FROM Custs as B • WHERE B.CustomerID = A.CustomerID) as CompanyName, A.* • FROM Orders as A • WHERE A.CustomerID <> 'ALFKI' sqlhwan@hotmail.com

38. SUB QUERY의 사용 - 6 • 위와 같이 SELECT절에 일반 컬럼이 순수하게 오는 것은 언제든지, 여러 • 건의 로우를 내보낼 수 있는 잠재적인 오류를 지니고 있다. • SELECT절 상관서브쿼리의 오류 해결 : MAX, MIN등의 집계 함수를 사용하건, • TOP 1등을 사용해서 오직 하나의 값만을 돌려주도록 한다. • SELECT (SELECT MAX(CompanyName) FROM Custs as B • WHERE B.CustomerID = A.CustomerID) as CompanyName, A.* • FROM Orders as A • --이 쿼리는 상관서브쿼리에서 돌려주는 값에 MAX만을 취하고 있다. • --이런 함수를 사용하게 되면 무조건 단일값을 돌려주게 된다. • --그러므로 오류는 발생하지 않는다. 하지만, 이렇게 돌려주는 값은 꼭 • --원하지 않는 값일 수 있다. • --이것이 SELECT절의 상관서브쿼리나, SELECT절의 서브쿼리에서 단일값을 • --돌려주기 위한 일반적인 방법이다. • SELECT절 상관서브쿼리의 사용 : Customers의 정보를 SELECT하고 해당 • 고객의 최근의 주문일자를 같이 조회하기 • SELECT (SELECT MAX(OrderDate) FROM Orders B • WHERE B.CustomerID = A.CustomerID) as RecentDate, A.* • FROM Customers A sqlhwan@hotmail.com

39. SUB QUERY의 사용 - 7 • 상관서브쿼리 이해하기 • SELECT (SELECT MAX(OrderDate) FROM Orders as B • WHERE B.CustID = A.CustID) as RecentDate, A.* • FROM Custs as A Custs as A Orders as B 2 3 9 1 8 4 5 7 10 11 13 16 12 6 Results 14 15 • Custs를 읽어가면서 각 데이터마다 Orders에 가서 • 해당 CustID의 최근 주문 일자를 찾아온다 sqlhwan@hotmail.com

40. SUB QUERY의 사용 - 8 • 파생된 테이블 :  FROM절에 사용되는 서브쿼리  해당 서브쿼리에 별칭을 꼭 부여해야 한다. 상관서브쿼리처럼 외부 테이블과 연관 될 수 없다. • 서브쿼리 안에 또 다른 서브쿼리를 사용하는 방식을 통해 어려운 문제도 • 해결 할 수 있다. • 파생된 테이블의 사용 : Customers를 조회하면서, 각 고객의 주문한 제품 • 의 건수(Order Details의 데이터 건수)를 같이 조회 • SELECT B.*, A.* • FROM Customers A JOIN • (SELECT A.CustomerID, COUNT(*) as CNT • FROM Orders as A JOIN [Order Details] as B • ON A.OrderID = B.OrderID • GROUP BY A.CustomerID) as B • ON A.CustomerID = B.CustomerID • go • --복잡한 쿼리이지만, FROM절 안의 서브쿼리부터 하나씩 실행해보면 쉽게 알 수 있다 sqlhwan@hotmail.com

41. SUB QUERY의 사용 - 9 • 파생된 테이블 이해하기 • SELECT B.*, A.* • FROM Custs A JOIN • (SELECT A.CustID, COUNT(*) as CNT • FROM Ords as A JOIN [OrdDets] as B • ON A.OrdID = B.OrdID • GROUP BY A.CustID) as B • ON A.CustID = B.CustID OrdDets as B Results Orders as A FROM Custs A JOIN (..) as B ON A.OrdID = B.OrdID Custs as A SubResults as B (SELECT A.CustID, COUNT(*) as CNT FROM Ords as A JOIN [OrdDets] as B ON A.OrdID = B.OrdID GROUP BY A.CustID) as B sqlhwan@hotmail.com

42. SUB QUERY의 사용 - 10 • WHERE절 서브쿼리 •  WHERE절에 사용되는 서브쿼리이다. • 여러 로우를 돌려 줄 수 있다.(WHERE절에 EXISTS, IN연산만 가능) • 여러 컬럼을 돌려 줄 수 있다.(WHERE절에 EXISTS만 가능) • WHERE절 서브쿼리의 사용 : Order Details에 11번제품에 주문이 있는 • Orders만 보여주는 쿼리 • SELECT * • FROM Orders as A • WHERE OrderID IN (SELECT OrderID • FROM [Order Details] as B • WHERE ProductID = 11) • --WHERE절의 서브쿼리에서 ProductID가 11인 모든 주문들의 주문ID를 SELECT하고 • --해당 결과를 메인쿼리에서 IN연산자를 사용해서 조건으로 받게 된다. • --만약에 WHERE절에 IN이나 EXISTS가 아닌 =, >, < 등의 조건식을 사용한다면 • --서브쿼리에서는 단일값을 돌려줘야 한다. • --위의 쿼리는 다음과 같은 조인으로도 해결이 가능하다. • SELECT * • FROM Orders as A JOIN [Order Details] as B • ON A.OrderID = B.OrderID • WHERE B.ProductID = 11 sqlhwan@hotmail.com

43. SUB QUERY의 사용 - 11 • WHERE절 서브쿼리 이해하기 • SELECT * • FROM Orders as A • WHERE OrdID IN (SELECT OrdID • FROM OrdDets as B • WHERE ProdID LIKE 'B%') Orders as A OrdDets as B Results sqlhwan@hotmail.com

44. SUB QUERY의 사용 - 12 • WHERE절 상관서브쿼리 •  WHERE절에 사용되는 상관서브쿼리이다. • 여러 로우를 돌려 줄 수 있다.(WHERE절에 EXISTS, IN연산만 가능) • 여러 컬럼을 돌려 줄 수 있다.(WHERE절에 EXISTS만 가능) • EXISTS연산은 데이터의 존재 여부를 따지는 연산이다. • EXISTS의 사용 : Orders에 주문이 존재하는 Customers의 조회 • SELECT * • FROM Customers as A • WHERE EXISTS(SELECT * • FROM Orders as B • WHERE B.CustomerID = A.CustomerID) • --EXISTS의 부정을 사용해서 존재하지 않는 데이터만 찾아 낸다. • SELECT * • FROM Customers as A • WHERE NOT EXISTS(SELECT * • FROM Orders as B • WHERE B.CustomerID = A.CustomerID) sqlhwan@hotmail.com

45. SUB QUERY의 사용 - 13 • EXISTS 이해하기 • SELECT * • FROM Custs as A • WHERE EXISTS(SELECT * • FROM Orders as B • WHERE B.CustID = A.CustID) Orders as A Custs as A Results • EXISTS는 존재여부만 확인을 하기 때문에 서브쿼리에 • 있는 데이터중에 조건에 맞는 데이터만 찾으면 나머지 • 데이터를 검색하지 않는다. sqlhwan@hotmail.com

46. SUB QUERY의 사용 - 14 • NOT EXISTS 이해하기 • SELECT * • FROM Custs as A • WHERE EXISTS(SELECT * • FROM Orders as B • WHERE B.CustID = A.CustID) Orders as A Custs as A Results sqlhwan@hotmail.com

47. TOP의 활용 - 1 • TOP키워드를 SELECT절에 입력함으로써 데이터의 출력 건수에 제한을 줄 수 • 있다. • TOP키워드를 사용하면 서브쿼리에 대해 정렬이 가능하다. • TOP은 덩치가 큰 테이블의 데이터를 볼 때 샘플 데이터 몇 건 만을 보기 • 위해 아주 유용하게 사용 될 수 있다. • TOP 대신에 SET ROWCOUNT n을 사용할 수 있다. • SET ROWCOUNT n은 사용 후 다시 초기화 해야 하는 불편함이 있다. • TOP의 사용 : Customers의 정보 10건만을 조회 • SELECT TOP 10 * • FROM Customers • go sqlhwan@hotmail.com

48. TOP의 활용 - 2 • TOP 대신 SET ROWCOUNT의 사용 : Customers의 데이터중 10건만 조회 • --SET ROWCOUNT를 사용하면 다음과 같다. • SET ROWCOUNT 10 • SELECT * • FROM Customers • SET ROWCOUNT 0 --ROWCOUNT를 초기화 시킨다. • TOP의 사용 : Customers의 정보중에 50%만 조회하기 • SELECT TOP 50 * • FROM Customers • TOP과 ORDER BY의 사용 : Orders의 고객별 Order Details의 UnitPrice합계 • 를 구해서 합계중 최상위 10명만 조회 • SELECT TOP 10 A.CustomerID, SUM(B.UnitPrice) • FROM Orders as A JOIN [Order Details] as B • ON A.OrderID = B.OrderID • GROUP BY A.CustomerID • ORDER BY SUM(B.UnitPrice) DESC • --해당 고객의 ID만 얻었을 뿐, 해당 고객의 정보를 얻기 위해서는 Customers테이블 • --과 조인을 해야만 한다. sqlhwan@hotmail.com

49. TOP의 활용 - 3 • TOP과 서브쿼리의 사용 : UnitPrice의 합계가 최상위인 고객 10명의 모든 • 고객정보를 보고자 한다. • --GROUP BY와 SELECT에 보고자 하는 컬럼을 모두 기술해야만 한다. • SELECT TOP 10 A.CustomerID, SUM(C.UnitPrice), A.CompanyName, A.Address • FROM Customers as A JOIN Orders as B ON A.CustomerID = B.CustomerID • JOIN [Order Details] as C ON B.OrderID = C.OrderID • GROUP BY A.CustomerID, A.CompanyName, A.Address • ORDER BY SUM(C.UnitPrice) DESC • go • --GROUP BY에 컬럼 기술을 피하기 위해 MAX, MIN등의 집계함수를 사용 할 수 있다. • SELECT TOP 10 A.CustomerID, SUM(C.UnitPrice), • MAX(A.CompanyName) as CompanyName, MAX(A.Address) as Address • FROM Customers as A JOIN Orders as B ON A.CustomerID = B.CustomerID • JOIN [Order Details] as C ON B.OrderID = C.OrderID • GROUP BY A.CustomerID • ORDER BY SUM(C.UnitPrice) DESC • go • --위 두 쿼리의 단점은 해당 고객의 모든 정보를 보고 싶을 때 모든 컬럼을 기술해야 • --한다는 것이다. • --개선 방법은 서브쿼리를 사용하는 것이다. sqlhwan@hotmail.com

50. TOP의 활용 - 4 • TOP과 서브쿼리의 사용 : UnitPrice의 합계가 최상위인 고객 10명의 모든 • 고객정보를 보고자 한다. • --다음과 같은 IN을 사용한 WHERE절의 서브쿼리를 사용해본다. • SELECT * • FROM Customers • WHERE CustomerID IN (SELECT TOP 10 A.CustomerID • FROM Orders as A JOIN [Order Details] as B • ON A.OrderID = B.OrderID • GROUP BY A.CustomerID • ORDER BY SUM(B.UnitPrice) DESC) • go • --위 쿼리의 또 다른 문제는 서브쿼리에 있는 SUM(B.UnitPrice)를 보여 줄 수 없다는 • --것이다. 다음과 같이 WHERE절의 서브쿼리를 FROM절의 파생된 테이블로 옮겨서 해결 • --가능하다. • SELECT B.*, A.* • FROM Customers as A JOIN (SELECT TOP 10 A.CustomerID, • SUM(B.UnitPrice) as TotUnit • FROM Orders as A JOIN [Order Details] as B • ON A.OrderID = B.OrderID • GROUP BY A.CustomerID • ORDER BY SUM(B.UnitPrice) DESC) as B • ON A.CustomerID = B.CustomerID sqlhwan@hotmail.com