JUST DO, 데이터베이스! 🕺 - (4) Join

joyfulwave·2022년 8월 30일

Coding DBMS INNER JOIN JOIN oracle outter join programming sql 데이터베이스 예제 오라클 함수

JUST DO! 데이터베이스!

목록 보기

4/7

방대한 데이터의 세계로, JUST DO DBMS!

📁 JOIN

두 개 이상의 테이블을 서로 연결하여 데이터를 검색할 때 사용하는 방법으로 두 개의 테이블을 마치 하나의 테이블인 것처럼 보여주는 것을 뜻해요.

JOIN의 기본 사용 방법

두 개의 테이블에 하나라도 같은 컬럼이 있어야 해요.

두 컬럼의 값은 공유 되어야 해요.

보통 조인을 위해서 기본키(Primary Key)와 외래키(Foreign Key)를 활용해요.

📎 INNER JOIN

각 테이블에서 조인 조건에 일치되는 데이터만 가져와요.
INNER JOIN은 교집합이라고 할 수 있어요.

⚫ (1) INNER JOIN

    SELECT 	e.EMPLOYEE_ID 
        ,	e.FIRST_NAME 
        ,	e.DEPARTMENT_ID 
        ,	d.DEPARTMENT_ID 
        ,	d.DEPARTMENT_NAME 
    FROM 	EMPLOYEES e INNER JOIN DEPARTMENTS d  
        ON	e.DEPARTMENT_ID  = d.DEPARTMENT_ID 
        -- ON은 조건절로 e 의 칼럼과 d의 칼럼이 같을 때 라는 뜻이다.
    -- WHERE  e.EMPLOYEE_ID = 178
    -- WHERE e.DEPARTMENT_ID = 270
    -- DEPARTMENT_ID가 NULL값인 경우의 데이터는 나오지 않는다. 
    ORDER BY e.EMPLOYEE_ID 
    ;

    SELECT 	*
    FROM 	EMPLOYEES e 
    WHERE 	DEPARTMENT_ID IS NULL;

⚫ (2) ANSI INNER JOIN

사원의 FIRST_NAME, LAST_NAME, EMAIL, DEPARTMENT_ID, DEPARTMENT_NAME, JOB_ID, JOB_TITLE, CITY 컬럼을 출력

ANSI INNER JOIN 사용
4개 테이블의 조인
단 CITY = 'Seattle' 인 사람의 정보

    SELECT	e.FIRST_NAME
        ,	e.LAST_NAME 
        ,	e.EMAIL 
        ,	e.DEPARTMENT_ID 
        ,	d.DEPARTMENT_NAME 
        ,	j.JOB_ID 
        , 	j.JOB_TITLE 
        , 	l.CITY 
    FROM  	EMPLOYEES e 
        INNER JOIN DEPARTMENTS d	ON	e.DEPARTMENT_ID = d.DEPARTMENT_ID
        INNER JOIN JOBS j			ON	e.JOB_ID = j.JOB_ID				
        INNER JOIN LOCATIONS l		ON 	d.LOCATION_ID = l.LOCATION_ID 
    WHERE	l.CITY = 'Seattle'
    ORDER BY e.DEPARTMENT_ID 
    ;

📎 OUTER JOIN

조인 조건에 일치하는 데이터 및 일치하지 않는 데이터를 모두 select 해요.
조인 조건에 일치하는 데이터가 없다면 NULL로 가지고 와요.
Outer Join은 inner join과는 다르게 주(main)테이블이 어떤 테이블인지가 중요하다. 그래서 어떤 테이블이 중심이 되느냐에 따라 다시 LEFT, RIGHT, FULL로 세분화 할 수 있어요.

LEFT OUTER JOIN : 왼쪽 테이블이 중심
RIGHT OUTER JOIN : 오른족 테이블이 중심
FULL OUTER JOIN : 양쪽 테이블 모두가 중심

⚫ LEFT OUTER JOIN

	SELECT	*
	FROM	tableA left outer join tableB

왼쪽 테이블 tableA가 기준이 돼요.
조인 조건에 부합하는 데이터가 조인 당하는 테이블(오른쪽)에 있으면 해당 데이터를 가지고 오고, 부재하면 NULL 로 SELECT 하게 돼요.

⚫ RIGHT OUTER JOIN

	SELECT	*
	FROM	tableA right outer join tableB

