DBMS 3 - SQL query

Subquery : 어떤 쿼리의 아래 계층에 중첩되어 쿼리. WHERE 등에서 조건을 주는 중첩 쿼리문이다.

 
Terminology

1. return  : 반환값에 따른 서브쿼리 분류 – 서브쿼리 반환의 형식에 따라 메인 쿼리문 안에서 해당 서브쿼리가 들어갈 수 있는 위치가 결정된다.
 

-single row&column subquery (스칼라 – 단일 값 반환) : SELECT
 

-Multiple row & single comlumn sub query (list) : SELECT & GROUP BY 하나
 

-multiiple row & column (table) : SELECT&WHERE(조건)
 

2. location : 쿼리의 어디에 위치했는가(위와 거의 연관)
 

-scalar scalar (select) : 괄호 친 메인 쿼리 뒤에 들어감. Select는 column 선택이므로 attribute 이름(단일 스칼라값) 만 입력 가능. Count 같은 것
 

-inline view (from) : 이것과 아래는 거의 비슷함. 어떻게 리턴이 되는지 타입에 대한 엄격한 기준은 없음. 내부에서 새로운(temporary) 테이블을 생성하여 함. 해당 subquery가 존재할 때만 존재하는 table이므로 view라고 부름. 주로 테이블 형태로 많이 사용한다.

-nested view (where) : 가장 많이 사용. 주로 리스트(multi row, single comlumn. table이면 안됨) 형태로 많이 사용. where뒤이므로 앞에 있는 것들의 조건을 거는 것이므로.
 

3. correlation with main query : 메인 쿼리와 서브 쿼리의 관계에 따라 분류
 

-상관성 쿼리 : 메인 쿼리의 정보를 가지고 서브 쿼리에서 연산함. 이러면 메인 쿼리가 돌아갈 때 서브쿼리도 다 다시 돌아가야 하므로 속도가 매우 느림. N*M번 실행된다.
 

-비상관성 쿼리 : 메인 쿼리와 서브 쿼리가 연관되어 있지 않음. 따라서 해당 리스트를 한 번 만들어놓으면 메인 쿼리를 돌릴 동안 해당 값을 변하지 않고 계속 사용할 수 있음. N번만 실행된다.
 

따라서 상관성 쿼리를 풀어 줘야 속도가 빨라진다. 비상관성 쿼리는 서브 쿼리 내부의 값을 따로 테이블을 만드는 등으로 서브쿼리를 없앨 수 있다.
 

Keyword. 주로 nested subquery에서 많이 사용할 수 있는 키워드
 

-IN : WHERE 조건에 속해 있는 것. (들어가있나 아닌가)NOT IN을 쓰면 반대. WHERE column IN(subquery) 형식으로 사용.
 

-ALL : 존재성에 대한 것. 어떤 범주의 모든 값과 비교하여 크거나 작은 것을 표현할 때 사용. <ALL이면 lower bound.
 

-ANY(=SOME) : 위와 반대. 하나라도 있는 경우. >ANY 이면 upper bound.
실제 사용할 때 MAX, MIN을 막 쓰고 싶지만 해당 구문 은 사용할 수 있는 장소가 한정 되어 있기 때문에 위처럼 사용하는 것이다. IN/EXIST의 조합으로도 나타낼 수 있으므로 취향의 차이. 

(IN : 안에 존재하는 가) = (=ANY : 어떤 것이라도 같은 것이 존재하는가) -> 둘은 같은 의미!!(안에 들어가는게 있나를 물어보는 것) 심지어 속도 차이도 없다. 왜냐하면 DB 입장에서 동일한 작업이므로.
(NOT IN : 안에 없는 것) = (<>ALL : 모두 하고 조건에 맞지 않는 것) -> 포함되지 않는다 = 모든 것과 맞지 않는다. <> sql에서 같지 않다. !=과 동일.
주의할 점은 NOT IN = <>ANY는 아니고, IN = =ALL이 아니라는 것이다! (역은 성립하지 않음)
 

