8. 5장 SQL: QUERIES,CONSTRNNTS, TRIGGERS - DML, multi-table

이번에는 멀티테이블을 사용해본다!

-> 여러개의 테이블을 합쳐서 본다,,,

즉, 쿼리를 시작하기 전에, from에 넣어준 테이블을을 cross-product한 다음에 그 다음동작들을 해준다

cross-product하면,, 하나의 테이블이 되는거니까 나머지 동작은 이전 포스트에서 했던것과 똑같을것같다!

 

전체적인 흐름으로 보면,,,

1. FROM (cross product)

2. WHERE (조건에 맞지 않는 row 삭제)

3. SELECT (colums선택)

4. GROUP BY (그룹짓지, aggregate해주기)

5. HAVING (조건에 맞지않는 그룹 삭제하기)

6. DISTINCT (중복제거)

순서로 실행된다고 한다!

SQL은 코드에 적은 순서대로 실행되는게 아니라 여러모로 햇갈리는것같다...

다만,,,, Query optimizer가 더 효율적인 순서?방법?을 알아서 찾아주는듯 하니

저 순서는 단순히 개념으로 생각하면 될것같다!

 

Cross (Cartesian) Product

SELECT *
FROM	Sailors, Reserves;

이렇게 해주면,, sailors테이블이랑 reseves테이블을 cross product한, 하나의 테이블 위에서 쿼리가 실행된다.

cross product의 결과는 sailors.rows * reserves.rows 크기의 테이블이 된다.

 

또한 똑같은 테이블을 여러번 넣을수도 있다 (self-join)

SELECT	x.sname, y.sname
FROM	Sailors AS x, Sailors AS y
WHERE	x.age > y.age;

이렇게,, 선원들 중, 나이별로 짝지어서 보여주고 싶을때 똑같은 테이블을 cross product해서 만들어준다!

 

Arithmetic Expression

WHERE, SELECT 안에 수식을 넣을 수 있다. 이때 수식은 각 원소마다 적용된다.

SELECT	S.age, S.age-5 AS age1, 2*S.age AS age2
FROM	Sailors AS S
WHERE	S.sname = 'Popeye'

SELECT	S1.sname AS name1, S2.sname AS name2
FROM	Sailors AS S1, Sailors AS s2
WHERE	2*s1.rating = s2.rating - 1

이렇게!! 위에처럼 SELECT안이나, 아래처럼 WHERE안에 적어준다.

 

SQL Calculator

log, exp, cos, ln 같은 수학함수들을 사용할 수 있다.

SELECT log(1000) as three,
	   exp(ln(2)) as two,
       cos(0) as one,
       ln(2*3) = ln(2) + ln(3) as sanity;

 

String Comparison

문자열 패턴을 사용해줄 수 있다 (LIKE랑 같이)

SELECT	S.sname
FROM	Sailors S
WHERE	S.sname LIKE 'B_%'

SELECT	S.sname
FROM	Sailors S
WHERE	S.sname ~ 'B.*'

두가지 표현 모두 사용할 수 있다! (아무거나 원하는거 쓰면 되는듯)

 

Combining Predicates

AND, OR같은 논리연산을 사용할 수 있다. 그리고 INTERSECT, UNION같은 집합연산도 적용 가능하다!

서로간에 차이가 있는듯하니 주의해야 하는듯!!

아래부터는 예시와 함께 설명한다.

 

 

Q. red "or" green 보트를 예약한 sailor의 아이디(sid)를 찾아줘

A. sailor의 이름을 궁금한게 아니라 아이디만 궁금한거니까 sailor테이블은 필요가 없다.

즉 보트테이블 (색깔비교)과 예약테이블(예약한 선원)을 가지고 진행한다.

reserves의 bid와 boats의 bid가 같은데(and), 색깔이 red or green인 선원의 아이디를 출력한다.

SELECT	R.sid
FROM	Reserves R, Boats B
WHERE	R.bid = B.bid AND(
			B.color = 'red' OR B.color = 'green');

앗 그런데,,, union을 사용하고 싶다면??

=> "빨간색 보트를 예약한 선원집합" 과 "초록색 보트를 예약한 선원집합" 으로 나눠서 생각하면... 두개를 따로 구해서 union을 해줘도 괜찮다!!

