[MySQL] 그룹 조회 / 조인(JOIN)

---

 

#1. 그룹 조회

1. GROUP BY 절

1) GROUP BY

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

SELECT 칼럼이름, [그룹함수(칼럼이름)] FROM 테이블이름
[WHERE 검색조건]
[GROUP BY 컬럼이름]
[ORDER BY 컬럼이름 [정렬옵션] ]

• 그룹핑 전에 WHERE 절을 사용하여 그룹 대상을 먼저 선택가능하다.
• GROUP BY 절에는 반드시 칼럼이름이 포함되어야 하며 별칭 사용 불가능.
• SELECT 절에서 집계 함수 없이 나열된 칼럼 이름이나 표현식은 GROUP BY 절에 반드시 포함 되어야 한다.
• GROUP BY 절에 나열된 칼럼 이름은 SELECT 절에 명시하지 않아도 된다.
• SELECT 절에서 그룹함수를 사용할 경우 GROUP BY 절에서 나눈 그룹 안에 집계를 수행해야한다.

 

2) 그룹조회와 집계 함수의 관계

• GROUP BY 절을 사용하여 컬럼을 그룹화 할 경우, 다른 컬럼에 대한 처리 기준이 정해지지 않았으므로 에러가 발생한다.

• GROUP BY 절에 명시되지 않은 컬럼을 SELECT에서 사용할 경우 집계 함수를 사용하여 각 그룹별 통계 데이터를 얻을 수 있다.

 

3) 다중 컬럼을 이용한 그룹별 검색

• GROUP BY 절에서 두 개 이상의 컬럼을 콤마로 구분하여 명시할 경우, 첫 번째 컬럼에 대한 그룹을 형성하고, 각 그룹 안에서 두 번째 컬럼에 대한 2차 그룹을 형성한다.

SELECT 컬럼이름, [그룹함수(컬럼이름)]
FROM 테이블이름
[WHERE 검색조건]
[GROUP BY 컬럼이름1, 컬럼이름2, ... 컬럼이름n]
[ORDER BY 컬럼이름 [정렬옵션]];

 

4) 예제

- 교수 테이블에서 학과별로 교수 수와 보직 수당을 받는 교수 수를 출력하시오.

- 학생 테이블에서 전체 학생을 소속 학과별로 나누고, 같은 학과 학생은 다시 학년별로 그룹핑하여 학과와 학년별로 인원수, 평균 몸무게를 출력하시오.

 

2. HAVING 절

1) HAVING

• SELECT 명령문의 WHERE절과 비슷한 기능을 하는 것으로 GROUP BY 절에서 조건 검색을 할 경우 반드시 HAVING 절을 사용해야한다.

SELECT 컬럼이름, [그룹함수(컬럼이름)]
FROM 테이블이름
[WHERE 검색조건]
[GROUP BY 컬럼이름1, 컬럼이름2, ... 컬럼이름n]
[HAVING 검색조건]
[ORDER BY 컬럼이름 [정렬옵션]];

 

2) HAVING 절을 사용해야 하는 경우 이해하기

- 학생 수가 4명 초과인 학년에 대해서 학년, 학생 수, 평균 키, 평균 몸무게를 출력하시오. 단, 출력 순서는 평균 키가 높은 순 부터 내림차순으로 출력.

GROUP BY 절만 사용하는 경우

GROUP BY 절만 사용할 경우 학생 수를 의미하는 COUNT(*)의 결과가 4 이하인 데이터도 모두 조회된다.

HAVING 절과 함께 사용

HAVING절에서 조건을 지정해 4보다 큰 데이터만 조회되었다.

• 집계 함수에 대한 검색 조건을 지정하고자 할 경우는 GROUP BY 절 뒤에 HAVING 절을 사용해야한다.

 

3) 예제

- 동일 학과 내에서 같은 학년에 재학중인 학생 수가 3명 이상인 그룹의 학과번호, 학년, 학생 수, 최대 키, 최대 몸무게를 출력하시오.

 

---

#2. JOIN

1. JOIN

1) JOIN이란?

• 두 개 이상의 테이블을 결합하여 필요한 데이터를 조회하는 기능이다.

SELECT 컬럼이름
FROM 테이블1이름, 테이블2이름, 테이블n이름
[WHERE 검색조건]
[GROUP BY 컬럼이름]
[ORDER BY 컬럼이름 [정렬옵션]];

• 조인하고자 하는 테이블의 이름을 콤마로 구분한다.
• 조인이 이루어지는 테이블에 각각 동일한 이름의 컬럼이 존재할 경우, SELECT 절에서 테이블이름.컬럼이름의 형식으로 명시 되어야한다.

SELECT department.deptno, professor.deptno
FROM department, professor;

# 아래처럼 줄여서 사용할 수 있다
SELECT d.deptno, p.deptno
FROM department d, professor p;

 

2) JOIN의 종류

• 카티션 곱(cross join), EQUI JOIN, INNER JOIN, OUTER JOIN 등

 

3) 카디션 곱(cross join)