-EXIST : subquery의 조건에 의해 반환값이 있으면 true, 없으면 false. ‘존재하는가’. 마찬가지로 IN으로 바꿀 수 있다. 그러나 존재 의미에 대한 것을 물을 때는 exist가 조금 더 직관적인 편.
 

-NOT EXIST : 위와 마찬가지로 반대인 경우.
조인으로 할 수 있는 것은 서브쿼리로 할 수 있고, 그 반대도 된다. Join은 테이블을 묶은 것이고, 서브 쿼리는 쿼리를 묶은 것이므로. 다만 조인을 사용하면 코드가 상대적으로 길어지고, 서브 쿼리를 사용하면 짧아진다. (항상 interchangeable 한 것은 아니다)
 

-UNIQUE : 하나밖에 없는가. MySQL은 제공하지 않음. 왜냐하면 다른 방법으로 확인할 수 있기 때문. 그러나 오라클은 해당 구문을 제공함.
 

-WITH : 사용하는 문법과 DB가 처리하는 구조가 다른 것과는 조금 다름. 얘는 서브 쿼리가 아니라, 공통 테이블 표현(CTE)라 함. 테이블을 미리 만들어두고, 해당 테이블을 가지고 아래의 쿼리를 실행하는 것. Inline view와 비슷함. WITH는 변수처럼 사용할 수 있는 반면에, inline view에서는 해당 메인 쿼리 안에서만 사용할 수 있으므로 조인 등의 다른 작업이 불가능하다.
해당 기능은 미리 테이블을 만들어 놓아 캐시처럼 사용할 수 있고, 아래 쿼리를 모두 빨리 처리할 수 있게 하는 장점이 있다. 왜냐하면 이런 것이 없으면 아래 쿼리를 할 때 메모리에 테이블을 올리고 하는 등의 작업이 필요하기 때문이다.
 

DML : insert, update, delete 등의 연산은 아래 서브 쿼리의 결과를 입력하는 것이다. 특히 안에 조건 등을 설정할 때 서브 쿼리를 사용

Join : 9개 중 실제 사용하는 것은 위의 6개. 그리고 조인 연산은 RA의 기본 연산이 아님. (카테시안 곱이므로) 양쪽 테이블의 겹치는 부분(inner join), 그리고 겹치는 부분과 나머지 한쪽(outer join) 

Caresian product : R X S -> FROM에 들어가는 두 attribute가 됨. 결과는 N*M개의 순서쌍이 됨. 다만 해당 전체 순서쌍이 필요하지는 않으므로 해당 집합에서 원하는 것을 골라서 쓰면 됨. 이것은 컴퓨터 내부 연산이고, 실제 사용하는 것은 join연산.

-Theta join : 두개의 from(cross product, 위의 것)에서 WHERE 뒤에 <> 조건을 주는 것. FROM, WHERE 를 쓰는 방법 외에 JOIN, ON을 사용하는 방법도 있다. DB가 JOIN은 FROM, WHERE로 바꿔줌. 

-EQUI join : 여기서는 WHERE에 =을 넣어 같은 값을 뽑음. 위와 같은 방법으로 다르게 표현 가능.

-Natural join : NATURAL JOIN이라고 앞에 붙여야 함. (그냥 위에 두개는 join만 써도 됨) 그 대신 ON을 안써도 됨. 자동으로 같은 것을 찾아서 합쳐진 것이므로 본질적으로 위와 동일함. 다만 내가 직접 ON으로 설정하느냐 아니면 이름이 같은 COLumn을 알아서 찾느냐만 다르다. 다만 NATURAL join은 같은 것은 그냥 생략하고 가져옴. (필요한 컬럼만 가져옴) 다만 앞서 얘기한 것 처럼 이름을 동일하게 맞춰줘야 한다. 만약 같은 컬럼이 없다면 그냥 크로스 조인(카테시안 곱)으로 작용함.
이너 조인은 대부분 이퀴 조인만 사용하게 된다.