== 즉 or로 연결된 조건은,, 이렇게 나눠서 생각해줄수도 있다고 볼 수 있다.

SELECT	R.sid
FROM	Reserves R, Boats B
WHERE	R.bid = B.bid AND B.color = 'red'
UNION ALL
SELECT	R.sid
FROM	Reserves R, Boats B
WHERE	R.bid = B.bid AND B.color = 'green';

두개의 select를 union all로 연결해준다!! (위에 red다음에 ; 없는것 주의!! union all로 연결된 전체가 하나의 쿼리문이다)

 

결론 : OR는 UNION ALL로 표현해줄 수 있음

 

Q. 그러면... red "AND" green 일때는??

A. AND는,,, INTERSECT로 바꿔서 표현해줄 수 있을까?

SELECT	R.sid
FROM	Reserves R, Boats B
WHERE	R.bid = B.bid AND(
			B.color = 'red' AND B.color = 'green');

일단 단순히 위에서 AND로 바꿔보았다. 이러면 물어보는거랑 같지 않다.

=> 이러면 B.color = 'red' AND B.color = 'green' 가 항상 false이기 때문에... 비어있는 테이블이 나오게 된다.

근데 질문에서 물어보는건,, 빨간색 보트랑 초록색 보트를 둘 다 빌렸던 선원을 물어보는것이다

 

INTERSECT로 바꿔보면 어떨까?

SELECT	R.sid
FROM	Reserves R, Boats B
WHERE	R.bid = B.bid AND B.color = 'red'
INTERSECT
SELECT	R.sid
FROM	Reserves R, Boats B
WHERE	R.bid = B.bid AND B.color = 'green';

이렇게 하면,,, 결과는..... 다르다!!!!

boats

reserves

이런 테이블을 생각해보면...

첫번째 red선택에서 101보트를 예약한 sid 1번이 결과로 나온다

두번째 green 선택에서 102번을 예약한 sid 1번이 결과로 나온다.

즉... 1번친구가 초록색보트를 9월 12일에 예악하고, 빨간색 보트를 10월 1일에 예약한것이다!!

그래서 INTERSECT결과가,,,, 1번친구가 나온다.

즉 이렇게 INTERSECT로 해줘야 문제에서 물어보는 답이 나오게 된다!!

 

결론 : AND라고 무지성으로 AND만 쓰면 원하는 결과를 내지 못한다.

 

Q. 한번도 예약 안해본 선원을 구해줘

A. 예약 이력은,, Reserves table에, 모든 선원의 정보는 Sailors table에 있다

-> 즉,,, 선원정보 - 예약선원을 해주면 된다.

SELECT	S.sid
FROM	Sailors S
EXCEPT
SELECT	R.sid
FROM	Reserves R;

 

Set Semantics, Multiset Semantics

집합 연산을 할 때, 명령에 따라 결과를 set(중복X)으로 처리하는지, multi set(중복O)으로 처리하는지 달라진다.

 

- UNION

- INTERSECT

- EXCEPT

이런것들은 멀티set을 set으로 바꾼 다음에 처리한다!

 

- UNION ALL

- INTERSECT ALL

- EXCEPT ALL

이렇게 ALL을 붙여주면, multi set을 그대로 유지해준다.

 

Nested Queries

쿼리를 중첩시킬 수 있다!

IN

WHERE 안에서 조건으로 사용된다.

WEHERE [원소] IN [또다른 쿼리문]

=> 해당 원소가, 또 다른 쿼리문의 결과 (어떠한 집합일것)에 "포함되는지" 확인해준다.

Q. 102번 보트를 예약한적 있는 선원의 이름 (sname) 출력하기

SELECT	S.sname
FROM	Sailors S
WHERE	S.sid IN
	   (SELECT R.sid
            FROM	Reserves R
            WHERE	R.bid = 102);

=> FROM에 두개의 테이블을 넣고, WHERE에서 S.sid = R.sid로 비교해도 좋지만...

이렇게 IN을 사용해줄 수 있다!!

IN 뒤에 괄호로 묶인 쿼리의 결과를 하나의 집합으로 보고 생각하면 좋다!

 

Q. 그러면,, 예약을 안해본 선원의 이름은??

SELECT	S.sname
FROM	Sailors S
WHERE	S.sid NOT IN
	   (SELECT R.sid
            FROM	Reserves R
            WHERE	R.bid = 102);

