이것이 MySQL이다. [Chapter 7 - SQL 고급(조인, SQL 프로그래밍)]

7.2 조인

조인이란 두 개 이상의 테이블을 서로 묶어서 하나의 결과 집합으로 만들어 내는 것을 말한다.

데이터베이스의 테이블은 중복과 공간 방비를 피하고 데이터의 무결성을 위해서 여러 개의 테이블로 분리하여 저장한다. 

그리고 이 분리된 테이블은 서로 관계를 맺고 있다.

조인을 통해 여러 테이블에 분산되어 있는 데이터를 효율적으로 관리하고 조회할 수 있다.

 

7.2.1 INNER JOIN(내부 조인) 

조인 중에서 가장 많이 사용되는 조인이다.

두 개의 테이블을 결합하여 두 테이블 간의 일치하는 행만 반환한다. *교집합

INNER 조인 예시

 

buytbl, usertbl테이블에서 buytbl 테이블에 있는 userid 중 값이 KBS인 값을 갖는 해당 열을 출력한다.

 

각 테이블에 동일한 열 이름이 존재하기 때문에 '테이블이름.열 이름' 형식을 사용한다.

ex) buytbl.userID

 

코드를 간결화하기 위해 각 테이블에 별칭을 줄 수 있다.

 

 

 

7.2.1 OUTER JOIN(내부 조인)

OUTER JOIN은 조건에 만족되지 않는 행까지도 포함시킨다. 

LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUT JOIN으로 나뉘어져 있다. 

OUTER JOIN 예시

 

 

LEFT OUTER JOIN 

LEFT OUTER JOIN 예시

usertbl에 대한 정보만 출력된다. 만약 buytbl의 데이터가 없다면 null로 값이 들어가게 된다.

즉, 왼쪽 테이블에 대한 정보만 출력된다.

줄여서 LEFT JOIN도 가능하다.

 

 

RIGHT OUTER JOIN 

RIGHT OUTER JOIN

buytbl에 대한 정보만 출력된다. 만약 usertbl의 데이터가 없다면 null로 값이 들어가게 된다.

즉, 오른쪽 테이블에 대한 정보만 출력된다.

줄여서 RIGHT JOIN도 가능하다.

 

 

FULL OUTER JOIN *합집합

FULL OUTER JOIN이란 LEFT OUTER JOIN과 RIGHT OUTER JOIN이 합쳐진 것이다.

즉, 한쪽을 기준으로 조건과 일치하는 것을 출력하는 것이 아닌 양쪽 모두 일치하는 행과 일치하지 않는 행을 포함한다.

FULL OUTER JOIN예시

두 테이블의 모든 값을 반환하였다.

먼저 usertbl의 있는 모든 정보를 LEFT JOIN하여 반환하고, buytbl에 있는 모든 정보를 RIGHT JOIN하여 반환한다.

그 다음 UNION을 통해 두 결과를 합쳐 두 테이블의 모든 값을 출력할 수 있다.

 

사용 예시

두 테이블 간의 일치하는 행과 일치하지 않은 행을 모두 확인할 수 있다. 즉, 데이터의 일치 여부를 확인하고자 할 때 사용된다. 

두 테이블 간의 모든 행을 포함하는 결과를 얻을 수 있기 때문에 두 테이블 간의 관계를 이해하고 패턴을 파악할 때 사용된다.

 

 

7.3.3 CROSS JOIN(상호 조인)

CROSS JOIN은 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인시키는 기능을 한다.

그래서 CROSS JOIN의 결과 개수는 두 테이블 개수를 곱한 개수가 된다. 이러한 곱을 카티션곱이라고도 부른다.

ON 절이 없기 때문에 모든 첫 번째 테이블의 행과 두 번째 테이블의 행이 서로 결합된다.

CROSS JOIN 예시

 

......

usertbl은 10개의 행을, buytbl은 12개 행을 가지고 있어 총 120개 행이 출력되었다.

 

사용 예시

데이터 집계를 위해 가능한 모든 조합을 얻어야 할때 사용된다.

 

 

7.2.4 SELF JOIN(자체 조인)

하나의 테이블을 여러 번 사용하여 자기 자신과 조인한다는 것을 말한다. 

즉, 하나의 테이블에서 서로 다른 행을 비교하고 결합하는 것이다.

특정 테이블 내에서 데이터 간의 관계를 분석하거나 계층 구조를 처리하는데 유용하다.

 

SELF JOIN 예시

지사원의 상관은 이부장이고, 이부장의 상관은 김재무이다.

만약 지사원의 상관 정보를 알고 싶다면?

위와 같이 INNER JOIN을 통해 테이블을 합치고,  지사원일 때 정보를 출력한다.

 

 

7.2.5 UNION / UNION ALL / NOT IN / IN

UNION / UNION ALL

UNION은 두 쿼리의 결과를 행으로 합치는 것을 말한다.

UNION 예시

SELECT 문장 1과 SELECT 문장 2의 결과 열의 개수가 같아야 되고, 데이터 형식도 각 열 단위로 같거나 서로 호환되는 데이터 형식이어야 한다.

UNION만 사용하면 중복된 열을 제거되고 데이터가 정렬되어 나오며, UNION ALL을 사용하면 중복된 열까지 모두 출력된다.

 