• 위 경우와 같이 단순히 테이블 이름을 콤마로 구분하여 나열할 경우, 32건이 조회된다.
• 이는 두 개의 테이블에서 연결 가능한 모든 경우의 수를 조합하기 때문에 발생하는 현상으로, 이를 카디션 곱 이라 한다.
  • 항상 카디션 곱이 발생하지 않도록 주의해야 한다.
• 발생 원인은 WHERE 절에서 조인 조건을 명시하지 않거나 잘못 설정하여 양쪽 테이블을 연결하는 조건을 만족하는 행이 하나도 없는 경우 발생한다.
• 카디션 곱을 해결하기 위해서는 조인의 조건이 되는 적절한 WHERE 절을 명시해야한다.

 

2. EQUI JOIN

1) EQUI JOIN이란?

• SQL문에서 가장 많이 사용되는 조인으로 WHERE절에 조인 대상 테이블의 공통 칼럼에 대해 ' = ' 비교를 명시해 같은 값을 갖는 행을 연결하여 결과를 생성하는 조인 방법이다.

SELECT 테이블1.칼럼이름, 테이블2.칼럼이름
FROM 테이블1, 테이블2
WHERE 테이블1.칼럼이름 = 테이블2.칼럼이름;

professor 테이블과 department 테이블 모두 deptno 컬럼이 존재하므로, 이 값을 사용하여 JOIN 조건을 명시하되,

SELECT 절에서 deptno 컬럼을 명시할 때에 어떤 테이블에 속한 컬럼인지를 명확하게 하기 위해

테이블이름.컬럼이름의 형식을 사용해야한다.

 

2) 테이블 이름에 대한 별칭 사용하기

• FROM 절에 명시되는 테이블 이름에 공백으로 구분하여 별칭을 적용하면 SELECT 절이나 WHERE 절에서 풀네임을 명시하지 않아도 된다.

 

3) 예제

- 101번 학과에 소속된 교수들의 이름, 학과번호, 학과 이름을 조회하시오.

이미 JOIN 조건을 명시하기 위해 WHERE절이 사용되었으므로 추가적인 검색 조건은 AND 연산자를 사용한다. 검색 조건은 양쪽 테이블 중 하나에만 지정하면 된다.

 

2. INNER JOIN

1) INNER JOIN이란?

• EQUI JOIN을 다른 형태로 표현한 JOIN 처리
• EQUI JOIN과 INNER JOIN의 공통점은 두 테이블 모두에서 JOIN 조건을 만족하는 교집합을 조회한다는 점이다.

EQUI JOIN 구문

SELECT 테이블1.칼럼이름, 테이블2.칼럼이름
FROM 테이블1, 테이블2
WHERE 테이블1.칼럼이름 = 테이블2.칼럼이름;

INNER JOIN 구문

SELECT 테이블1.칼럼이름, 테이블2.칼럼이름
FROM 테이블1
INNER JOIN 테이블2
ON 테이블1.칼럼이름 = 테이블2.칼럼이름;

• EQUI JOIN에서 테이블 이름을 구분하는 콤마를 INNER JOIN이라는 키워드로 변경하고, WHERE은 ON으로 변경한다.

 

2) 예제

- 교수이름, 소속학과번호, 학과이름을 INNER JOIN으로 조회하시오.

- 101번 학과에 소속된 교수들의 이름, 학과번호, 학과이름을 INNER JOIN으로 조회하시오.

 

3. OUTER JOIN

1) OUTER JOIN이란?

• INNER JOIN이 JOIN 조건에 부합하는 행들만 JOIN이 발생하는 것이라면, OUTER JOIN은 조건에 부합하지 않는 행들까지도 포함시켜 결합하는 것을 의미한다.

 

2) OUTER JOIN의 종류

종류	설명
LEFT OUTER JOIN	조인절에서 명시한 테이블 중, 왼쪽의 테이블에 대해 조건에 부합하지 않는 데이터까지 조회한다.
RIGHT OUTER JOIN	조인절에서 명시한 테이블 중, 오른쪽의 테이블에 대해 조건에 부합하지 않는 데이터까지 조회한다.
FULL OUTER JOIN	조인에서 사용하는 모든 테이블에서 조건에 부합하지 않는 데이터까지 조회한다. 시스템의 성능에 큰 영향을 주기 때문에 실무에서는 잘 사용하지 않는다고 한다.

 

3) 예제

- 학생 이름과 담당 교수의 이름을 조회하시오. 단, 담당교수가 배정되지 않은 학생의 경우 이름만 조회

- 학생 이름과 담당 교수의 이름을 조회하시오. 단, 담당하는 학생이 없는 교수는 교수의 이름만 조회

 

4) 두 개 이상의 테이블에 대한 조인

- 학생의 이름과 학년, 소속학과의 이름과 담당교수의 이름을 모두 조회하시오.

두 개 이상의 테이블을 조인하는 경우 기준이 되는 하나의 테이블을 정하고, 기준 테이블에서 다른 테이블을 조인하도록 작성한다.

- 위 예제를 INNER JOIN으로 작성

- 학생의 이름, 학년, 소속학과 이름, 담당교수 이름+직급을 조회하시오. 단, 담당교수가 없는 학생의 경우 NULL로 표시