JOIN, UNION, NOT IN, IN, 스토어드, PREPARE EXECUTE

0. SQL 표현순서

SELECT / FROM / WHERE / GROUP BY / HAVING / ORDER BY

0. 연산순서

FROM / WHERE / GROUP BY / HAVING / SELECT / ORDER BY

1. SQL JOIN

SELECT 컬럼명
FROM TableA A
LEFT OUTER JOIN TableB B
ON A.KEY = B.KEY
-- USING(KEY)로 대체가능

SELECT 컬럼명
FROM TableA A
RIGHT OUTER JOIN TableB B
ON A.KEY = B.KEY
-- USING(KEY)로 대체가능

SELECT 컬럼명
FROM TableA A
LEFT OUTER JOIN TableB B
ON A.KEY = B.KEY
WHERE B.KEY IS NULL

SELECT 컬럼명
FROM TableA A
LEFT OUTER JOIN TableB B
ON A.KEY = B.KEY
WHERE A.KEY IS NULL

SELECT 컬럼명
FROM TableA A
INNER JOIN TableB B
ON A.KEY = B.KEY

SELECT 컬럼명
FROM TableA A
FULL OUTER JOIN TableB B
ON A.KEY = B.KEY
WHERE A.KEY IS NULL OR B.KEY IS NULL

SELECT 컬럼명
FROM TableA A
FULL OUTER JOIN TableB B
ON A.KEY <> B.KEY

SELECT 컬럼명
FROM TableA A
FULL OUTER JOIN TableB B
ON A.KEY = B.KEY

SELECT A.MEM_ID, A.NAME, A.DEPART_ID, B.DEPART_NAME, C.LOG_TIME
FROM A A
JOIN B B
ON A.DEPART_ID = B.DEPART_ID
JOIN C C
ON A.MEM_ID = C.MEM_ID
WHERE A.NAME = ‘홍길동';

CROSS JOIN - 데카르트의 곱

SELECT 컬럼명
FROM TableA A
CROSS JOIN TableB B

SELF JOIN - 필요에 의해 JOIN 구문에 같은 테이블이 두번이상 등장 ex) 자신의 상급자 매핑

SELECT 컬럼명
FROM TableA A
CROSS JOIN TableB B
ON A.id = B.manageid

UNION

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

당연한 문장1의 결과는 INT인데 문장2의 결과는 CHAR이라면 오류가 발생할 것이다.

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

SELECT 문장1
UNION
-- UNION DISTINCT 와 동일
SELECT 문장2

SELECT 문장1
UNION ALL
SELECT 문장2

NOT IN 연산자

첫번째 쿼리의 결과 중에서 두번쨰 쿼리에 해당하는 것을 제외하기 위한 구문

SELECT 컬럼명
FROM 테이블명
WHERE 조건컬럼명 NOT IN (문장2)

IN 연산자

첫번째 쿼리의 결과 중에서 두 번째 쿼리에 해당되는 것만 조회

SELECT 컬럼명
FROM 테이블명
WHERE 조건컬럼명 IN (문장2)

---

2. 스토어드

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

$$ 구분자는 //, &&, @@ 등 다른것을 사용해도 된다. 또 2개 연속이 아니라 하나만 사용해도 되지만 가능하면 다른 기호와 중복되지 않도록 2개를 연속해서 사용하는것이 좋다.

DELIMITER $$
create procedure 스토어드 프로시저이름()
BEGIN
	이부분에 SQL 프로그래밍 코딩
END $$
DELIMITER ;
CALL 스토어드 프로시저이름()

IF..ELSE문

drop procedure IF EXISTS IFPROC;
DELIMITER $$
create procedure IFPROC()
BEGIN
	declare VAR1 INT; -- var1 지역변수 선언
    set var1 = 100; -- 변수에 값 대입
 
    if var1 = 100 then -- 만약 @VAR1이 100이라면
		select 'true';
	else
		select 'false';
	end if;
END $$
DELIMITER ;
call IFPROC();

CASE문

drop procedure IF EXISTS CaseProc;
DELIMITER $$
create procedure CaseProc()
BEGIN
    declare n1 int;
    declare credit char(1);
    set n1 = 50;
 
    case
		when n1 >= 90 then
			set credit = 'A';
		when n1 >= 80 then
			set credit = 'B';
		when n1 >= 70 then
			set credit = 'C';
		when n1 >= 60 then
			set credit = 'D';
		else
			set credit = 'F';
	end case;
    select concat('학점 ==> ', credit) AS '결과';
END $$
DELIMITER ;
call CaseProc();

WHILE과 ITERATE/LEAVE

while <부울식> DO
   SQL 명령어
end while;

drop procedure IF EXISTS whileProc;
DELIMITER $$
create procedure whileProc()
BEGIN
	declare i int;
    declare count int;
    set i = 1;
    set count = 0;
 
    mywhile: while(i<100) do -- while문에 label 지정
		if(i%2 = 0) then
			set i = i+1;
            iterate mywhile; -- countinue;
		end if;
        set count = count + 1;
 
        if(count >= 10) then
			leave mywhile; -- break;
		end if;
        set i = i+1;

	end while;
	select i,count; -- 결과 i:19, count:10
END $$
DELIMITER ;
call whileProc();

오류처리

delcare 액션 handler for 오류조건 처리할_문장;

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

- 오류조건 : 어떤 오류를 처리할 것인지를 지정한다. 여기에는 MySQL의 오류 코드 숫자가 오거나 SQLSTATE '상태코드', SQLEXCEPTION, SQLWARNING, NOT FOUND 등이 올 수 있다. SQLSTATE에서 상태 코드는 5자리 문자열로 되어 있다. SQLEXCEPTION은 대부분의 오류를, SQLWARNING은 경고메시지를, NOT FOUND는 커서나 SELECT ... INTO에서 발생되는 오류를 의미한다.

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

https://dev.mysql.com/doc/refman/8.0/en/server-error-reference.html

이 사이트에 가면 코드에 관한 설명서가 있다.

drop procedure IF EXISTS errorProc1;
DELIMITER $$
create procedure errorProc1()
BEGIN
	declare continue handler for 1146 select '테이블이 없습니다' AS '메시지';
    select * from notalbe;
END $$
DELIMITER ;
call errorProc1();

동적SQL

PREPARE EXECUTE문

set @curDate = current_timestamp();
 
prepare myquery from 'select ? as \'time\''; -- prepare문 준비
execute myquery using @curDate; -- prepare문 실행
deallocate prepare myquery; -- prepare문 해체