SQL(JOIN, GROUP BY, HAVING)

gwanhun·2023년 10월 1일
sql
0

https://k-glory.tistory.com/56 참고

https://velog.io/@taeha7b/mysql-in-a-nutshell#다중-테이블-연산

https://paris-in-the-rain.tistory.com/100

https://velog.io/@ygh7687/SQL-문법-정리

https://zetawiki.com/wiki/SQL왼쪽조인

https://wkdtjsgur100.github.io/groupby-having/

1. 그룹 조회

GROUP BY

특정 칼럼 값을 기준으로 테이블의 전체 행을 그룹별로 나누기 위한 절

MySQL에서의 GROUP BY는 특정 컬럼 이름을 지정(column-names)해주면 그 컬럼의 UNIQUE한 값에 따라서 데이터를 그룹 짓고, 중복된 열은 제거됩니다.GROUP BY는 보통 집합 함수(aggregate function, [AVG, SUM, COUNT 등을 말합니다])와 같이 쓰이며, 다음과 같은 형태를 지닙니다.

SELECT
    c1, c2,..., cn, aggregate_function(ci)
FROM
    table
WHERE
    where_conditions
GROUP BY c1 , c2,...,cn;
select {컬럼이름 [as 별칭], 그룹함수(컬럼명)} from 테이블이름 [where 검색조건][group by 컬럼이름]
[order by 컬럼이름[정렬옵션] ]
  • 그룹핑 전에 WHERE 절을 사용하여 그룹 대상을 먼저 선택 가능.
  • GROUP BY절에는 반드시 칼럼 이름이 포함되어야 하며 별명 사용 불가.
  • SELECT 절에서 집계 함수 없이 나열된 칼럼 이름이나 표현식은 GROUP BY 절에 반드시 포함되어야 함.
  • GROUP BY 절에서 나열된 칼럼 이름은 SELECT 절에 명시하지 않아도 됨.
  • SELECT 절에서 그룹 함수를 사용할 경우 group by 절에서 나눈 그룹 안에서 집계를 수행.

예를 들어, 다음과 같은 order 테이블이 있다고 합시다.

!https://wkdtjsgur100.github.io/images/posts/order_table.png

아래의 SQL 구문을 실행한 결과(status 별로 그룹지어서 각각의 갯수를 센 결과로 테이블을 만듭니다),

SELECT
    status, COUNT(*)
FROM
    orders
GROUP BY status;

//COUNT(*)
//: 테이블의 모든 ROW의 수를 센다.

결과는 다음과 같습니다.

!https://wkdtjsgur100.github.io/images/posts/groupby_order_table.png

HAVING

HAVING은 간단하게 생각해서 GROUP BY한 결과에 조건을 붙이고 싶을때, 즉 GROUP BY의 WHERE 절과도 같다고 볼 수 있습니다.

위의 예제에서, 각각 status의 총 갯수(COUNT)가 4이상인 경우를 예제로 보여드리겠습니다.

SELECT
    status, COUNT(*)
FROM
    orders
GROUP BY status
HAVING COUNT(*) > 4;

결과는 다음과 같습니다.

!https://wkdtjsgur100.github.io/images/posts/order_having.png

2. 다중 테이블 연산

Join 기본 문법

**SELECT**
테이블이름.조회할 테이블,
테이블이름.조회할 테이블
**FROM** 기준테이블 이름
**(INNER, LEFT, RIGHT FULL) JOIN** 조인테이블 이름
**ON** 기준테이블이름.기준키 = 조인테이블이름.기준키;
예시)

SELECT users.id, users.name, users.age, users.gender, accounts.account_type
FROM users JOIN accounts ON accounts.id = users.account_id;

한 눈에 보는 전체 사진

!https://user-images.githubusercontent.com/32683894/91840106-1d099d00-ec8b-11ea-87d0-248be6b55a46.png

1. LEFT JOIN (= LEFT OUTER JOIN)

!https://user-images.githubusercontent.com/32683894/91832062-da8e9300-ec7f-11ea-9c24-b4176ecbfc87.png

Left-outer-join-operation.png

LEFT JOIN은 A와 B 테이블 중에 A값 + A와 B의 KEY값이 같은 결과 를 리턴한다.

