9. 5장 SQL: QUERIES,CONSTRNNTS, TRIGGERS - VIEW, subqueries, WITH, NULL

VIEW

물리적으로(materialized) 존재하지 않는다!!!

편의를 위해, 보안을 위해 만든 논리적인 테이블이다.

=> 근데 사용할때는 테이블마냥 사용하는듯하다..!ㅎ

CREATE VIEW Redcount
AS SELECT	B.bid, COUNT(*) AS scount
	FROM	Boats B, Reserves R
    WHERE	R.bid = B.bid AND B.color='red'
    GROUP BY	B.bid;

이렇게 AS뒤에 원하는 조건을 넣어주면 된다.

 

이렇게 view를 만들어두면,,, 나중에 저런 테이블이 필요할때 복잡하게 적을필요가 없어진다!

 

Subqueries

SELECT, FROM, WEHRE에 각각 서브쿼리를 넣을 수 있다!

근데.. SELECT는 느려서 거의 사용하지 않는다고 하고 설명도 안해주셨다ㅎㅎ

그냥 무시하자^^;;

 

Subqueries in FROM

즉석에서 보기? 같은거래 (view on the fly)

SELECT	bname, scount
FROM	Boats B, (
		SELECT	B.bid, COUNT(*)
        	FROM	Boats B, Reserves R
            WHERE	R.bid = B.bid AND B.color = 'red'
            GROUP BY	B.bid) AS Reds(bid, scount)
WHERE	Reds.bid = B.bid
	AND	scount < 10;

FROM 안에서 "() AS 이름" 이렇게 테이블을 만들어줄 수 있다. 이러면 쿼리 내에서 저 테이블을 재사용?할 수 있다!

(그러니까 굳이 view를 안만든것)

 

WITH : CTE (Common table expression)

이것도 "view on the fly"같은것이다.

=> 쿼리로 만든 "임시적인" 테이블 이다.

그냥 냅다 서브쿼리로 안에 넣어버리면 이해하기 너무 힘들다!! 이렇게 CTE를 만들어서 사용해주면 이해하기에도 더간편하다/

WITH	Reds(bid, scount) AS
(SELECT	B.bid, COUNT(*)
FROM	Boats B, Reserves R
WHERE	R.bid = B.bid ANd B.color = 'red'
GROUP BY	B.bid)

SELECT	bname, scount
FROM	Boats b, Reds
WHERE	Reds.bid = B.bid
	AND scount < 10;

일단 WITH로 CTE Reds를 만들어준다. Reds는... 예약된 보트들 중에, 빨간색 보트의 개수를 담고있다.

똑같은 보트여도, 여러번 예약됐을 수 있으니까 저렇게 group해서 나타낼 수 있다!

그리고 이렇게 만든 Reds를 가져와서 빨간색이고 10번 미만으로 예약됐던 보트의 이름과 몇번 예약됐는지를 출력한다.

=> 이렇게 하니까.. 서브쿼리로 만든것보다 훨씬 이해가 편해진다!

 

WITH다음에는 여러개의 CTE를 만들어줄수도 있다.

WITH	Reds(bid, scount) AS
(SELECT	B.bid, COUNT(*)
FROM	Boats B, Reserves R
WHERE	R.bid = B.bid AND B.color = 'red'
GROUP BY	B.bid),
	UnpopularReds AS
(SELECT	bname, scount
FROM	Boats B, Reds
WHERE	Reds.bid = B.bid
	AND	scount < 10)
    
SELECT	*
FROM	UnpopularReds;

이렇게 여러개의 CTE를 만들어줄 수 있다!

 

Q. 각 나이마다, 최대  rating을 가지는 선원의 정보를 알고싶어용

=> 이럴때도 WITH를 사용해주면... 알아보기 쉽게 작성할 수 있다.

(그룹단위에서 ARGMAX를 알고싶은것)

1. 그룹단위로 MAX rating값, 그때의 age를 추출한다. -> 이걸 CTE로 만들기

2. 각 선원들과 비교해서,, CTE속 rating과 age가 모두 같은애들만 뽑아오면 된다!

WITH maxrating(age, maxrating) AS(
SELECT	age, max(rating)
FROM	Sailors
GROUP BY	age)

SELECT	*
FROM	Sailors S, maxrating M
WHERE	S.age = M.age AND S.rating = M.maxrating;

 

NULL

종종 field값들은 unknown상태일 수 있다 -> SQL에서는 이걸 NULL값으로 처리한다.

근데.... NULL의 존재가 WHERE, Aggregation에 영향을 줄 수 있다!!

하지만... outer join의 영향으로 null의 존재를 무시할수는 없다.

 

NULL의 존재가 생기면.. 논리연산에서 문제가 생긴다.

NULL > 8 은 true인가 false인가?? 둘 다 아니가 그냥 unknown이다!!

=> 즉, 논리연산에서 값의 상태가 binary가 아니라 3개가 된다.

=> 논리연산에서 하나의 오퍼랜드가 NULL이면,,, 결과는 그냥 NULL이 된다!!

 

이게 뭐가 문제냐?? 바로 내가 NULL값을 지닌 튜플을 확인하고 싶을 때 문제가 된다!

그냥 평범하게...

SELECT 	*
FROM	Sailors
WHERE	rating = NULL;

이렇게 하면, rating이 NULL인 친구들이 출력될까? 정답은 아니다!!

논리연산자의 오퍼랜드로 NULL이 들어가면,,, 논리연산의 결과는 무조건 NULL이 된다.

즉, WHERE의 결과는 rating이 몇이든간에 항상 NULL이 되고, 출력되는건 아무것도 없다!

 

그래서 SQL은 NULL값을 확인할 수 있는 명령?을 따로 준비해놨다.

- IS NULL

- IS NOT NULL

-> 이걸로 확인해준다!

SELECT 	*
FROM	Sailors
WHERE	rating IS NULL;

 

이렇게 쿼리를 작성해야지 내가 의도했던 결과가 나온다.

 

논리연산에서의 NULL

NULL의 존재로,,,, 논리연산은 3가지 값을 가지게 된다.

논리연산자를 사용할 때, 좀 더 잘 생각할 필요가 있어졌다.

 

Aggregation에서의 NULL

count, sum, avg에서 NULL은 어떻게 취급될까??

=> NULL은 무시한다!

SELECT	count(*)
FROM	Sailors;

SELECT	count(rating)
FROM	Sailors;

SELECT	sum(rating)
FROM	Sailors;

SELECT	avg(rating)
FROM	Sailors;

이 때, 첫번째 쿼리랑 두번째 쿼리의 결과는 항상 같을까? 정답은 아니다!!

=> rating 하나의 값이 NULL인 튜플은 존재하지만,,, 모든 column에서 NULL값을 가지는 튜플은 존재하지 않는다!

즉, 첫번째 쿼리에서는 진짜 모든 원소들의 개수가 나오고, 두번째 쿼리에서는 rating이 NULL이 아닌 애들의 개수만 나온다.