오른쪽 테이블 tableB가 기준이 돼요.
조인 조건에 부합하는 데이터가 조인 당하는 테이블(왼쪽)에 있으면 해당 데이터를 가지고 오고, 부재하면 NULL 로 SELECT 돼요.

⚫ FULL OUTER JOIN

	SELECT	*
	FROM	tableA full outer join tableB

양쪽 테이블 모두가 기준이 돼요.
조인 조건에 부합하는 데이터가 조인 당하는 테이블에 있으면 해당 데이터를, 부재하면 NULL 로 SELECT 돼요.

    -- left outer join
    SELECT 	e.EMPLOYEE_ID 
        ,	e.FIRST_NAME 
        ,	e.HIRE_DATE 
        ,	jh.EMPLOYEE_ID 
        ,	jh.START_DATE 
        ,	jh.END_DATE 
        ,	jh.JOB_ID 
    FROM 	EMPLOYEES e 
        -- 모든 직원들 중에서 직군을 이동한 잡히스토리에 대한 내용을 알고 싶을 때
        LEFT OUTER JOIN JOB_HISTORY jh ON e.EMPLOYEE_ID = jh.EMPLOYEE_ID 
        -- 직군을 이동한 사람들 중에서 추가적인 내용을 알고 싶을때 
        -- RIGHT OUTER JOIN JOB_HISTORY jh ON e.EMPLOYEE_ID = jh.EMPLOYEE_ID 
        -- inner join은 부서이동이 있던 사람에 대한 데이터만
        -- INNER JOIN JOB_HISTORY jh ON e.EMPLOYEE_ID = jh.EMPLOYEE_ID 
    ORDER BY 	e.EMPLOYEE_ID 
    ;

    -- FULL OUTER JOIN
    SELECT 	e.EMPLOYEE_ID 
        ,	e.DEPARTMENT_ID 
        ,	d.DEPARTMENT_ID 
        ,	d.DEPARTMENT_NAME 
    FROM 	EMPLOYEES e 
        -- 양쪽테이블의 모든 테이블을 볼 수 있다.
        FULL OUTER JOIN DEPARTMENTS d ON e.DEPARTMENT_ID  = d.DEPARTMENT_ID 
    ;

📎 ANSI JOIN vs ORACLE JOIN

SQL은 데이터베이스를 관린하기 위해 만들어진 프로그래밍 언어에요. 데이터베이스를 관리해주는 대부분의 DBMS들은 SQL을 사용해요. 물론, DBMS자체의 특수성 때문에 SQL의 사용법이 조금씩 다르기도 하지만, 큰 틀에선 나름대로의 보편성을 가지고 있어요. 이를 위해 미국 국립 표준 협회(ANSI = American National Standards Institute)에서도 SQL에 대한 보편적인 문법을 제시하고 있는데, 그것이 바로 ANSI Query에요.

📁 서브쿼리(SubQuery)란?

MainQuery에 반대되는 개념으로 이름을 붙인 것이에요.
메인 쿼리를 구성하는 소단위 쿼리에요.
SELECT, INSERT, DELETE, UPDATE 절에서 모두 사용 가능하며, 서브쿼리의 결과 집합을 메인 쿼리가 중간 결과값으로 사용이 가능해요. 서브쿼리 자체는 일반 쿼리와는 다를 바가 없어요.

⚫ 서브쿼리의 모습

    -- 평균 급여보다 적게 받는 사람
    -- 평균 급여 : 6,462
    SELECT 	ROUND(AVG(SALARY)) 
    FROM 	EMPLOYEES e 
    ;

    -- WHERE 절에서는 집계함수를 사용할 수 없다.
    SELECT 	EMPLOYEE_ID 
        ,	FIRST_NAME 
        ,	LAST_NAME 
    FROM 	EMPLOYEES e 
    WHERE 	SALARY < 6462 -- 평균 급여는 계속 바뀔 수 있다.
            -- SALARY < ROUND(AVG(SALARY)) 
    ORDER BY EMPLOYEE_ID 
    ;

    -- SUBQUERY
    SELECT 	EMPLOYEE_ID 
        ,	FIRST_NAME 
        ,	LAST_NAME 
    FROM 	EMPLOYEES e 
    WHERE 	SALARY < ( SELECT ROUND(AVG(SALARY)) 
                       FROM EMPLOYEES e )
    ORDER BY EMPLOYEE_ID 
    ;

📁 예제

⚫ (1)