NOT IN을 사용해주면 된다!

 

EXISTS

뒤에 나오는 쿼리의 결과집합이 "공집합이 아닐 때" 참이된다.

=> 즉.. 뒤에 쿼리에서 뭐가 존재하는지를 확인하는 것이다.

=> 이 때 주의할점!! FROM에서 tuple을 하나씩 뽑아서 EXISTS여부를 확인한다! 그러니까 Loop마냥 생각해본다.

Q. 102번 보트를 예약한적 있는 선원의 이름 (sname) 출력하기

SELECT	S.sname
FROM	Sailors S
WHERE	EXISTS
	(SELECT *
        FROM	Reserves R
        WHERE	R.bid = 102 AND S.sid = R.sid);

EXISTS 뒤에 괄호안에 있는 쿼리를 보면... 저기서 S집합을 사용하고 있다 (S.sid)

=> 근데..? 괄호 안에있는 FROM에서는 S를 언급하지 않았다!!

즉 저 S는 밖에서 온것이다!

=> 바로바로,, 밖에 있는 S집합의 튜플 하나마다, EXISTS안에 있는 쿼리문이 실행된다.

=> 즉 S의 원소개수가 10개면, 괄호속 쿼리문이 10번 실행되는것이다!

 

IN처럼 NOT EXISTS를 사용할 수 있다 (예약을 한번도 안해본 선원을 뽑는경우?)

 

ANY,  ALL

[논리연산자] ANY, [논리연산자] ALL 형태로 사용한다.

- ANY : 하나라도 참이면 참

- ALL : 전부 참이어야 참

Q. popeye라는 "어떤 (some)" 선원보다 rating이 큰 선원을 찾아라

A. some이 나왔다!! ANY를 써준다 (하나라도 참이면 그냥 참임)

SELECT	*
FROM	Sailors S
WHERE	S.rating > ANY 
	(SELECT	S2.rating
    	FROM	Sailors S2
        WHERE	S2.sname = 'popey');

 

Division

Relatioinal Division으로 생각해보자..

Q. Find sailors who've reserved all boats.

모든 보트를 예약한 선원을 찾는다...

다르게 말하면 sailors with no counterexample missing boats 라고 볼 수 있다??

 

각 선원별로 각 보트에 대해 확인한다. <- 즉 선원별/보트별로 반복문이 각각 필요함

특정 선원 한명에 대해, 각 보트마다 예약 여부를 확인해준다.

=> 예약 기록이 "없는" 보트만 뽑아서 테이블을 만들어본다. (NOT EXISTS)

=> 만약 테이블이 "비어있으면.." 해당 선원은 모든 보트를 예약해본 사람이다! (NOT EXISTS)

=> 만약 테이블이 "비어있지 않으면..." 예약을 안해본 보트가 존재하는 선원이므로 조건에 만족하지 않는다.

SELECT	 S.sname
FROM	Sailors S
WHERE	NOT EXIST
	(SELECT	B.bid
    	FROM	Boats B
        WHERE	NOT EXIST
        	(SELECT	R.bid
            	FROM	Reserves R
                WHERE	R.sid = S.sid
                	AND	R.bid = B.bid));

 

ARGMAX

max값을 가지는 tuple자체가 궁금할 수 있다.

하지만... MAX(s.rating)으로는 max value 자체만 알 수 있지, 어떤 튜플인지는 알 수 없다!

SELECT	S*, MAX(S.rating)
FROM	Sailors S;

이런쿼리는 그냥 틀려먹은 쿼리다!! 저렇게 해서는 알 수 없다.

 

Q. The sailor with the highest rating

이런 문제를 마주했을 때 ALL을 사용해주는것이다!

SELECT	*
FROM	Sailors S
WHERE	S.rating >= ALL
	(SELECT S2.rating
    	FROM	Sailors S2);

이 때 주의할점은,... 특정 세일러에 대해 나머지 모든 세일러와의 rating을 비교하므로 Sailors집합을 같이 사용할 수 없다.

저렇게 S, S2로 구문해서 사용해줘야한다!

 

굳이 MAX(S.rating)을 사용하고 싶다면 어떻게 해야할까? "=" 조건을 활용해준다!

SELECT MAX(S.rating)을 통해 나온 결과는 원소가 한개지만, 분명히 "집합"이다.