NOT IN / IN

NOT IN은 첫 번째 쿼리의 결과 중에서, 두 번째 쿼리에 해당하는 것을 제외하기 위한 구문이다.

IN은 반대로 첫 번째 쿼리의 결과 중에서, 두 번째 쿼리에 해당하는 것만 조회하기 위한 구문이다.

 

NOT IN 예시

만약 그룹 카테고리가 없는 물건을 산 사람의 userid를 알고 싶다면 다음과 같이 buytbl에서 userID가 groupName이 null 일때 전체를 조회하는 쿼리이다.

NULL 값을 빼고 조회하는 것을 알 수 있다.

 

IN 예시

마찬가지로 해당 쿼리에 IN만 바꾸면 groupName이 NULL인 경우만 조회한다.

 

 

7.3 SQL 프로그래밍

SQL에서도 다른 프로그래밍 언어와 비슷한 분기, 흐름 제어, 반복의 기능이 있다.

SQL 프로그램을 하려면 우선 스토어드 프로시저를 만들어야 한다.

스토어드 프로시저 형식

DELIMITER $$ ~ END $$ 부분까지는 스토어드 프로시저의 코딩할 부분을 묶어준다고 보면 된다.

다시 DELIMITER;로 종료 문자를 세미콜론으로 변경해놓아야 한다.

CALL 스토어드 프로시저이름();은 CREATE로 생성한 스토어드 프로시저를 실행한다.

 

 

7.3.1 IF ... ELSE

조건에 따라 분기한다.

한 문장 이상이 처리되어야 할 때는 BEGIN, END와 함께 묶어줘야만 한다.

IF ELSE 형식

 

IF ELSE 예시

var의 값은 24이고

24보다 크면 '해당 값보다 더 작습니다.'.

24보다 작으면 '해당 값보다 더 큽니다'.

24이면 '정답입니다.'를 출력하는 예시이다.

 

 

7.3.2 CASE

IF구문은 참 아니면 거짓만 있기 때문에 2중 분기라는 용어를 사용하지만

CASE는 경우에 따라 달라지기 때문에 다중 분기라는 용어를 사용한다.

CASE 문 예시

 

CASE문 결과

CASE문은 조건에 맞는 WHEN이 여러 개더라도 먼저 조건이 만족하는 WHEN이 처리된다.

 

 

7.3.3 WHILE과 ITERATE/LEAVE

WHILE

WHILE문은 다른 프로그래밍 언어의 WHILF과 동일한 개념이다.

해당 <부울 식>이 참인 동안에 계속 반복되는 반복문이다.

WHILE 형식

 

while문 예시

while문 결과

i가 10일 때까지 반복하는데, 한 번 반복될 때마다 result에 1씩 더해진다.

즉, 1부터 10까지의 모든 값을 더한 합이 result에 담기고 출력된다.

 

 

ITERATE/LEACE

만약 반복문을 사용하다 중간에 반복하는 것을 멈추고 싶다면 ITERATE와 LEACE 문을 사용한다.

ITERATE/LEAVE문 예시

ITERATE문을 만나면 바로 WHILE문으로 이동해서 비교를 다시 하고, 

LEAVE문을 만나면 WHILE문을 빠져나온다.

 

 

7.3.4 오류 처리

오류 처리 형식

액션: 오류 발생 시에 행동을 정의하는데 CONTINU와 EXIT 둘 중 하나를 사용한다. CONTINUE가 나오면 제일 뒤의 '처리할 문장' 부분이 처리된다.

 

오류 조건: 어떤 오류를 처리할 것인지 지정한다. 여기에는 오류 코드 숫자가 오거나 SQLSTATE, SQLEXCEPTION, SQLWARNING, NOT FOUND 등이 올 수 있다.

 

SQLSTATE에서 상태 코드는 5자리 문자열로 되어 있다. SQLEXCEPTION은 대부분의 오류를, SQLWRNING은 경고 메시지를, NOT FOUND는 커서나 SELECT INTO에서 발생되는 오류를 의미한다.

 

처리할 문장: 처리할 문장이 하나라면 한 문장이 나오면 되며, 여러 개일 경우에는 BEGIN...END로 묶어줄 수 있다.

 

오류 처리 예시

오류 처리 예시 결과

테이블이 없을 경우 오류를 처리하는 코드이다.

DECLARE 부분이 있기 때문에 사용자가 지정한 메시지인 '테이블이 없다.'가 출력된다.

 

 

7.3.5 동적 SQL

PREPARE는 SQL 문을 실행하지 않고 미리 준비만 해놓고, EXECUTE문은 준비한 쿼리문을 실행한다.

그리고 실행 후에 DEALLOCATE PREFARE로 문장을 해제해준다.

이렇게 미리 쿼리문을 준비한 후에 나중에 실행하는 것을 동적 SQL이라고 부른다.

 

PREPARE문에서 ?으로 향후에 입력될 값을 비워놓고, EXECUTE에서 USING을 이용해서 값을전달해서 사용할 수 있다.

동적 SQL 예시

쿼리를 실행하는 순간 날짜와 시간이 입력되는 기능이다.

?에는 @curDate가 들어가게 되고, @curDate에는 현재 날짜와 시간이 들어가있다.

즉 날짜와 시간이 동적으로 할당되게 된다.