[zero-base/] DS Part 5. SQL - 39일차 스터디 노트

손윤재·2024년 1월 26일

Database db mysql sql 스터디 노트 제로베이스 DS 22기

제로베이스 DS 22기

목록 보기

40/55

UNION : 수직결합

여러 개의 SQL문을 합쳐서 하나의 SQL문으로 만들어 주는 명령어이다.
주의! SQL문을 적용하는 컬럼의 개수가 같아야 한다. 같지 않으면 오류가 발생한다.

UNION : 중복된 값을 제거하고 검색 결과를 보여준다.
UNION ALL : 중복된 값을 포함해 모두 보여준다.

    SELECT column1, column2, ... FROM table_A;
    UNION | UNION ALL
    SELECT column1, column2, ... FROM table_B;

예제1.
성별이 여자인 데이터를 검색하는 쿼리와
소속사가 YG엔터테이먼트인 데이터를 검색하는 쿼리를 UNION으로 실행
예제2.
가수가 직업인 연예인의 이름, 직업을 검색하는 쿼리와,
1980년대에 태어난 연예인의 이름, 생년월일, 나이를 검색하는 쿼리를 UNION으로 실행

▶ 컬럼 개수가 맞지 않아 오류가 발생한다.
컬럼 개수를 맞춰 주고 다시 실행해 보면 결과값은 나오지만,
컬럼의 종류가 달라 컬럼 값에 맞지 않는 데이터가 같이 결과에 출력된다.

JOIN : 수평결합

두 개 이상의 테이블을 특정 컬럼을 기준으로 결합하는 명령어이다.

조인의 기본 종류

🔰 INNER JOIN

두 개의 Table에서 공통된 요소들을 통합해 결합하는 조인 방식이다. 일종의 교집합
- ON : 조인의 기준을 정해준다. 해당 컬럼의 값이 같을 때 조인한다.

    SELECT tableA.column1, tableA.column2, ..., tableB.column1, tableB.column2, ...
    	FROM tableA
        INNER JOIN tableB
    	ON tableA.column_name = tableB.column_name WHERE condition;

예제
snl_show에 호스트로 출연한 셀럽을 기준으로 celeb Table과 snl_show Table을 INNER JOIN

🔰 LEFT JOIN

두 개의 Table에서 공통영역을 포함하고 왼쪽 Table에만 있는 다른 데이터도 포함한 조인 방식

    SELECT tableA.column1, tableA.column2, ..., tableB.column1, tableB.column2, ...
    	FROM tableA --> left table에 해당
    	LEFT JOIN tableB --> right table에 해당
    	ON tableA.column_name = tableB.column_name WHERE condition;

예제
snl_show에 호스트로 출연한 셀럽을 기준으로 celeb Table과 snl_show Table을 LEFT JOIN

🔰 RIGHT JOIN

두 개의 Table에서 공통영역을 포함하고 오른쪽 Table에만 있는 다른 데이터도 포함한 조인 방식

    SELECT tableA.column1, tableA.column2, ..., tableB.column1, tableB.column2, ...
    	FROM tableA
    	RIGHT JOIN tableB
    	ON tableA.column_name = tableB.column_name WHERE condition;

예제
snl_show에 호스트로 출연한 셀럽을 기준으로 celeb Table과 snl_show Table을 RIGHT JOIN

🔰 FULL OUTER JOIN

두 개이 Table에서 공통 영역을 포함하고 양쪽 Table의 다른 영역도 모두 포함시키는 조인 방식

    SELECT tableA.column1, tableA.column2, ..., tableB.column1, tableB.column2, ...
    	FROM tableA FULL OUTER JOIN tableB
    	ON tableA.column_name = tableB.column_name WHERE condition;

FULL OUTER JOIN은 MySQL에서 지원하지 않는 쿼리이다.
그러므로 FULL OUTER JOIN과 같은 결과를 내는 다른 방식을 사용한다.
FULL OUTER JOIN = (LEFT JOIN) UNION (RIGHT JOIN)

    SELECT tableA.column1, tableA.column2, ..., tableB.column1, tableB.column2, ...
    	FROM tableA
    	LEFT JOIN tableB
    	ON tableA.column_name = tableB.column_name WHERE condition
    UNION --> 중복되는 부분은 삭제하고 결합해 준다.
    SELECT tableA.column1, tableA.column2, ..., tableB.column1, tableB.column2, ...
    	FROM tableA
    	RIGHT JOIN tableB
    	ON tableA.column_name = tableB.column_name WHERE condition;