사원의 이름, 부서번호, 부서명 출력

    SELECT	e.FIRST_NAME || ' ' || e.LAST_NAME name
        ,	e.DEPARTMENT_ID 
        ,	d.DEPARTMENT_NAME 
    FROM 	EMPLOYEES e 
        LEFT OUTER JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID 
    ;

⚫ (2)

30번 부서의 사원들의 이름, 직업, 부서명을 출력

    SELECT	e.FIRST_NAME || ' ' || e.LAST_NAME name
        ,	e.JOB_ID 
        ,	d.DEPARTMENT_NAME 
    FROM 	EMPLOYEES e 
        LEFT OUTER JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID 
    WHERE 	e.DEPARTMENT_ID = 30
    ;

⚫ (3)

커미션을 받는 사원의 이름, 직업, 부서번호, 부서명을 출력

    SELECT	e.FIRST_NAME || ' ' || e.LAST_NAME name
        ,	e.JOB_ID 
        ,	e.DEPARTMENT_ID 
    --	,	COMMISSION_PCT 
        ,	d.DEPARTMENT_NAME 
    FROM 	EMPLOYEES e 
        LEFT OUTER JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID 
    WHERE 	e.COMMISSION_PCT IS NOT NULL
    ;

⚫ (4)

지역번호(location_id) 2500번에서 근무하는 사원의 이름, 직업, 부서번호, 부서명을 출력

    SELECT 	e.FIRST_NAME || ' ' || e.LAST_NAME name
        ,	e.JOB_ID 
        ,	d.DEPARTMENT_ID 
        , 	d.DEPARTMENT_NAME 
    FROM 	EMPLOYEES e 
        LEFT OUTER JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID 
        LEFT OUTER JOIN LOCATIONS l ON 	d.LOCATION_ID = l.LOCATION_ID 
    WHERE 	l.LOCATION_ID = 2500
    ;

⚫ (5)

이름에 A가 들어가는 사원들의 이름과 부서이름을 출력

    SELECT	e.FIRST_NAME || ' ' || e.LAST_NAME name
        ,	d.DEPARTMENT_NAME 
    FROM 	EMPLOYEES e 
        LEFT OUTER JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID 
    WHERE 	e.FIRST_NAME LIKE '%A%' OR e.LAST_NAME LIKE '%A%'
    ;

⚫ (6)

사원 이름과 그 사원의 관리자 이름(manager)을 출력

    SELECT	e.FIRST_NAME || ' ' || e.LAST_NAME name -- 사원 이름
        ,	e2.First_name || ' ' || e2.LAST_NAME manager_name -- 매니저이름
    FROM 	EMPLOYEES e 
        INNER JOIN EMPLOYEES e2 ON e.MANAGER_ID = e2.EMPLOYEE_ID 
    ORDER BY e2.EMPLOYEE_ID 
    ;

⚫ (7)

사원 이름과 부서명과 월급을 출력하는데, 월급이 3000이상인 사원을 출력

    SELECT	e.FIRST_NAME || ' ' || e.LAST_NAME name
        , 	d.DEPARTMENT_NAME 
        ,	e.SALARY 
    FROM 	EMPLOYEES e 
        LEFT OUTER JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID 
    WHERE 	e.SALARY >= 3000
    ORDER BY e.SALARY 
    ;

⚫ (8)

급여가 3000에서 5000사이인 사원이 이름과 소속 부서명 출력

    SELECT	e.FIRST_NAME || ' ' || e.LAST_NAME name
        , 	d.DEPARTMENT_NAME 
    --	,	SALARY 
    FROM 	EMPLOYEES e 
        LEFT OUTER JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID 
    WHERE 	e.SALARY BETWEEN 3000 AND 5000
    ;

⚫ (9)

급여가 3000이하인 사원의 이름과 급여, 근무지 출력

    SELECT	e.FIRST_NAME || ' ' || e.LAST_NAME name
        ,	e.SALARY 
    --	,	l.LOCATION_ID 
        ,	l.CITY 
    FROM 	EMPLOYEES e 
        LEFT OUTER JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID 
        LEFT OUTER JOIN LOCATIONS l ON d.LOCATION_ID = l.LOCATION_ID 
    WHERE 	e.SALARY <= 3000
    ORDER BY l.CITY 
    ;

⚫ (10)