SELECT * FROM TableA A LEFT JOIN TableB B ON A.key = B.key;
**SELECT** * **FROM** employee
+------------+--------------+
| LastName   | DepartmentID |
+------------+--------------+
| Rafferty   |           31 |
| Jones      |           33 |
| Heisenberg |           33 |
| Robinson   |           34 |
| Smith      |           34 |
| Williams   |         NULL |
+------------+--------------+
**SELECT** * **FROM** department
+--------------+----------------+
| DepartmentID | DepartmentName |
+--------------+----------------+
|           31 | Sales          |
|           33 | Engineering    |
|           34 | Clerical       |
|           35 | Marketing      |
+--------------+----------------+
**SELECT** * **FROM** employee **LEFT** **JOIN** department
**ON** employee.DepartmentID = department.DepartmentID
+------------+--------------+--------------+----------------+
| LastName   | DepartmentID | DepartmentID | DepartmentName |
+------------+--------------+--------------+----------------+
| Rafferty   |           31 |           31 | Sales          |
| Jones      |           33 |           33 | Engineering    |
| Heisenberg |           33 |           33 | Engineering    |
| Robinson   |           34 |           34 | Clerical       |
| Smith      |           34 |           34 | Clerical       |
| Williams   |         NULL |         NULL | NULL           |
+------------+--------------+--------------+----------------+
**SELECT** A.LastName, B.DepartmentID, B.DepartmentName 
**FROM** employee A **LEFT** **JOIN** department B
**ON** A.DepartmentID = B.DepartmentID
+------------+--------------+----------------+
| LastName   | DepartmentID | DepartmentName |
+------------+--------------+----------------+
| Rafferty   |           31 | Sales          |
| Jones      |           33 | Engineering    |
| Heisenberg |           33 | Engineering    |
| Robinson   |           34 | Clerical       |
| Smith      |           34 | Clerical       |
| Williams   |         NULL | NULL           |
+------------+--------------+----------------+

2. RIGHT JOIN (= RIGHT OUTER JOIN)

RIGHT JOIN은 A와 B 테이블 중에 B 값 + A와 B의 KEY값이 같은 결과 를 리턴한다.

!https://user-images.githubusercontent.com/32683894/91833819-26dad280-ec82-11ea-9256-18181ddc96a0.png

Right-outer-join-operation.png

SELECT * FROM TableA A RIGHT JOIN TableB B ON A.key = B.key;
**SELECT** * **FROM** employee
+------------+--------------+
| LastName   | DepartmentID |
+------------+--------------+
| Rafferty   |           31 |
| Jones      |           33 |
| Heisenberg |           33 |
| Robinson   |           34 |
| Smith      |           34 |
| Williams   |         NULL |
+------------+--------------+
**SELECT** * **FROM** department
+--------------+----------------+
| DepartmentID | DepartmentName |
+--------------+----------------+
|           31 | Sales          |
|           33 | Engineering    |
|           34 | Clerical       |
|           35 | Marketing      |
+--------------+----------------+
**SELECT** * **FROM** employee **RIGHT** **JOIN** department
**ON** employee.DepartmentID = department.DepartmentID
+------------+--------------+--------------+----------------+
| LastName   | DepartmentID | DepartmentID | DepartmentName |
+------------+--------------+--------------+----------------+
| Rafferty   |           31 |           31 | Sales          |
| Heisenberg |           33 |           33 | Engineering    |
| Jones      |           33 |           33 | Engineering    |
| Smith      |           34 |           34 | Clerical       |
| Robinson   |           34 |           34 | Clerical       |
| NULL       |         NULL |           35 | Marketing      |
+------------+--------------+--------------+----------------+
**SELECT** A.LastName, B.DepartmentID, B.DepartmentName 
**FROM** employee A **RIGHT** **JOIN** department B
**ON** A.DepartmentID = B.DepartmentID
+------------+--------------+----------------+
| LastName   | DepartmentID | DepartmentName |
+------------+--------------+----------------+
| Rafferty   |           31 | Sales          |
| Heisenberg |           33 | Engineering    |
| Jones      |           33 | Engineering    |
| Smith      |           34 | Clerical       |
| Robinson   |           34 | Clerical       |
| NULL       |           35 | Marketing      |
+------------+--------------+----------------+

