조인.... 일단 학부시절에 배운건 ANSI JOIN이었음. 학부시절에 DB 배울때.. 아마 MySQL을 갖고 했던 것 같음. 그래서 Oracle sql은 전혀 배운 기억이 없음.
고론데... sql에 alias 주는거가... 뭔가 이거랑 뭔가가 꼬여서 지금 뭔가 이상함.. 고로.
join을 처음부터 다시 써보도록 하겠음.. (쓰는게 중요한건 아니겠지만... 쨋든..)
일단, Join에는 오라클에서 사용하는 Oracle join과 범용적으로 사용하는 ANSI Join이 있다는 것.
그리고 그것은 크게 Inner Join과 Outer Join으로 나뉘는데
Inner Join은 두 테이블 내에 함께 공존하고 있는 키 값들만을 갖고 새로운 테이블을 만들어 내는 것이고
Outer Join은 Left나 Right, 하나의 테이블의 키 값을 기준으로 새로운 테이블을 만드는 것입니다.
Full outer join과 같은 경우는 두 테이블을 그냥 공존하는 키 값이 있든 없든 그냥 합쳐 놓는 것이구요.
| 종류 | 설명 | |
| 오라클 조인 | cartesian product | 조인 조건을 생략하거나 조인이 잘못된 경우에 발생된다. |
| equi 조인 | 기본키(Primary Key)와 참조키(Foreign Key)을 사용하여 반드시 조건이 일치하는 데이터만 조회하는 방법이다. | |
| non-equi 조인 | 조건이 반드시 일치하지 않더라고 범위에 포함되는 경우에 조회하는 방법이다. | |
| self 조인 | 자신의 테이블과 조인하는 방법이다. | |
| outer 조인 | 조건에 일치하지 않아도 조인 결과에 포함시키는 방법이다. | |
| ANSI 조인 | cross 조인 | 오라클 조인의 cartesian product와 동일한 방법이다. |
| natural 조인 | 오라클 조인의 equi 조인과 동일하며 자동으로 두 개의 테이블에서 일치하는 컬럼을 찾아서 조인된다. | |
| using(컬럼) | 오라클 조인의 equi 조인과 동일하며 명시적으로 일치하는 컬럼을 작성한다. | |
| join ~ on절 | 오라클 조인의 non-equi 조인과 동일한 조인방법이다. on절에 조인조건을 명시한다. | |
| left | right | full outer 조인 | 오라클 조인의 outer 조인과 동일한 방법이다. | |
| self 조인 | 오라클 조인의 self 조인과 동일한 방법이다. |
참조 당하는 table을 master table, 참조하는 table을 slave, child table이라고 부릅니다.
foreign key = 반드시 master table의 column 중 primary key 혹은 unique column만 참조할 수 있습니다.
= 참조하는 퀄럼의 값만 저장 가능하며, Null 값도 저장 가능합니다.
먼저, 오라클 Join ==> Where 절에서 연산자를 이용하여 join
1. Equi 조인 (equivalent join) : ANSI join의 inner join의 역할입니다.
< 문법 >
SELECT 테이블1.컬럼 , 테이블2.컬럼
FROM 테이블1 , 테이블2
WHERE 테이블1.공통컬럼 = 테이블2.공통컬럼;< 예제 >
--사원 테이블(employees)과 부서 테이블(departments)을 Equi 조인을 사용하여 사원명과 부서명을 출력하는 SQL문
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;
-- foreign key, 얘가 얘의 값을 참조해서 가져옴 department table의 primary key
--테이블에 별칭(alias) 사용 가능합니다.
SELECT alias1.컬럼 , alias2.컬럼
FROM 테이블1 alias1, 테이블2 alias2
WHERE alias1.공통컬럼 = alias2.공통컬럼;--사원 테이블과 부서 테이블 조인시 테이블 별칭(alias)을 사용한 SQL문
SELECT emp.last_name, department_name, emp.department_id
FROM employees emp, departments dept
WHERE emp.department_id = dept.department_id;
--검색 조건을 추가할 수 있습니다.
--사원 테이블과 부서 테이블 조인시 검색 조건으로 이름이 Whalen 사원만 검색하는 조건이 추가된 SQL문
SELECT emp.last_name,salary,department_name
FROM employees emp, departments dept
WHERE emp.department_id = dept.department_id
AND last_name='Whalen';
2. Non-Equi 조인 : WHERE 절에 조인 조건을 지정할 때 동등 연산자(=) 이외의 비교 연산자를 사용하는 조인
Between A and B 연산자를 사용한 Non-Equi join
--사원들의 월급 등급을 조회 할 수 있는 SQL문
SELECT last_name, salary, grade_level
FROM employees e, job_grades g
WHERE e.salary BETWEEN g.lowest_sal AND g.highest_sal;
3. self 조인 : 자기 자신의 테이블을 join하여 새로운 테이블 생성
--사원의 관리자명 출력
SELECT e.last_name 사원명, m.last_name 관리자명
FROM employees e, employees m
WHERE e.manager_id = m.employee_id;
--실습 : 상원명, 사원의 관리자명, 관리자의 관리자명 출력? (3개 tables 조인)
SELECT e.last_name 사원명, m.last_name 관리자명, mm.last_name "관리자의 관리자명"
FROM employees e, employees m, employees mm
WHERE e.manager_id = m.employee_id and m.manager_id = mm.employee_id;
4.Oracle Outer 조인 : 한 테이블을 기준으로 조인. null 값을 포함할 수 있음. (+) 연산자를 사용.
--(+) 연산자는 조인하고자 하는 테이블 중에서 한 쪽만 사용 가능, 일치하는 데이터가 없는 쪽에 지정 (기준 테이블의 반대)
< 문법 >
SELECT 테이블1.컬럼 , 테이블2.컬럼
FROM 테이블1 , 테이블2
WHERE 테이블1.공통컬럼 = 테이블2.공통컬럼 (+);< 예제 >
--(+) 연산자는 조인하고자 하는 테이블 중에서 한 쪽만 사용 가능, 일치하는 데이터가 없는 쪽에 지정
--Employees 테이블에 있는 Department_Id null인 grant를 Department table과 join하여 표현하기 위해서 일치하는 데이터가 없는 department에 grant가 갖고 있는 department_id null값을 갖은 가상 데이터 값을 넣어줌 (+)
SELECT emp.last_name, department_name, emp.department_id
FROM employees emp, departments dept
WHERE emp.department_id = dept.department_id(+);
ANSI 조인 - 범용 DB Langauge
1. Natural Join : (=Oracle Equi join), From 절에서 join, inner join
Table들을 자동으로 Inner join => 두 개 이상의 common columns가 있다면 결과 Table의 값을 보장할 수 없다.
< 문법 >
SELECT 테이블1.컬럼 , 테이블2.컬럼
FROM 테이블1 NATURAL JOIN 테이블2
[WHERE 검색조건];< 예제 >
--사원 테이블과 부서 테이블 조인, natural join --> 동일 컬럼이 2개, manage_id, department_id, => 오류 결과 테이블
SELECT last_name,department_name, department_id
FROM employees NATURAL JOIN departments;
(정상 테이블에는 Whalen이 포함됨)
--alias 사용 가능
(natural join은 알아서 공통column을 지정하여 join하기때문에 공통column을 지정하면 안 된다.)
--alias 사용한 Natural join
SELECT last_name, department_name, department_id --e.department_id로 common column지정 => Error
FROM employees e NATURAL JOIN departments d;
----Natural join의 검색 조건
SELECT last_name,department_name, department_id
FROM employees e NATURAL JOIN departments d
WHERE department_id=90;
2. JOIN ~ USING : natrual join에서 두 개 이상의 공통 컬럼이 존재할 때 -> 이상한 table 출력
==>using절을 이용하여 join할 1개 column 지정.
< 문법 >
SELECT 테이블1.컬럼 , 테이블2.컬럼
FROM 테이블1 [INNER] JOIN 테이블2 USING(공통컬럼)
[WHERE 검색조건];< 예제 >
SELECT last_name,department_name, department_id
FROM employees e JOIN departments d
USING(department_id);
3. ON 절 : 만약 Non-Equi 조인이나 임의의 조건으로 조인할 경우에는 ON절을 사용.
=> 내부에 공통 column이 반드시 일치하지 않는 경우 사용. (natrual join이나 using절을 쓸 수 없을 때)
< 문법 >
SELECT 테이블1.컬럼 , 테이블2.컬럼
FROM 테이블1 [INNER] JOIN 테이블2 ON 조인조건
[WHERE 검색조건];< 예제 >
But, Natural join, join ~ using절을 사용할 수 있다면, on절도 사용 가능함.
SELECT last_name, department_name, e.department_id
FROM employees e JOIN departments d
ON e.department_id = d.department_id;SELECT last_name, department_name, department_id
FROM employees e JOIN departments d using (department_id); 
하지만, 아래와 같이 다른 column명으로 join할 때는, ON절을 필수적으로 사용해야만 함.
SELECT e.last_name 사원명, m.last_name 관리자
FROM employees e INNER JOIN employees m
ON e.manager_id = m.employee_id;
혹은, 아래와 같이 연산자를 이용한 join을 할 때, ON절이 필수적임.
--사원들의 월급 등급을 조회 할 수 있는 SQL문 (on join)
SELECT last_name, salary, grade_level
FROM employees e join job_grades g
on e.salary BETWEEN g.lowest_sal AND g.highest_sal;
3개 table을 join할 수도 있음
--질문 : 사원명, 부서명, salary 등급 보여주는 on join문 ( natural on join )
select last_name 사원명, department_name 부서명, grade_level 등급
from employees e inner join departments d on e.department_id = d.department_id --EMPLOYEES와 DEPARTMENTS에 동일 컬럼이 2개 이상 존재
inner join job_grades on salary between lowest_sal and highest_sal;그리고 코드에서 보이다싶이 동일 colunm을 on절을 사용함
=> using절로 대체 가능. (다수의 동일 컬럼 존재시에는 문제 가능)
select last_name 사원명, department_name 부서명, grade_level 등급
from employees inner join departments using(department_id)
inner join job_grades on salary between lowest_sal and highest_sal; 
4. Cross 조인 : 각 테이블의 행 갯수를 서로 곱한 결과가 반환된다. = cartesian Product = trash
S
SELECT last_name,department_name, e.department_id
FROM employees e CROSS JOIN departments d;
5. LEFT OUTER | RIGHT OUTER | FULL OUTER 조인
LEFT | RIGHT | FULL 키워드를 이용하며 한 쪽 테이블 또는 양쪽 테이블 지정이 모두 가능하다
LEFT JOIN : left table을 기준으로 left table에 null값을 가진 값도 표현
RIGHT JOIN : right table을 기준으로 right table에 null 값을 가진 값도 표현
< 문법 >
SELECT 테이블1.컬럼 , 테이블2.컬럼
FROM 테이블1 LEFT|RIGHT|FULL OUTER JOIN 테이블2
ON 조인조건 | USING(컬럼)
[WHERE 검색조건];< 예제 >
--모든 사원의 부서 정보 출력? 사원 중 부서가 null인 값도 표현 (grant)
select last_name, department_name, e.department_id
from employees e left outer join departments d -- left table을 기준으로 left table에 null값을 가진 값도 표현
on e.department_id = d.department_id;
--모든 부서의 사원 정보 출력 Grant는 department에 null이라 grant는 나오지 않음
select last_name, department_name, e.department_id
from employees e right outer join departments d -- right table을 기준으로 right에 null 값을 가진 값도 표현
on e.department_id = d.department_id;
--모든 사원, 모든 부서 정보 출력
select last_name, department_name, e.department_id
from employees e full outer join departments d
on e.department_id = d.department_id;
서브 쿼리 ( subquery ) : query = select문, 여러 개의 SELECT문장을 하나로 합쳐서 만든 하나의 실행 가능한 SQL문
< 문법 >
SELECT select_list
FROM 테이블
WHERE 컬럼명 연산자 (SELECT select_list
FROM 테이블);--Whalen 사원의 월급보다 많이 받는 사원을 조회하기 위한 SQL문
--1
SELECT salary
FROM employees
WHERE last_name='Whalen';--2
SELECT last_name,salary
FROM employees
WHERE salary >= 4400;--서브 쿼리로 한 번에 해결 가능
SELECT last_name,salary
FROM employees
WHERE salary >= (SELECT salary
FROM employees
WHERE last_name='Whalen');
서브쿼리 종류
| 종류 | 설명 | 사용 가능 연산자 |
| 단일행 서브쿼리 | 서브쿼리 실행 결과가 한 개의 행을 반환한다 | =, >, >=, <, <= ,!= 와 같은 비교 연산자 |
| 복수행 서브쿼리 | 서브쿼리 실행 결과가 복수 개의 행을 반환한다. | IN, ANY, ALL, EXIST 연산자 |
일반적으로 서브쿼리라고 하면 WHERE절에 사용되는 서브쿼리를 의미
하지만, WHERE절 뿐만 아니라 SELECT절, FROM 절, HAVING 절, ORDER BY절, UPDATE 문, INSERT 문, DELETE 문에 사용될 수 있다.
--단일행 서브쿼리 : 서브쿼리가 실행되어 반드시 한 개의 행을 반환하는 서브쿼리
--대표적으로 기본키(Primary Key)를 이용하거나 MAX,MIX,SUM 같은 그룹함수를 사용하여 검색하는 경우로서 반드시 단일행 연산자를 사용하여 메인 쿼리와 연산되어야
SELECT last_name,salary
FROM employees
WHERE salary >= (SELECT AVG(salary)
FROM employees);
--복수행 서브쿼리 : 서브쿼리가 실행되어 반환되는 결과가 하나 이상의 행일 때 사용하는 서브쿼리, 반드시 복수형 연산자와 함께 사용
--복수행 연산자
| 종류 | 설명 |
| IN | 메인 쿼리와 서브 쿼리가 IN 연산자로 비교한다. 서브쿼리 결과값이 복수개인 경우에 사용된다. |
| ANY | ANY 연산자는 복수행 서브쿼리에서 > 또는 < 같은 비교 연산자를 사용하고자 할 때 사용되며 검색 조건이 하나라도 일치하면 참이다. |
| ALL | ALL 연산자는 복수행 서브쿼리에서 > 또는 < 같은 비교 연산자를 사용하고자 할 때 사용되며 검색 조건의 모든 값이 일치하면 참이다. |
| EXIST | 서브 쿼리의 반환값이 존재하면 메인 쿼리를 실행하고 반환값이 없으면 메인 쿼리를 실행하지 않는다. |
1. IN
--문제3. last_name이 'Fay' 또는 'Whalen'과 동일한 salary를 가진 사원 출력
select last_name, salary
from employees
where salary in (select salary
from employees
where last_name = 'Fay' or last_name = 'Whalen')
order by salary; 
--2. ALL : 복수행 서브쿼리에서 비교 연산자 사용
--2가지 형태
--1) where 변수 > ALL 복수형 서브 쿼리
--예> where salary > all ( 800, 1000, 1300 )
-- where salary > max (sal)
-- 최대 salary 보다 큰 salary 조회.. ==> 최대값보다 큰 값 조회
--2) where 변수 < ALL 복수형 서브 쿼리
--예> where salary < all ( 800, 1000, 1300 )
-- where salary < min (sal)
-- 최소 salary 보다 작은 salary 조회.. ==> 최소값보다 작은 값 조회
--3. ANY : 복수행 서브쿼레이서 비교 연산자 사용
--2가지 형태
--1) where 변수 > ALL 복수형 서브 쿼리
--예> where salary > any ( 800, 1000, 1300 )
-- where salary > min (sal)
-- 최소 salary 보다 큰 salary 조회.. ==> 최소값보다 큰 값 조회
--2) where 변수 < ALL 복수형 서브 쿼리
--예> where salary < any ( 800, 1000, 1300 )
-- where salary < max (sal)
-- 최대 salary 보다 작은 salary 조회.. ==> 최대값보다 작은 값 조회
--4. EXIST : 서브 쿼리의 반환값이 존재하면 메인 쿼리를 실행하고 반환값이 없으면 메인 쿼리를 실행하지 않는다.
SELECT last_name, department_id, salary
FROM employees
WHERE EXISTS (SELECT employee_id
FROM employees
WHERE commission_pct IS NOT NULL);
SELECT last_name, department_id, salary
FROM employees
WHERE EXISTS ( SELECT employee_id
FROM employees
WHERE salary > 9999999 );
--다중 컬럼 서브쿼리 : 서브쿼리에서 여러 개의 컬럼값을 검색하여 메인쿼리의 조건절과 비교하는 서브쿼리
--메인쿼리의 조건절은 서브쿼리의 컬럼과 일대일 매칭이 되어야 함.
SELECT last_name, department_id, salary
FROM employees
WHERE (department_id, salary) IN ( SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id )
ORDER BY 2;
--인라인 뷰( in-line view ) : FROM 절에서 사용된 서브쿼리 --> 성능 향상 쿼리
SELECT select_list
FROM ( 서브쿼리 ) alias
WHERE 조건식;SELECT e.department_id, 합계, 평균, 인원수
FROM ( SELECT department_id, SUM(salary) 합계, AVG(salary) 평균 , COUNT(*) 인원수 --->column 수가 11 x 27
FROM employees
GROUP BY department_id ) e, departments d
WHERE e.department_id = d.department_id
ORDER By 1;
이제.. 다시 위에서부터 보고 a4에 정리를 좀 해야겠습니다....
조인을... 내가 학부때도 배웠는데... 아직도 뭔가 좀 찝찝함...
특히나... 저 oracle join이랑 ansi join이... 굳이 내가 oracle join을 알아야할까?? 생각이 들면서도.. 해야될 것 같고...
근데 두 개를 하니까 뭔가 헷갈리고;;; 그럼...
'SK 행복성장캠퍼스 > SQL' 카테고리의 다른 글
| 2020-09-14, SQL_5일차 (0) | 2020.09.16 |
|---|---|
| 2020-09-11, SQL_4일차_연습문제 (0) | 2020.09.12 |
| 2020-09-10, SQL_3일차_연습문제 (0) | 2020.09.11 |
| 2020-09-10, SQL_3일차 (0) | 2020.09.11 |
| 2020-09-09, SQL_2일차_연습문제 (0) | 2020.09.10 |
댓글