Spring/Spring Boot

DB - JOIN, SUBQUERY

경토리 2024. 5. 3. 17:07
728x90

JOIN

  • JOIN 이란 - 하나 이상의 테이블로부터 연관된 데이터를 검색해 오는 방법

Cartesian Join

  • Join에 대한 조건이 생략되거나 잘못 기술되어 한 테이블에 있는 모든 행들이 다른 테이블에 있는 모든 행들과 Join이 되어서 얻어진 경우를 Cartesian Product라 한다.
  • 두 테이블의 합집합이라 생각하면 된다.
  • select * from employees, departments;
  • Cartesian Product를 얻지 않기 위해서 반드시 WHERE 절을 써 준다.
  • (JOIN 하는 테이블의 수 - 1) 개의 JOIN 조건이 필요하다.

Simple Join

SELECT t1.col1, t1.col2, t2.col1 ... FROM Table1 t1, Table2 t2
WHERE t1.col3 = t2.col3
  • FROM 절에 필요로 하는 테이블을 모두 적는다.
    • 컬럼 이름의 모호성을 피하기 위해 테이블 이름으로 직접 지칭 가능 (어느 테이블에 속하는지 알 수 없는 경우가 있을 수 있으므로 Table 이름에 Alias 사용)
    • 적절한 Join 조건을 Where 절에 부여 (일반적으로 테이블 개수 -1 개의 조인 조건이 필요)
    • 일반적으로 PK와 FK간의 = 조건 이 붙는 경우가 많다.

Join 종류

종류 설명

Cross Join (Cartesian Product) 모든 가능한 쌍이 나타남
Inner Join 조건을 만족하는 튜플만 나타남
Outer Join Join 조건을 만족하지 않는 튜플 (짝이 없는 튜플)도 null과 함께 나타남
Theta Join 조건(theta) 에 의한 조인 / (FK가 아닌 다른걸 이용 ?)
Equi-Join Theta Join & 조건이 Equal (=)
Natural Join Equi-Join & 동일한 Column 명 합쳐짐
Self Join 자기 자신과 조인

EQUI-JOIN

  • 컬럼에 있는 값이 정확하게 일치하는 경우에 = 연산자를 사용하여 JOIN
SELECT 테이블명.컬럼명, 테이블명.컬럼명. …
FROM 테이블1, 테이블2
WHERE 테이블1.컬럼1 = 테이블2.컬럼2
  • 테이블명.컬럼명 - 검색해올 데이터가 어디에서 오는지 테이블과 컬럼을 밝혀둔다.
  • 테이블1.칼럼1 = 테이블2.컬럼2 - 두 테이블 간에 논리적으로 값을 연결시키는 칼럼 간의 조건을 기술한다.

EQUI-JOIN의 문형

  • 컬럼에 있는 값들이 정확히 일치하는 경우에 = 연산자를 사용해서 조인
  • 일반적으로 PK-FK 관계에 의하여 JOIN이 성립
  • WHERE절 or ON절을 이용
  • 액세스 효율을 향상시키고 좀 더 명확히 하기 위해 칼럼이름 앞에 테이블 이름을 밝힌다.
  • 같은 이름의 칼럼이 조인대상 테이블에 존재하면 반드시 테이블 이름을 밝혀줄 것 !
  • JOIN을 위한 테이블이 N개라 하면, JOIN을 위한 최소한의 = 조건은 N-1 이다.
# 사원 이름과 부서명을 출력하라.
select emp.ename, dept.dname from emp, dept;
select e.ename, d.dname from emp e, dept d;
select ename, dname from emp e, dept d;

# 중복되는 컬럼명 앞에는 반드시 테이블을 명시해야함
select ename, dname, e.deptno from emp e, dept d;

select e.ename, d.dname from emp e, dept d
where e.deptno = d.deptno;

# 모든 사원의 사번, first_name, email, department_name을 조회하라
select e.employee_id, e.first_name, e.email, d.department_name
from employees e, departments d
where e.department_id = d.department_id;

추가적인 조건 기술

  • WHERE 절에 JOIN 조건 이외의 추가적인 조건을 가질 수 있다.
  • 조인을 만족하는 데이터 중 특정 행만 선택하여 결과를 얻고 싶을 때 추가조건을 AND로 연결한다.
  • 사원의 이름과 부서명을 출력하라. 단, location_id 가 1800인 경우만 출력하라.
  • select concat(e.first_name, ' ', e.last_name) as 이름, d.department_name, l.city from employees e, departments d, locations l where e.department_id = d.department_id and d.location_id = l.location_id and l.location_id = 1800;