하지만 원소가 하나라는 특성때문에... SQL에서는 원소와의 비교를 허용해준다!

SELECT	*
FROM	Sailors S
WHERE	S.rating = 
	(SELECT	MAX(S2.rating)
    	FROM	Sailors S2);

바로 이렇게 말이다! = 을 기준으로 왼쪽은 원소 (S.rating) 오른쪽은 집합이지만.. 원소가 한개이므로 알아서 처리해준다.

 

LIMIT를 이용하면 어떨까? rating을 기준으로 정렬하고, 맨 위에 있는 한개만 출력해도 최대 rating을 가지는 원소를 찾을 수 있을것같다.

SELECT	*
FROM	Sailors S
ORDER BY rating DESC
LIMIT	1;

하지만... 최대값을 가지는 원소가 여러개이면 어떡하지?

위에 작성한 코드들로는.. 그럴 때 여러개의 원소가 출력되지만, LIMIT을 이용하면... 그냥 맨 앞에있는 원소 한개만 출력된다.

이렇게 작성하면 안된다!!

 

JOIN

FROM뒤에 여러 테이블을 적으면, 알아서 cross-product해준다.

근데... JOIN에도 INNER, OUTER JOIN.. 여러가지 방법이 있고, 이걸 설정해줄 수 있다!

다만 아무것도 명시하지 않고, 그냥 여러개를 적으면 INNER JOIN으로 알아듣고 INNER JOIN을 해준다.

아래 3개의 쿼리는 모두 똑같은 결과를 낸다

그냥 나열

SELECT	S.sid, S.sname, R.bid
FROM	Sailors S, Reserves R
WHERE	S.sid = R.sid
	AND	S.age > 20;

이렇게 그냥 나열해주면,,, 알아서 INNER JOIN해준다!

INNER JOIN + ON

SELECT	S.sname, S.sid, R.bid
FROM	Sailors S INNER JOIN Reserves R
	ON	S.sid = R.sid
WHERE	S.age > 20;

ON안에 조건을 넣어줄 수 있다!! 이러면 INNER JOIN할 때 처음부터 조건을 만족하는 애들로만 모아서 테이블을 만들어준다.

NATURAL JOIN

SELECT	S.sid, S.sname, R.bid
FROM	Sailors S NATURAL JOIN Reserves R
Where	S.age > 20;

NATURAL JOIN은 equi-join과 같다!

즉... S랑 R에 둘 다 sid가 있으니까 알아서 S.sid = R.sid인 애들만 골라준다 (이 의미가 내포된것)

 

OUTER JOIN

outer join은 안겹치는 친구들까지 남겨둔다.

join을 할 때, 보통 sid = sid처럼 조건을 넣어준다 (왠만해서는 natural join같은걸 원하고 함)

이럴 때,, sid = sid를 만족하지 못하는 tuple은 그대로 날려버린다! (inner에서는)

Outer는 저런애들을 그냥 나머지칸을 Null로 채우고 남겨두자는 것이다.

 

- LEFT : 왼쪽를 남기기

- RIGHT : 오른쪽을 남기기

- FULL : 양쪽 다 남기기

LEFT OUTER JOIN

SELECT	S.sid, S.sname, R.bid
FROM	Sailors S LEFT OUTER FOIN Reserves R
	ON	S.sid = R.sid;

이렇게 하면,,, 일단 sid짝 지을 수 있는애들을 다 데려오고, sailors중에서 짝을 못지은 애들은 bid를 null로 채운채로 오게된다!

sailors

boats

 

쿼리문 결과

Lubber는 예약기록이 없지만, LEFT OUTER JOIN덕분에 살아남았다!!

 

RIGHT OUTER JOIN

이번에는,, 오른쪽 친구를 살린다!

SELECT	R.sid, B.bid, B.bname
FROM	Reserves R RIGHT OUTER FOIN Boats B
	ON	R.bid = B.bid;

이러면,,, 예약기록이 없는 보트도 살아남는다!

reserves

boats

쿼리 결과

예약기록이 없는 보트들도 sid가 Null인채로 살아남았다!

 

FULL OUTER JOIN

양쪽 다 살려준다!!

... 여기서부터는 귀찮으니까 생략...

그치만 위에내용을 이해했으면 이것도 알 수 있을것이다.