'Steven King'의 부서명을 출력

    SELECT	--e.FIRST_NAME || ' ' || e.LAST_NAME name ,
            d.DEPARTMENT_NAME 
    FROM 	EMPLOYEES e 
        LEFT OUTER JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID 
    WHERE 	e.FIRST_NAME = 'Steven' AND e.LAST_NAME = 'King'
    ;

⚫ (11)

IT 부서에서 근무하고 있는 사람을 출력

    SELECT	e.EMPLOYEE_ID 
        ,	e.FIRST_NAME || ' ' || e.LAST_NAME name 
        , 	d.DEPARTMENT_NAME 
    FROM 	EMPLOYEES e 
        LEFT OUTER JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID 
    WHERE 	d.DEPARTMENT_NAME = 'IT'
    ;

⚫ (12)

employees 테이블에서 사원번호, 이름, 업무, 부서번호, 부서명, 근무지 아이디 출력

    SELECT	e.EMPLOYEE_ID 
        ,	e.FIRST_NAME || ' ' || e.LAST_NAME name 
        ,	e.JOB_ID 
        ,	e.DEPARTMENT_ID 
        , 	d.DEPARTMENT_NAME 
        , 	l.LOCATION_ID 
    FROM 	EMPLOYEES e 
        LEFT OUTER JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID 
        LEFT OUTER JOIN LOCATIONS l ON d.LOCATION_ID = l.LOCATION_ID 
    ;

⚫ (13)

'SA_MAN'직군의 사원번호, 이름, 급여, 부서명, 근무지 아이디 출력

    SELECT	e.EMPLOYEE_ID 
        ,	e.FIRST_NAME || ' ' || e.LAST_NAME name 
        ,	e.SALARY 
        , 	d.DEPARTMENT_NAME 
        , 	l.LOCATION_ID 
        ,	e.JOB_ID 
    FROM 	EMPLOYEES e 
        LEFT OUTER JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID 
        LEFT OUTER JOIN LOCATIONS l ON d.LOCATION_ID = l.LOCATION_ID 
    WHERE 	e.JOB_ID = 'SA_MAN'
    ;

⚫ (14)

COUNTRY_ID = 'US' 인 LOCATION_ID => 1400, 1500, 1600, 1700 부서테이블에 정보 전체를 조회

    SELECT	LOCATION_ID 
    FROM 	LOCATIONS l 
    WHERE 	COUNTRY_ID = 'US'
    ;

    SELECT 	*
    FROM 	DEPARTMENTS d 
    WHERE 	LOCATION_ID IN (1400, 1500, 1600, 1700)
    ;

    SELECT 	*
    FROM 	DEPARTMENTS d 
    WHERE 	LOCATION_ID IN (SELECT	LOCATION_ID 
                            FROM 	LOCATIONS l 
                            WHERE 	COUNTRY_ID = 'US')
    ;

⚫ (15)

월급이 가장 적은 사원의 이름을 FIRST_NAME, LAST_NAME을 연결하여 조회

    SELECT 	MIN(SALARY)
    FROM 	EMPLOYEES e 
    ;

    SELECT	FIRST_NAME || ' ' || LAST_NAME name
        ,	SALARY 
    FROM 	EMPLOYEES e 
    WHERE 	SALARY = (SELECT 	MIN(SALARY)
                      FROM		EMPLOYEES e2 ) -- 서브쿼리의 별칭은 가능한 메인 쿼리와 충돌하지 않게 하는 것이 좋다.
    ;

⚫ (16)

월급이 가장 많은 사원

    SELECT 	e.FIRST_NAME || ' ' || LAST_NAME name
        ,	e.SALARY 
        ,	j.JOB_TITLE 
    FROM 	EMPLOYEES e 
        LEFT OUTER JOIN JOBS j ON e.JOB_ID = j.JOB_ID 
    WHERE 	e.SALARY = (SELECT MAX(SALARY) FROM EMPLOYEES e2)
    ;

⚫ (17)

LAST_NAME이 'Kochhar'인 사람의 급여보다 많은 사원의 사원번호, 이름, 담당업무(JOB_ID), JOB_TITLE, 급여를 조회

    SELECT 	e.EMPLOYEE_ID 
        , 	e.FIRST_NAME || ' ' || e.LAST_NAME 
        , 	e.JOB_ID 
        , 	j.JOB_TITLE 
    --	,	e.SALARY 
    FROM 	EMPLOYEES e 
        LEFT OUTER JOIN JOBS j ON e.JOB_ID = j.JOB_ID 
    WHERE 	e.SALARY > (SELECT	SALARY  
                        FROM 	EMPLOYEES e2 
                        WHERE 	LAST_NAME = 'Kochhar' )
    ;