조건(theta) Join

  • 임의의 조건을 Join 조건으로 사용가능
  • Non-Equi Join 이라고도 한다.
  • # 이름, 급여, 등급을 조회하라 select e.ename, e.sal, s.grade from emp e, SALGRADE s where e.sal BETWEEN s.LOSAL AND s.HISAL;

Natural Join

  • 두 테이블에 공통 칼럼이 있는 경우 별다른 조인 조건없이 공통 칼럼처럼 묵시적으로 조인이 되는 유형
  • ANSI / ISO SQL1999를 따르는 ANSI JOIN 문법
select e.ename, d.dname
from emp e natural join dept d;

문제점

  • 조인하고자 하는 두 테이블에 같은 이름의 칼럼이 많을 때, 위와 같을 시 특정한 칼럼으로만 조인하고 싶다면 USING 절을 사용해서 기술한다.
  • select e.ename, d.dname from emp e join dept d using(deptno);

INNER JOIN - JOIN ~ ON

  • 공통된 이름의 칼럼이 없는 경우 가장 보편적으로 사용할 수 있는 유형이다.
  • WHERE 절에 일반조건만 쓸 수 있게하고, 조인 조건은 ON에 두어 보다 의미를 명확히 하고 알아보기도 쉽다.
  • ON 부분을 WHERE 절에서 작성 가능하다.
  • select e.ename, d.dname from emp e join dept d on (e.deptno = d.deptno) where d.deptno = 20 ;

Outer Join

  • Join 조건을 만족하지 않는(짝이 없는) 튜플의 경우 Null을 포함하여 결과를 생성
  • 모든 행이 결과 테이블에 참여

종류

  • Left Outer Join - 왼쪽의 모든 튜플은 결과 테이블에 나타남
  • Right Outer Join - 오른쪽의 모든 튜플은 “”
  • Full Outer Join - 양쪽 모두 결과 테이블에 참여

→ 왼쪽 테이블의 Null 값을 포함하는 튜플도 모두 보고 싶어요 : Left Outer Join

  • 오라클의 경우, NULL이 올 수 있는 쪽 조건에 + 를 붙여준다.
select e.ename, d.dname
from emp e left outer join dept d using(deptno); # 왼쪽의 모든 튜플

select e.ename, d.dname
from emp e right outer join dept d using(deptno); # 오른쪽의 모든 튜플

select e.ename, d.dname
from emp e left outer join dept d using(deptno)
union
select e.ename, d.dname
from emp e right outer join dept d using(deptno); # 양쪽 모든 튜플
  • MySQL의 경우, FULL OUTER JOIN이 아닌 UNION 연산을 통해 가능하다.

JOIN 문법 정리

SELECT table1.column, table2.column
FROM table1

[CROSS JOIN table2] |

[NATURAL JOIN table2] |

[JOIN table2 USING (column_name)] |

[JOIN table2
ON(table1.column_name = table2.column_name)] | [LEFT|RIGHT|FULL OUTER JOIN table2
ON (table1.column_name = table2.column_name)];
  • left outer → left / right outer → right 로 축약해서 사용가능

SELF JOIN

  • 하나의 테이블을 서로 다른 테이블로 생각하여 JOIN 하는 것
# 사원이름과 매니저 이름을 출력하라.
select e.ename 사원명, m.ename 매니저이름
from emp e, emp m
where e.mgr = m.empno;

# 사원이름, 매니저 이름을 출력하세요. 단, 매니저가 없는 사원도 출력하시오.
select e.ename 사원명, m.ename 매니저이름
from emp e
left outer join emp m
on e.mgr = m.empno;

🔎 SubQuery

  • 하나의 SQL 질의문 속에 다른 SQL 질의문이 포함되어 있는 형태
  • 쿼리 하나로는 원하는 결과를 얻어내지 못하는 경우에 사용
# SMITH가 속한 부서의 급여 평균을 알고 싶다.
# 1. 스미스의 부서 번호는?
# 2. 해당 부서 번호의 급여 평균

select avg(sal)
from emp
where deptno = (select deptno from emp where ename = 'SMITH');
  • ✔ 서브쿼리 짜는 것을 익히기 위해선 각 조건들을 작은 단위로 (쿼리 하나씩) 뽑아내는 연습을 하는 것이 좋겠다.

Single-Row Subquery

  • Subquery의 결과가 한 개의 ROW인 경우
  • Single-Row Operator 를 사용해야 함 - = , > , >= , < , <= , <>
# 사원의 평균 급여보다 작은 급여를 받는 사원의 이름과 급여를 출력
select ename, sal
from emp
where sal < (select avg(sal)
             from emp);

