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
'Spring > Spring Boot' 카테고리의 다른 글
| Spring 이메일 인증 기능 구현하기 2 : 비밀번호 찾기 (0) | 2024.08.20 |
|---|---|
| Spring 이메일 인증 기능 구현하기 1편 : 회원가입시 이메일 인증 (0) | 2024.08.20 |
| SpringJPA : 영속성 컨텍스트, 엔티티 매핑, @OneToMany, @ManyToOne (1) | 2024.06.21 |
| 추상클래스, 인터페이스 (0) | 2024.04.11 |
| 객체 지향 프로그래밍이란? (0) | 2024.03.28 |