3. LEFT JOIN (순수 A만 구할 때)

순수 A만 구할 때, 순수 A들은 JOIN 후 B가 가진 테이블 COLUMN들의 항목이 NULL로 포함되어

있다.

그래서 B의 KEY 값이 IS NULL인지 확인한다.

!https://user-images.githubusercontent.com/32683894/91834764-6eae2980-ec83-11ea-8745-cb69161157d5.png

Left-anti-join-operation.png

SELECT * FROM TableA A LEFT JOIN TableB B ON A.key = B.key
WHERE B.Key IS NULL;
**CREATE** **TABLE** Sales (Date date, CountryID int(11), Units int(11));
**INSERT** **INTO** Sales (Date, CountryID, Units) **VALUES**('2020-01-01', 1, 40),
	('2020-01-02', 1, 25),
	('2020-01-03', 3, 30),
	('2020-01-04', 2, 35);

**CREATE** **TABLE** Countries (ID int(11), Country varchar(16));
**INSERT** **INTO** Countries (ID, Country) **VALUES**(3, 'Panama'),
	(4, 'Spain');

**SELECT** * **FROM** Sales
**WHERE** CountryID **NOT** **IN** ( **SELECT** ID **FROM** Countries );
+------------+-----------+-------+
| Date       | CountryID | Units |
+------------+-----------+-------+
| 2020-01-01 |         1 |    40 |
| 2020-01-02 |         1 |    25 |
| 2020-01-04 |         2 |    35 |
+------------+-----------+-------+

4. RIGHT JOIN (순수 B만 구할 때)

순수 B만 구할 때, 순수 B들은 JOIN 후 A가 가진 테이블 COLUMN들의 항목이 NULL로 포함되어 있다.

그래서 A의 KEY 값이 IS NULL인지 확인한다.

!https://user-images.githubusercontent.com/32683894/91834237-b97b7180-ec82-11ea-816d-54e44706ae49.png

Right-anti-join-operation.png

SELECT * FROM TableA A RIGHT JOIN TableB B ON A.key = B.key
WHERE A.Key IS NULL;
**CREATE** **TABLE** Sales (Date date, CountryID int(11), Units int(11));
**INSERT** **INTO** Sales (Date, CountryID, Units) **VALUES**('2020-01-01', 1, 40),
	('2020-01-02', 1, 25),
	('2020-01-03', 3, 30),
	('2020-01-04', 2, 35);

**CREATE** **TABLE** Countries (ID int(11), Country varchar(16));
**INSERT** **INTO** Countries (ID, Country) **VALUES**(3, 'Panama'),
	(4, 'Spain');

**SELECT** * **FROM** Countries
**WHERE** ID **NOT** **IN** ( **SELECT** CountryID **FROM** Sales )
+------+---------+
| ID   | Country |
+------+---------+
|    4 | Spain   |
+------+---------+

5. INNER JOIN (A와 B의 교집합)

A와 B의 교집합 ( 서로 중복되는 값)을 보여준다.

!https://user-images.githubusercontent.com/32683894/91835533-7de1a700-ec84-11ea-9435-2d0c66b2ed5d.png

Inner-join-operation.png

SELECT * FROM TableA A INNER JOIN TableB B ON A.key = B.key;
**SELECT** * **FROM** employee
+------------+--------------+
| LastName   | DepartmentID |
+------------+--------------+
| Rafferty   |           31 |
| Jones      |           33 |
| Heisenberg |           33 |
| Robinson   |           34 |
| Smith      |           34 |
| Williams   |         NULL |
+------------+--------------+
**SELECT** * **FROM** department
+--------------+----------------+
| DepartmentID | DepartmentName |
+--------------+----------------+
|           31 | Sales          |
|           33 | Engineering    |
|           34 | Clerical       |
|           35 | Marketing      |
+--------------+----------------+
**SELECT** * **FROM** employee **JOIN** department
**ON** employee.DepartmentID = department.DepartmentID
+------------+--------------+--------------+----------------+
| LastName   | DepartmentID | DepartmentID | DepartmentName |
+------------+--------------+--------------+----------------+
| Rafferty   |           31 |           31 | Sales          |
| Jones      |           33 |           33 | Engineering    |
| Heisenberg |           33 |           33 | Engineering    |
| Robinson   |           34 |           34 | Clerical       |
| Smith      |           34 |           34 | Clerical       |
+------------+--------------+--------------+----------------+
**SELECT** A.LastName, B.*
**FROM** employee A **JOIN** department B
**ON** A.DepartmentID = B.DepartmentID
+------------+--------------+----------------+
| LastName   | DepartmentID | DepartmentName |
+------------+--------------+----------------+
| Rafferty   |           31 | Sales          |
| Jones      |           33 | Engineering    |
| Heisenberg |           33 | Engineering    |
| Robinson   |           34 | Clerical       |
| Smith      |           34 | Clerical       |
+------------+--------------+----------------+