# 부서이름이 SALES인 부서의 사원 이름과 부서 번호를 출력
select ename, deptno
from emp
where deptno = (select deptno
                from dept
                where dname = 'SALES');

Multi-Row SubQuery (Feat. IN, ANY, ALL)

  • Subquery의 결과가 둘 이상의 ROW
  • Multi-Row에 대한 연산을 사용해야 함 - ANY , ALL , IN , EXIST …
# 아래의 경우는 오류가 발생한다.
SELECT *
FROM emp
WHERE ename = (SELECT MIN(ename)
               FROM emp GROUP BY deptno);

# 아래와 같이 in 연산을 사용하여 그룹 내에 있는지
# 확인하는 식으로 사용해야 한다.
SELECT *
FROM emp
WHERE ename in ('KANG', 'CLARK', 'ADAMS', 'ALLEN');

# 따라서 다음과 같이 해결할 수 있다.
SELECT *
FROM emp
WHERE ename in (SELECT MIN(ename)
               FROM emp GROUP BY deptno);

ANY

  • 다수의 비교값 중 한개라도 만족하면 true
  • IN 과 다른 점은 비교 연산자를 사용한다는 점
  • SELECT ename, sal, deptno FROM emp WHERE ename = ANY (SELECT MIN(ename) FROM emp GROUP BY deptno); # 아래 쿼리는 sal > 950 과 같은 결과이다. SELECT * FROM emp WHERE sal > ANY(950, 3000, 1250)

ALL

  • 전체 값을 비교해서 모두 만족해야만 true
  • Oracle은 오류가 발생하지 않지만, MySQL은 Subquery에서만 사용 가능하다.
  • # 아래의 쿼리는 결과가 없다. 모두를 만족할 수는 없기 때문 # MySQL에서는 값에 대해서는 ALL을 사용할 수 없다. 여기선 단지 예제일뿐 ! SELECT * FROM emp WHERE sal = ALL(950, 3000, 1250) # 결국 아래와 같이 사용된다. select * from emp where sal < all (select sal from emp where deptno in (30, 10));

Correlated Query

  • Outer Query와 Inner Query가 서로 연관되어 있다.
  • 해석방법
    • Outer query의 한 Row를 얻는다.
    • 해당 Row를 가지고 Inner Query를 계산한다.
    • 계산 결과를 이용, Outer query의 WHERE 절을 evaluate
    • 결과가 참이면 해당 Row를 결과에 포함시킨다.
# 사원의 이름, 급여, 부서 번호를 출력하시오. 단 사원의 급여가
# 그 사원이 속한 부서의 평균 급여보다 큰 경우만 출력하시오.
SELECT o.ename, o.sal, o.deptno
FROM emp o
WHERE o.sal > (SELECT AVG(i.sal)
               FROM emp i
               WHERE i.deptno = o.deptno);

# 각 부서별로 최고 급여를 받는 사원을 출력하라.
# 1
select deptno, empno, ename, sal
from emp
where (deptno, sal) in (select deptno, max(sal)
                        from emp
                        group by deptno);

# 2
select a.deptno, a.empno, a.ename, a.sal
from emp a,
     (select b.deptno, max(b.sal) msal
      from emp b
      group by deptno) c
where a.deptno = c.deptno
  and a.sal = c.msal;

# 3
SELECT deptno, empno, ename, sal
FROM emp e
WHERE e.sal = (SELECT max(sal)
               FROM emp WHERE deptno = e.deptno);

Set Operator

  • 두 질의의 결과를 가지고 집합 연산
  • UNION , UNION ALL , INTERSECT , MINUS
  • 우선 임의로 테이블 생성 a : {1, 2, 3} / b : {2, 3, 4}
  • create table a ( name int ); create table b ( name int ); insert into a value ('1'); insert into a value ('2'); insert into a value ('3'); insert into b value ('2'); insert into b value ('3'); insert into b value ('4');
# UNION : 합집합 (중복 제거)
select * from a union select * from b;
# UNION ALL : 합집합 (중복 포함)
select * from a union all select * from b;
# INTERSECT : 교집합
# - MySQL에서는 지원하지 않는다.
select * from a intersect select * from b; # 불가
select a.name from a, b where a.name = b.name;
# minus : 차집합
# - MySql 에서 지원하지 않는다.
select * from a minus select * from b;
select a.name from a where a.name not in (select b.name from b);

RANK() 함수

  • 요소에 대해 순서를 매기는 함수
  • MySQL 8 이상에서 사용가능
SELECT sal, ename,
			rank() over(order by sal desc) AS ranking
FROM emp;

 

728x90