DB

1. SubQuery

x= 4
y= x+1
y= 5 이처럼 사전에 추출된 내용(x)을 이용한다

-- 문제1> 'han' 이 근무하는 부서 이름은?
select deptno from emp where ename = 'han';
select deptname from dept where deptno='1';
-- 해결책 : 쿼리문에서 반환된 값을 다른 쿼리에 사용
select deptname from dept where deptno=(select deptno from emp where ename = 'han');

-- 문제2> 부서위치가 LA 이거나 BOSTON 인 부서에 속한 사람들의 이름과 직책
select deptno from dept where loc in('LA','BOSTON');
select ename,job from emp where deptno in('2','4');
-- 해결책 
select ename,job from emp
	where deptno in(select deptno from dept where loc in('LA','BOSTON'));

• distinct : 중복 제거
• min / max : 최소, 최대값
• date를 비교할 경우 작은 값이 더 빠른 날

-- 문제5> 부서별로 몇명인지 찾아보자
-- count(column) : 해당 컬럼의 수를 알려 준다
select distinct deptno from emp;
select count(deptno) from emp where deptno =1; -- 4
select count(deptno) from emp where deptno =2; -- 5
select count(deptno) from emp where deptno =4; -- 1

select deptname from dept where deptno = 1;
select deptname from dept where deptno = 2;
select deptname from dept where deptno = 4;

-- 부서이름과 그에따른 수를 따로 확인 하기가 어렵다.
-- 서브쿼리로 추출한 결과를 하나의 컬럼처럼 보여준다.
-- 이렇게 데이터의 일부 처럼 사용하는 서브쿼리를 상하관계 쿼리라고 부른다.
select 
	deptname,
		(select count(deptno) from emp where deptno =1) as cnt
		from dept where deptno =1;

2. JOIN

서브쿼리만 사용 할 경우 쿼리가 굉장히 복잡해 져 JOIN 을 활용 할 수 있다

• 둘이상의 테이블을 연결해서 검색하는 방법
• 공통되는 컬럼이 적어도 하나 이상은 존재해야 한다
• 일반적으로 pk 와 fk 를 이용하여 join 한다

• CROSS JOIN
  가장 기본적인 JOIN 방법 카다시안곱을 수행한다(모든 경우의수)
  FROM [테이블1] CROSS JOIN [테이블2]
  cross join 은 생략이 가능하다 (,)

• Equi join
  가장 일반적인 Join 등가조인 과 내부조인 이있다.

  • 등가조인 (=)
    양쪽 테이블에 전부 있을 경우 가져온다
  • 내부조인(INNER JOIN)
    join 에 대한 조건을 on 으로 사용하고
    join 하는 테이블 사이에 inner join을 넣는다.(inner는 생략 가능)
    on 대신 using 을 조건으로 사용 할 수 있으며, 안에는 사용될 컬럼명,서브쿼리를 넣을 수 있다

• NATURAL JOIN
  동일한 값을 찾는 컬럼을 내부적으로 자연스럽게 조인 하므로 조건을 주지 않는다.
  다만 동일한 컬럼은 단축명으로 주지 말자

select **deptno**, d.deptname, e.ename 
	from emp e natural join dept d;

• OUTER JOIN
  Equi join 에서 할수 없는 걸 한다.
  기준(데이터를 더 보여줘야 할 곳)이 되는 곳을 지목

-- outer는 생략 가능
-- FROM [tableA][LEFT|RIGHT|FULL] OUTER JOIN [tableB] ON [조건절]
-- LEFT OUTER JOIN (왼쪽을 기준으로 더 있는 값을 보여줌)
-- RIGHT OUTER JOIN (오른쪽을 기준으로 더 있는 값을 보여줌)
-- FULL OUTER JOIN (양쪽 서로에게 없는 값들을 보여줌 - mariaDB 에서는 지원X)

• SELF JOIN
  Equi join 과 같으나 대상 테이블이 스스로라는 것이 차이점이다.

3. 집합(SET)

SET 은 집합을 의미하며 UNION 은 '중복'을 허용하지 않는 합집합 이다.

• 중복 제거시 속도가 저하되어 사용을 권장하지 않는다.

• 1. UNION (중복을 제거한 합집합)
     동일한 컬럼이 하나는 있어야 하며 데이터간의 중복을 제거후 가져온다

• UNION을 활용 하여 FUUL OUTER JOIN 효과를 낼 수 있다.

select d.deptno, d.deptname, e.ename 
	from emp e  left outer join dept d on e.deptno = d.deptno
union 
select d.deptno, d.deptname, e.ename 
	from emp e  right outer join dept d on e.deptno = d.deptno;

• 2. UNION ALL (중복을 제거하지 않는 합집합)
• 3. INTERSECT (교집합)
     양쪽에 존재하는 값만 가져온다(Equi join과 같다)
• 4. MINUS (차집합)
     mariaDB 에서는 차집합 함수가 없어 NOT IN을 활용 한다.

-- emp - dept =
select deptno from emp 
	where deptno  not in (select deptno from dept);
-- dept - emp =
 select deptno from dept 
	where deptno  not in (select deptno from emp);

UNION은 중복 제거시 속도가 느려 사용을 권장 하지 않았다 이에 대한 해결법은

select distinct u.deptno 
	from (select deptno from emp union all select deptno from dept) u
	order by u.deptno;
-- union all 로 데이터를 확보 한 후 distinct로 중복을 제거하여 준다

4. IN & EXISTS

• IN : or 조건 검색 결과를 가져온다
  ( = ) 비교만 가능

• EXISTS :메인쿼리의 비교조건이 서브쿼리의 결과중 만족하는 값이 하나라도 존재하면 참(1)/거짓(0)을 반환
  **메인쿼리가 먼저 실행되고, 이후 서브쿼리가 실행된다 (데이터를 먼저 확보하기 때문에 IN보다 처리속도가 빠르다)

-- 모든 결과 보여줌(exists 값이 1이므로)
select ename, job, deptno from emp 
	where	exists(select deptno from dept where loc='LA' or loc='BOSTON');
-- 메인쿼리를 실행해 데이터를 가져오고, exists 를 통해 0 / 1 값을 통해 보여줄지 말지를 결정
select * from emp where 1;
select * from emp where 0;

5. ANY & ALL

• ANY : IN 과 비슷하다. 다만 부등호를 사용 할 수 있다
  • =ANY : IN 과 같은 효과
  • -- >ANY : 최소값 보다 크면 ...
  • -- <ANY: 최대값 보다 작으면...
• ALL : ANY 와 같지만 ANY 가 or 조건이라면 ALL 은 AND 조건이다.

-- 1) = ALL : 모두 같으면...
-- deptno = 1 AND deptno = 3
select ename,job from emp 	
	where deptno = all(select deptno from dept where loc='NEWYORK');
-- deptno = 1 OR deptno = 3
select ename,job from emp 	
	where deptno = any(select deptno from dept where loc='NEWYORK');

select deptno from dept where loc='NEWYORK'; -- 1,3
-- ALL 은 and 조건 이기 때문에 1 과 3에 대해서 deptno = 1 AND deptno = 3 이라는 조건으로 검색하게 된다.
-- 그렇기 때문에 이를 만족하는 값이 나올 수 없다.

-- 그래서 all 을 사용할 때는 같은 컬럼으로 2개 조건을 걸어서는 안된다.

-- >ALL : 최대값 보다 크면...
-- < ALL : 최소값 보다 작으면...