⚫ (18)

급여의 평균보다 적은 사원의 사원번호, 이름, 담당업무, 급여, 부서번호, 부서명 출력

    SELECT 	e.EMPLOYEE_ID 
        ,	e.FIRST_NAME || ' ' || e.LAST_NAME 
        ,	e.JOB_ID 
        ,	e.SALARY 
        , 	e.DEPARTMENT_ID 
        ,	d.DEPARTMENT_NAME 
    FROM 	EMPLOYEES e 
        LEFT OUTER JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID 
    WHERE 	SALARY < (SELECT	AVG(SALARY) 
                      FROM 		EMPLOYEES e2)
    ;

⚫ (19)

100번 부서의 최소 급여보다 최소 급여가 많은 다른 모든 부서를 출력

    SELECT 	MIN(SALARY)
    FROM 	EMPLOYEES e2
    WHERE 	DEPARTMENT_ID = 100 -- 6900
    ;

    SELECT 	DEPARTMENT_ID 
        ,	MIN(SALARY) 
    FROM 	EMPLOYEES e 
    GROUP BY DEPARTMENT_ID 
    HAVING MIN(SALARY) > (	SELECT 	MIN(SALARY)
                            FROM 	EMPLOYEES e2
                            WHERE 	DEPARTMENT_ID = 100) 
    ;

⚫ (20)

업무가 'SA_MAN'인 사원의 이름, 업무(JOB_ID), 부서명, 근무지(CITY)를 출력

    SELECT 	FIRST_NAME 
        , 	JOB_ID 
        ,	DEPARTMENT_ID 
    FROM 	EMPLOYEES e 
    WHERE 	JOB_ID = 'SA_MAN'
    ;

    SELECT 	emp.FIRST_NAME
        , 	emp.JOB_ID
        , 	d.DEPARTMENT_NAME 
        , 	l.CITY 
    FROM 	(	SELECT 	FIRST_NAME  -- 서브쿼리 자체를 테이블로 사용할 수 있다.
                    , 	JOB_ID 
                    ,	DEPARTMENT_ID 
                FROM 	EMPLOYEES e 
                WHERE 	JOB_ID = 'SA_MAN') emp
        INNER JOIN DEPARTMENTS d 	ON emp.DEPARTMENT_ID = d.DEPARTMENT_ID 
        INNER JOIN LOCATIONS l 		ON d.LOCATION_ID = l.LOCATION_ID 
    ;

⚫ (21)

가장 많은 사원을 갖는 MANAGER의 사원번호, 관리하는 사원수를 출력

    SELECT 	MAX(COUNT(*)) 
    FROM	EMPLOYEES e 
    GROUP BY MANAGER_ID 

    SELECT 	MANAGER_ID
        ,	COUNT(*) 
    FROM 	EMPLOYEES e 
    GROUP BY MANAGER_ID 
    HAVING 	COUNT(MANAGER_ID) = (	SELECT 	MAX(COUNT(*)) 
                                    FROM	EMPLOYEES e 
                                    GROUP BY MANAGER_ID ) 
    ;

⚫ (22)

가장 많은 사원이 속해있는 부서 번호와 사원수를 출력

    SELECT 	MAX(COUNT(*)) 
    FROM 	EMPLOYEES e
    GROUP BY  DEPARTMENT_ID 
    ; 

    SELECT 	DEPARTMENT_ID
        ,	COUNT(EMPLOYEE_ID) 
    FROM 	EMPLOYEES e 
    GROUP BY DEPARTMENT_ID 
    HAVING	COUNT(DEPARTMENT_ID) = (	SELECT 	MAX(COUNT(*)) 
                                        FROM 	EMPLOYEES e
                                        GROUP BY  DEPARTMENT_ID ) 
    ;

포기하지 말고 JUST DO! ✔️

출처
https://media.giphy.com/media/1hVi7JFFzplHW/giphy.gif
https://media.giphy.com/media/jUwpNzg9IcyrK/giphy.gif

joyfulwave

이전 포스트

JUST DO, 데이터베이스! 🕺 - (3) dual, 다양한 함수

다음 포스트

JUST DO, 데이터베이스! 🕺 - (5) 데이터타입, DDL, DML

0개의 댓글