예제
snl_show에 호스트로 출연한 셀럽을 기준으로 celeb Table과 snl_show Table을 FULL OUTER JOIN

🔰 SELF JOIN

문법은 다르지만 INNER JOIN과 같은 결과를 도출하는 조인 방식이다.
조인되는 Table 간에 공통된 데이터를 가져온다.

    SELECT tableA.column1, tableA.column2, ..., tableB.column1, tableB.column2, ...
    	FROM tableA, tableB, ...
    	WHERE condition; --> WHERE 절에 결합의 기준을 명시한다.

예제
celeb Table의 연예인 중 snl_show에 host로 출연했고
영화배우는 아니면서 YG엔터테이먼트 소속이거나 40세 이상이면서 YG엔터테이먼트 소속이 아닌 연예인의 이름과 나이, 직업, 소속사, 시즌, 에피소드 정보를 검색

SQL Subquery

하나의 SQL 문 안에 포함되어 있는 또 다른 SQL 문을 말한다.

메인쿼리가 서브쿼리를 포함하는 종속적인 관계이다.

⭕ 서브쿼리는 메인쿼리의 컬럼 사용이 가능하다.

❌ 메인쿼리는 서브쿼리의 컬럼 사용이 불가능하다.

Subquery 사용시 주의사항

💦 서브쿼리는 괄호로 묶어서 사용한다.

💦 단일 행 혹은 복수 행 비교 연산자와 함께 사용 가능하다.

💦 서브쿼리에서는 ORDER BY를 사용할 수 없다.

🔰 Scalar Subquery

스카라 서브쿼리는 SELECT 절에서 사용하는 서브쿼리이다.
스카라 서브쿼리의 결과는 하나의 Column이어야 한다.

    SELECT column1, (SELECT column2 FROM table2_name WHERE condition)
    		 FROM table1_name WHERE condition;

예제
서울은평경찰서의 강도 검거 건수와 서울시 경찰서 전체의 평균 강도 검거 건수를 조회

    SELECT case_number 은평강도검거건수,
    			 (SELECT AVG(case_number)
    			  FROM crime_status
    			  WHERE crime_type LIKE '강도'
                   	    AND status_type LIKE '검거') 서울강도검거평균
    FROM crime_status
    WHERE police_station='은평' AND crime_type='강도' AND status_type='검거';
    -- ⬇ 실행결과 ⬇ --
    +--------------------+--------------------+
    | 은평강도검거건수      | 서울강도검거평균     |
    +--------------------+--------------------+
    |                  1 |             4.1935 |
    +--------------------+--------------------+

🔰 Inline View

인라인 뷰는 FROM 절에서 사용하는 서브쿼리이다.
메인쿼리에서는 인라인 뷰에서 조회한 Column 만 사용 가능하다.

    SELECT a.column_name, b.column_name, ...
    FROM table1_name a, (SELECT column1, column2, ... FROM table2_name) b
    WHERE condition;

예제
경찰서 별로 가장 많이 발생한 범죄 건수와 범죄 유형을 조회

    SELECT c.police_station, c.crime_type, c.case_number
    FROM crime_status c, (SELECT police_station, MAX(case_number) count
    					  FROM crime_status
    					  WHERE status_type='발생'
                          GROUP BY police_station) m
    WHERE c.police_station = m.police_station AND c.case_number = m.count;
    -- ⬇ 실행결과 ⬇ --
    +----------------+------------+-------------+
    | police_station | crime_type | case_number |
    +----------------+------------+-------------+
    | 중부            | 폭력       |         997 |
    | 종로            | 폭력       |         964 |
    | 남대문          | 절도       |         699 |
    | 서대문          | 폭력       |        1292 |
    | 혜화            | 폭력       |         747 |
    | 용산            | 폭력       |        1617 |
    | 성북            | 폭력       |         672 |
    | 동대문          | 폭력       |        1784 |

🔰 Nested Subquery

중첩 서브쿼리는 WHERE 절에서 사용되는 서브쿼리이다.

❗ Single Row

하나의 행을 검색하는 서브쿼리
서브쿼리가 비교연산자(=, >, >=, <, <=, <>, !=)와 사용되는 경우이다.

    SELECT column_names,... FROM table_name
    WHERE column_name = (SELECT column_name FROM table_name WHERE condition)
    ORDER BY column_names;