-Left outer join : 교집합 부분은 모두 작성하고, 왼쪽 부분(교집합이 아닌 것)은 조건에 맞는 attribute가 없는 것은 해당 값은 버리고 추가한다. 즉, 교집합에다가 왼쪽 값을 그냥 붙이는 것. 그래서 값이 없는 것들은 교집합이 아닌 것.
모든 정보를 보고 싶은 동시에, 어떤 조건에 맞는 다른 테이블의 조건도 보고 싶을 경우 아우터 조인을 사용하여 추가적인 정보를 붙여서 보는 것이다.
Null인 attribute 값도 조인에 포함시켜 통계를 내는 등의 작업을 할 때 사용한다. 

-right outer join : from의 값이 고정되는 경우가 있다. 이럴 때 right outer join을 사용하면 바로 기준이 join 뒤에 오는 것으로 바뀐다. 

-full outer join은 MySQL에서 지원하지 않음. 만약 쓰려면 Left, right를 union하면 된다. 데이터를 그냥 하나의 테이블에 싹 다 묶어서 보여주고 싶을 때 사용. 다만 빈 null 이 많아지면 공간의 낭비가 심해질 수 있다.

-semi join은 Mysql에서 유저가 컨트롤 할 수 없음. DB가 요청한 데이터를 처리하는 방식. 퍼포먼스 향상의 목적으로 효율적으로 데이터를 가져오는 optimition에 사용.

Join과 subquery : 일반적으로 조인을 더 선호함. RA에서 정의된 수학적 연산, 기본적으로 제공되는 기능이기 때문. (기본 5개는 아니지만 제공된 9개에는 포함됨) 반대로 subquery는 수학적 정의가 아니기 때문에 최적화가 매우 어렵다. 그러나 조인은 DB가 알아서 수학적 최적화를 해줄 수 있다. 그래서 조인이 조금 더 빠를 수 있음. (경향성)

Group by : 그룹 바이를 여러 개의 tier로 가능. 이렇게 하면 각각에 사용된 attribute(그룹에 관련된 키)를 반드시 셀렉트 해줘야 됨. 

Case for handling null : 값이 없을 때 0으로 교체. Outer join을 하면 null값 그대로 남음. 이를 해결하는 방법. 단순하게 0값을 덮어씌우면 해당 값은 바뀌지만 여전히 테이블의 기본값이 null이므로 차후에도 버그가 생길 수 있다. 따라서 다른 방법을 써야 한다.
먼저 CASE 문법을 사용할 수 있다. 값이 null일 때와 0일 때 서로 다른 케이스로 나누어서 스위치처럼 작용하게 할 수 있다. 다만 문법이 길고 외우기 어려우며 DB마다 다른 형식이다. (비추천)

두번째로 COALESCE(a,b)라는 함수를 사용할 수 있다. 이는 a attribute의 null 값을 b로 바꿔주는 함수이므로 a에 대입한 column의 null 값을 0으로 대치할 수 있다. 다만 모든 column의 값을 확인하고 일일히 바꿔주는 함수이므로 코스트가 매우 높다는 것을 알아야 한다.
어떤 count의 avg 등 한번 연산한 거에 다시 연산하는 경우 depth를 늘려야 한다. (by subquery).

특정 함수들은 null 값을 제외하거나 0으로 치환하고 해주는 함수들이 있다. 어떤 함수는 null을 받을 수 없는 함수도 있다. 따라서 null 값을 우리가 상황에 따라 핸들링해야 하는지 아닌지 알아야 한다.

Round(a,b) : a를 b번째까지 표기하고 그 밑은 반올림. 소수점에 대한 도메인을 통일해주는 용도로 사용한다. 어떤 문자 등으로 입력된 column을 숫자로 바꿔서 하는 방법은 CASE 문을 이용하여 table을 변환하고, 해당 table에서(from) 값을 가져오는 방식으로 대치할 수 있다.