MySQL(그룹조회, 조인)

그룹 조회

GROUP BY 절

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

select {컬럼이름 [as 별칭], 그룹함수(컬럼명)} from 테이블이름 [where 검색조건][group by 컬럼이름]
[order by 컬럼이름[정렬옵션] ]

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

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

group by 절에서 명시되지 않은 칼럼을 select에서 사용할 경우 집계 함수를 사용해 그룹별 통계 데이터를 얻을 수 있음.

예제 1)

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

다중 칼럼을 이용한 그룹별 검색

group by 절에서 두 개 이상의 칼럼을 콤마(,)로 구분하여 명시할 경우 첫 번째 칼럼에 대한 1차 그룹을 형성 후,
각 그룹 안에서 두 번째 칼럼에 대해 2차 그룹을 형성한다.

select {컬럼이름 [as 별칭], 그룹함수(컬럼명)} from 테이블이름 [where 검색조건]
[group by 컬럼1이름, 컬럼2이름, ~~, 컬럼n이름][order by 컬럼이름[정렬옵션] ]

예제 2)

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

HAVING 절

select 명령문의 where 절과 비슷한 기능을 하는 것으로 group by 절에서 조건 검색을 할 경우 반드시 HAVING 절을 사용

select {컬럼이름 [as 별칭], 그룹함수(컬럼명)} from 테이블이름 [where 검색조건]
[group by 컬럼1이름, 컬럼2이름, ~~, 컬럼n이름][having 검색조건]
[order by 컬럼이름[정렬옵션] ]

having 절을 사용해야 하는 경우 이해하기 (1)

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

group by 절만 사용할 경우 학생 수를 의미하는 count(*)의 결과가 4 이하인 데이터도 조회됨.
집계 함수(count, max, min, avg)는 where 절에서 사용하지 못함
-> 이때 having 절을 쓴다.

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

집계 함수에 대한 검색조건을 지정하고자 할 경우는 group by 절 뒤에 having 절을 사용해야 함

• count(*)의 결과가 4보다 큰 데이터만 조회되었음.

예제 1)

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

조인

두 개 이상의 테이블을 결합하여 필요한 데이터를 조회하는 기능.
예) 김도훈 교수의 이름과 소속 학과이름을 출력하려 할 때, professor테이블과 department테이블을
두 번 조회해야 하지만 join을 사용하면 한 번에 가능.

join의 종류

카티션 곱(cross join), EQUI JOIN, INNER JOIN, OUTER JOIN 등이 있다.

Join의 기본 문법

select {컬럼이름 [ as 별칭 ] } from 테이블1이름, 테이블2이름, ~~~, 테이블n이름
[where 검색조건][group by 컬럼이름][order by 컬럼이름 [정렬옵션] ]

조회하고자 하는 테이블의 이름을 콤마(,)로 구분.
조인이 이루어지는 테이블에 각각 동일한 이름의 칼럼이 존재할 경우, select절에서 '테이블 이름. 칼럼 이름'의 형식으로 명시해야 함.

교수 테이블과 학과 테이블을 조인하기

단순히 테이블 이름을 콤마로 구분하여 나열하면, 32개의 값이 조회됨.
이는 두 개의 테이블에서 연결 가능한 모든 경우의 수를 조합하기 때문에 발생하는 것으로 '카디션 곱'이라 함.
항상 카디션 곱이 발생하지 않도록 주의해야 함.

카디션 곱의 원인

where 절에서 조인 조건을 명시하지 않거나 잘못 설정하여 양쪽 테이블을 연결하는 조건을 만족하는 행이 하나도 없는 경우
카디션 곱을 해결하기 위해서는 join의 조건이 되는 적절한 where 절을 명시.

EQUI JOIN

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

select 테이블1이름.컬럼이름, 테이블2이름.컬럼이름 from 테이블1이름, 테이블2이름
where 테이블1이름.컬럼이름 = 테이블2이름.컬럼이름;

예제 1)

교수의 이름과 소속 학과 번호, 학과 이름을 조회.

테이블 이름에 대한 별칭 사용

from절에 명시되는 테이블이름에 공백으로 구분하여 별칭을 적용하면 select절이나 where절에 풀네임을 명시 안해도됨.

예제 2)

교수이름, 소속학과 번호, 학과 이름 조회

예제 3)

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

이미 join의 조건을 명시하기 위해 where 절이 사용 되었기 때문에 추가적인 검색조건은 and 연산자 사용

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이름.컬럼이름;

예제 1)

교수이름, 소속학과 번호, 학과이름을 INNER JOIN으로 조회

예제 2)

101번 학과에 소속된 교수들의 이름, 전화번호, 학과 이름을 INNER JOIN으로 조회

OUTER JOIN의 이해

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

OUTER JOIN의 종류

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

예제 1)

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

예제 2)

학생이름과 담당 교수의 이름 조회. 단, 담당하는 학생이 없는경우 교수의 이름만 조회

두 개 이상의 테이블에 대한 JOIN

예제 1)

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

예제 2)

위 예제를 INNER JOIN으로 구성

예제 3)

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