예제

	SELECT name FROM celeb WHERE name = SELECT host FROM snl_show;

    - 괄호가 없으면 에러가 발생한다.

    SELECT name FROM celeb WHERE name = (SELECT host FROM snl_show);

    - 한 개 이상의 결과값을 가지는 경우 에러가 발생한다.

    SELECT name FROM celeb WHERE name = (SELECT host FROM snl_show WHERE id=1);
    -- ⬇ 실행결과 ⬇ --
      +-----------+
      | name      |
      +-----------+
      | 강동원     |
      +-----------+

	- 서브쿼리의 검색 결과는 한 개의 결과값이어야 한다.

❗ Multiple Row

하나 이상의 행을 검색하는 서브쿼리

IN : 서브쿼리 결과 중에 포함될 때

    SELECT column_names,... FROM table_name
    WHERE column_name IN (SELECT column_name FROM table_name WHERE condition)
    ORDER BY column_names;

예제 - SNL에 출연한 영화배우를 조회

    SELECT host FROM snl_show
    WHERE host IN (SELECT name FROM celeb WHERE job_title LIKE '%영화배우%');
    -- ⬇ 실행결과 ⬇ --
      +-----------+
      | host      |
      +-----------+
      | 강동원     |
      | 차승원     |
      +-----------+

EXISTS : 서브쿼리 결과에 값이 있으면 True를 반환

    SELECT column_names,... FROM table_name
    WHERE EXISTS (SELECT column_name FROM table_name WHERE condition)
    ORDER BY column_names;

예제 - 범죄 검거 혹은 발생 건수가 2000건 보다 큰 경찰서 조회

    SELECT name FROM police_station p
    WHERE EXISTS (SELECT police_station FROM crime_status c
                  WHERE p.name = c.reference AND case_number > 2000);
    -- ⬇ 실행결과 ⬇ --
    +------------------------+
    | name                   |
    +------------------------+
    | 서울강남경찰서           |
    | 서울강서경찰서           |
    | 서울관악경찰서           |
    | 서울구로경찰서           |
    | 서울노원경찰서           |
    | 서울송파경찰서           |
    | 서울영등포경찰서         |
    | 서울중랑경찰서           |
    +------------------------+

ANY : 서브쿼리 결과 중에 최소한 하나라도 만족하면 True를 반환

    SELECT column_names,... FROM table_name
    WHERE column_name = ANY (SELECT column_name FROM table_name WHERE condition)
    ORDER BY column_names;

예제 - SNL에 출연한 적이 있는 연예인 이름 조회

    SELECT name FROM celeb
    WHERE name = ANY (SELECT host FROM snl_show);
    -- ⬇ 실행결과 ⬇ --
      +-----------+
      | name      |
      +-----------+
      | 강동원     |
      | 유재석     |
      | 차승원     |
      | 이수현     |
      +-----------+

ALL : 서브쿼리 결과를 모두 만족하면 True를 리턴 (비교연산자를 사용)

    SELECT column_names,... FROM table_name
    WHERE column_name = ALL (SELECT column_name FROM table_name WHERE condition)
    ORDER BY column_names;

예제 - SNL에 출연한 적이 있는 연예인 이름 조회

    SELECT name FROM celeb
    WHERE name = ALL (SELECT host FROM snl_show WHERE id=1);
    -- ⬇ 실행결과 ⬇ --
      +-----------+
      | name      |
      +-----------+
      | 강동원     |
      +-----------+

❗ Multiple Column

하나 이상의 열을 검색하는 서브쿼리
서브쿼리 내에 메인쿼리 컬럼을 가져와 같이 사용되는 경우이다.

    SELECT column_names, ... FROM table1_name a
    WHERE (a.column1, a.column2, ...)
    		IN (SELECT b.column1, b.column2, ...
            	FROM table2_name b
            	WHERE a.column_name = b.column_name)
    ORDER BY column_names;

예제 - 강동원과 성별, 소속사가 같은 연예인의 이름, 성별, 소속사를 조회

    SELECT name, sex, agency FROM celeb
    WHERE (sex, agnecy) IN (SELECT sex, agency FROM celeb WHERE name='강동원');
    -- ⬇ 실행결과 ⬇ --
    +-----------+------+----------------------+
    | name      | sex  | agency               |
    +-----------+------+----------------------+
    | 강동원     | M    | YG엔터테이먼트         |
    | 차승원     | M    | YG엔터테이먼트         |
    +-----------+------+----------------------+