6. FULL OUTER JOIN

!https://user-images.githubusercontent.com/32683894/91835803-d87b0300-ec84-11ea-984e-7abea0d1568c.png

Full-outer-join-operation.png

SELECT * FROM TableA A FULL OUTER JOIN TableB B ON A.key = B.key;
  • MySQL은 FULL OUTER JOIN을 지원하지 않는다.
  • 그래서 LEFT JOIN과 RIGHT JOIN을 UNION해서 사용해야한다.
SELECT * FROM TableA A LEFT JOIN TableB B 
UNION
SELECT * FROM TableA A RIGHT JOIN TableB B

Union

  • select의 칼럼 리스트를 기준으로 두 개 이상의 질의 결과를 하나의 테이블로 합치고자 할 때 사용
  • 기본적으로 중복값을 제거한다.
  • 중복값을 포함하고 싶은 경우 union all을 사용한다.
select *
from customers
union
select city from orders
order by city;
**SELECT** * **FROM** employee
+------------+--------------+
| LastName   | DepartmentID |
+------------+--------------+
| Rafferty   |           31 |
| Jones      |           33 |
| Heisenberg |           33 |
| Robinson   |           34 |
| Smith      |           34 |
| Williams   |         NULL |
+------------+--------------+
**SELECT** * **FROM** department
+--------------+----------------+
| DepartmentID | DepartmentName |
+--------------+----------------+
|           31 | Sales          |
|           33 | Engineering    |
|           34 | Clerical       |
|           35 | Marketing      |
+--------------+----------------+

MySQL에서는 FULL OUTER JOIN 기능이 지원되지 않는다.

**SELECT** *
**FROM** employee **FULL** **OUTER** **JOIN** department
**ON** employee.DepartmentID = department.DepartmentID
💡 ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OUTER JOIN department ON employee.DepartmentID = department.DepartmentID' at line 2 Command exited with non-zero status 1

대신, 왼쪽 조인과 오른쪽 조인을 UNION하여 구할 수 있다.

**SELECT** * **FROM** employee A **LEFT** **JOIN** department B **ON** A.DepartmentID = B.DepartmentID
**UNION 
SELECT** * **FROM** employee A **RIGHT** **JOIN** department B **ON** A.DepartmentID = B.DepartmentID
+------------+--------------+--------------+----------------+
| LastName   | DepartmentID | DepartmentID | DepartmentName |
+------------+--------------+--------------+----------------+
| Rafferty   |           31 |           31 | Sales          |
| Jones      |           33 |           33 | Engineering    |
| Heisenberg |           33 |           33 | Engineering    |
| Robinson   |           34 |           34 | Clerical       |
| Smith      |           34 |           34 | Clerical       |
| Williams   |         NULL |         NULL | NULL           |
| NULL       |         NULL |           35 | Marketing      |
+------------+--------------+--------------+----------------+

7. FULL OUTER JOIN (교집합 제외)

!https://user-images.githubusercontent.com/32683894/91838564-ae2b4480-ec88-11ea-93e9-7fc17b8962ca.png

SELECT * FROM TableA A LEFT JOIN TableB B 
UNION
SELECT * FROM TableA A RIGHT JOIN TableB B
WHERE A.key IS NULL OR B.key IS NULL
profile
gwanhun
주니어 백앤드 개발자
이전 포스트

NestJS에서 csv변환 후 Cloud Storage에 저장하기

다음 포스트

SQL(JOIN, GROUP BY, HAVING)

0개의 댓글