SQL 함수, JOIN

최종윤·2023년 8월 10일

문자형 함수

java에서 사용했던 문자열 method와 이름도 비슷한 함수들이 존재한다.

LOWER : 문자열을 소문자로
UPPER : 문자열을 대문자로
ASCII : 문자의 ASCII 값 반환
CHR/CHAR : ASCII 값에 해당하는 문자 반환
CONCAT : 문자열1, 2를 연결
SUBSTR/SUBSTRING : 문자열 중 m 위치에서 n개
의 문자 반환
LENGTH/LEN : 문자열 길이를 숫자 값으로 반환
CONCAT(‘RDBMS’,‘ SQL’) -> ‘RDBMS SQL’
SUBSTR(‘SQL Expert’,5,3) -> ‘Exp’

argument 값이 왼쪽 오른쪽 양옆 끝에 존재하는 것들을 모두 문자열에서 제외시킨다.

LTRIM(‘xxxYYZZxYZ’,‘x’) -> ‘YYZZxYZ’
RTRIM(‘XXYYzzXYzz’,‘z’) -> ‘XXYYzzXY’
TRIM(‘x’ FROM ‘xxYYZZxYZxx’) -> ‘YYZZxYZ’
숫자형 함수

math에서 썻던 method도 보인다.
SIGN(n) : 숫자가 양수면1 음수면-1 0이면 0 반환
MOD : 숫자1을 숫자2로 나누어 나머지 반환
CEIL/CEILING(n) : 크거나 같은 최소 정수 반환
FLOOR(n) : 작거나 같은 최대 정수 리턴

몇번 째 자리까지 남길 것인지,
argument가 3이면 4번째 자리에서 반올림하여 3번쨰 소수자리까지 남김
ROUND(38.5235,3) -> 38.524
ROUND(38.5235,1) -> 38.5
ROUND(38.5235) -> 39

반올림과 다르게 그 뒤에 자리를 버린다.
TRUNC(38.5235,3) -> 38.523
TRUNC(38.5235,1) -> 38.5
TRUNC(38.5235) -> 38

날짜형 함수

조회할 때의 시각을 같이 조회할 때 쓰는듯 하다.
SYSDATE/GETDATE() 현재날짜와 시각 출력

EXTRACT : 날짜 정보 추출 함수, 날짜 데이터에서 연도, 월, 일, 시, 분, 초 추출하기
ex) '날짜요소'는 'YEAR', 'MONTH', 'DAY', 시분초등 이 들어간다.
select extract('날짜요소' from 컬럼X) as 별칭 from 테이블A;

DATE 타입 컬럼에서 년,월,일을 조회하기 위해 YEAR, MONTH등을 쓰는 듯 하다.
TO_NUMBER(TO_CHAR(d,‘YYYY’))/YEAR(d)

NULL 관련 함수

내가 조회하는 컬럼이 NULL값인지 비교하기 위해 ==NULL이 아닌 IS NULL을 쓴다.

NVL(식1,식2)/ISNULL(식1,식2) : 식1의 값이 NULL 이면 식2 출력

NULLIF(식1,식2) : 식1이 식2와 같으면 NULL을 아니면 식1을 출력

COALESCE함수는 인자로 주어진 컬럼들 중에서 NULL이 아닌 첫 번째 값을 반환하는 함수입니다. 만약 A, B라는 컬럼을 인자로 COALSESC 함수로 주게 되면 A 컬럼 값이 NULL 값이 아닌 경우 A 값을 리턴하고 A가 NULL이고 B가 NULL이 아닌 경우 B 값을 리턴합니다. 모든 인수가 NULL이면 NULL을 반환합니다.

ex) SELECT A, B, COALESCE(A,B) FROM table_a;

집계 함수

  1. 여러 행들의 그룹이 모여서 그룹당 단 하나의 결과를 돌려주는 함수이다.
  2. GROUP BY 절은 행들을 소그룹화 한다.
  3. SELECT, HAVING, ORDER BY 절에 사용 가능-ALL : Default 옵션

-DISTINCT : 같은 값을 하나의 데이터로 간주 옵션COUNT(*) : NULL 포함 행의 수
COUNT(표현식) : NULL 제외 행의 수

SUM, AVG : NULL 제외 합계, 평균 연산
STDDEV : 표준 편차
VARIAN : 분산
MAX, MIN : 최대값, 최소값

GROUP BY, HAVING 절의 특징

  1. GROUP BY 절을 통해 소그룹별 기준을 정한 후, SELECT 절에 집계 함수를 사용한다.
  2. 집계 함수의 통계 정보는 NULL 값을 가진 행을 제외하고 수행한다.
  3. GROUP BY 절에서는 ALIAS 사용 불가
  4. 집계 함수는 WHERE 절에 올 수 없다.
  5. HAVING 절에는 집계함수를 이용하여 조건 표시o
  6. HAVING 절은 일반적으로 GROUP BY 뒤에 위치

집계 함수는 값들의 집합을 계산해서 하나의 값을 리턴하는 함수임.
COUNT(*)를 제외하고, 집계 함수는 NULL 값을 무시함.
집계 함수는 자주 SELECT문에서 GROUP BY절과 함께 사용됨.
WHERE절에 바로 집계함수 사용할 수 없음. GROUP BY를 하고 HAVING절에 집계함수를 쓰거나 WHERE절에 서브쿼리에서 집계함수를 사용할 수 있음.

NULL 값 포함하여 값 나타내기

NULL 값을 데이터에서 제외하고 평균을 구하려면 AVG 쓰면 됨. 그러나 NULL 값을 포함해서 평균을 구하려면 SUM으로 총합을 구하고 COUNT(*)로 나눠야함.

ORDER BY 특징

  1. SQL 문장으로 조회된 데이터들을 다양한 목적에
    맞게 특정한 칼럼을 기준으로 정렬하여 출력하는데
    사용한다.

  2. ORDER BY 절에 칼럼명 대신 ALIAS 명이나 칼럼
    순서를 나타내는 정수도 사용 가능하다.

  3. DEFAULT 값으로 오름차순(ASC)이 적용되며
    DESC 옵션을 통해 내림차순으로 정렬이 가능하다.

  4. SQL 문장의 제일 마지막에 위치한다.

  5. SELECT 절에서 정의하지 않은 칼럼 사용 가능

Oracle에서는 NULL을 가장 큰 값으로 취급하며 SQL
Server에서는 NULL을 가장 작은 값으로 취급한다.

SELECT 문장 실행 순서

FROM -> WHERE -> GROUP BY -> HAVING ->
SELECT -> ORDER BY

실행순서가 중요한 이유

쿼리의 실행 순서를 아는 것은 중요하다. 실행순서를 모르면 쿼리를 제대로 작성하기 어렵다. 예를 들어 보자.

OrderBy 절에서 Alias 사용

SELECT CONCAT(first_name, last_name) AS full_name
FROM user
ORDER BY full_name;
ORDER BY 절은 SELECT 절보다 뒤에 실행되기 때문에 SELECT 절의 결과를 사용할 수 있다.

Where 절에서 Alias 사용

SELECT CONCAT(first_name, last_name) AS full_name
FROM user
WHERE full_name = 'VioletBeach';
Where 절에서는 SELECT 절보다 먼저 실행된다. 즉, WHERE 절은 FROM 절의 결과를 가지고 필터링을 하는 용도이지 SELECT문 에서 사용한 AS를 활용할 수 없다. 그래서 해당 쿼리는 에러가 발생한다.

WHERE 절에서 Alias를 사용하려다가 원치 않는 결과를 받는다거나, ORDER BY 절에서 SELECT 절에서 사용된 함수를 또 호출해서 자원이 낭비되는 이슈를 막으려면 실행 순서에 대한 이해가 필요하다.

SELECT TOP(2) WITH TIES ENAME, SAL
FROM EMP
ORDER BY SAL DESC;

위는 급여가 높은 2명을 내림차순으로 출력하는데
같은 급여를 받는 사원은 같이 출력한다(WITH TIES)

적응

SQL 실행 순서를 익히기 위한 방법 중에 LINE DBA 분이 소개해준 방법으로 SQL을 실행 순서대로 작성하면 익히기 쉽다고 한다.

가령, SELECT -> FROM -> WHERE 순으로 작성하는 것이 아니라 FROM -> WHERE -> SELECT 순으로 작성하는 것이다.

그러면 중간에 성능적으로 튜닝할 수 있는 요소를 발견할 가능성이 높아진다고 한다.


join이란 인스타그램의 댓글창처럼, 유저의 아이디와 댓글의 내용을 동시에 보여줄 때처럼 서로 다른 각각의 테이블 속 데이터를 동시에 보여주려고 할 때 사용하는 SQL문이다.

JOIN :

두 개 이상의 테이블들을 연결 또는 결합하여
데이터를 출력하는 것
일반적으로 행들은 PK나 FK 값의 연관에 의해 JOIN
이 성립된다. 어떤 경우에는 PK, FK 관계가 없어도
논리적인 값들의 연관만으로 JOIN이 성립가능하다. 5가지 테이블을 JOIN 하기 위해서는 최소 4번의(N-1) JOIN
과정이 필요하다.

EQUI JOIN :

2 개의 테이블 간에 칼럼 값들이 서로
정확하게 일치하는 경우에 사용, 대부분 PK, FK의 관
계를 기반으로 한다. SELECT PLAYER.PLAYER_NAME
FROM PLAYER
위 SQL처럼 컬럼명 앞에 테이블 명을 기술해줘야 함

NON EQUI JOIN :

2개의 테이블 간에 칼럼 값들이
서로 정확하게 일치하지 않는 경우에 사용
‘=’ 연산자가 아닌 BETWEEN, >, <= 등 연산자 사용SELECT E.ENAME, E.JOB, E.SAL, S.GRADE
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HSAL;
위는 E의 SAL의 값을 S의 LOSAL과 HSAL 범위에서
찾는 것이다.

============================================

집합 연산자 :

두 개 이상의 테이블에서 조인을 사용하지 않고 연관된 데이터를 조회할 때 사용
SELECT 절의 칼럼 수가 동일하고 SELECT 절의 동일 위치에 존재하는 칼럼의 데이터 타입이 상호 호환할 때 사용 가능

일반 집합 연산자

  1. UNION : 합집합(중복 행은 1개로 처리)
  2. UNION ALL : 합집합(중복 행도 표시)
  3. INTERSECT : 교집합(INTERSECTION)
  4. EXCEPT,MINUS : 차집합(DIFFERENCE)
  5. CROSS JOIN : 곱집합(PRODUCT)

순수 관계 연산자 : 관계형 DB를 새롭게 구현

  1. SELECT -> WHERE
  2. PROJECT -> SELECT
  3. NATRUAL JOIN -> 다양한 JOIN
  4. DIVIDE -> 사용x
    {a,x}{a,y}{a,z} divdie {x,z} = {a}

FROM 절 JOIN 형태

  1. INNER JOIN
  2. NATURAL JOIN
  3. USING 조건절
  4. ON 조건절
  5. CROSS JOIN
  6. OUTER JOIN

INNER JOIN :

JOIN 조건에서 동일한 값이 있는 행만
반환, USING이나 ON 절을 필수적으로 사용

조인 (INNER JOIN) : 기준 테이블과 조인 테이블 모두 데이터가 존재해야 조회됨
아우터 조인 (OUTER JOIN) : 기준 테이블에만 데이터가 존재하면 조회됨

NATURAL JOIN :

두 테이블 간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI JOIN 수행, NATURAL
JOIN이 명시되면 추가로 USING, ON, WHERE 절에
서 JOIN 조건을 정의할 수 없다, SQL Sever는 지원x

USING 조건절

같은 이름을 가진 칼럼들 중에서 원하는 칼럼에 대해
서만 선택적으로 EQUI JOIN을 할 수 있다, JOIN 칼
럼에 대해서 ALIAS나 테이블 이름과 같은 접두사를
붙일 수 없다, SQL Server 지원x
t_dept 테이블과 dept_temp 테이블이 있을 때, 두 테이블은 서로 이름이 같은 컬럼이 3개가 있다.

using 조건절을 이용해서 'DEPTNO' 컬럼에 대해서만 [INNER] JOIN을 수행해보자.
SELECT * FROM t_dept JOIN dept_temp USING (DEPTNO);
USING 조건절의 기준이 되는 칼럼이 맨 앞에 출력하고, 하나의 컬럼으로 나타낸다.

USING 조건절을 이용한 EQUI JOIN에서도 NATURAL JOIN과 마찬가지로 JOIN 칼럼에 대해서는 별칭(ALIAS)이나 테이블 이름과 같은 접두사를 붙일 수 없다.

select t_dept.deptno (XXX) select deptno(d)

ON 조건절

ON 조건절과 WHERE 조건절을 분리하여 이해가 쉬
우며, 칼럼명이 다르더라도 JOIN 조건을 사용할 수 있
는 장점이 있다, ALIAS나 테이블명 반드시 사용

CROSS JOIN

크로스 조인은 기준 테이블(emp)의 행을 조인 테이블(dept) 행만큼 증가를 시킨다.
아주 가끔씩 사용할 일이 있으니 개념만 이해하면 된다.

양쪽 집합의 M*N건의 데이터 조합이 발생한다. OUTER JOIN
JOIN 조건에서 동일한 값이 없는 행도 반환 가능하다, USING이나 ON 조건절 반드시 사용해야 함

OUTER JOIN 의 종류

Left Join
Right Join
Full Join

Outer Join은 Inner Join과 달리 3가지 종류가 있으며, 그 이름처럼 교집합 외부에 존재하는 데이터를 가져올 때 사용한다.
이렇게 다양한 Join 중에서 LEFT 조인이 일반적으로 가장 많이 사용된다.

LEFT OUTER JOIN

조인 수행시 먼저 표기된 좌측 테이블에 해당하는 데
이터를 읽은 후,
나중 표기된 우측 테이블에서 JOIN 대상 데이터를 읽어 온다.
우측 값에서 같은 값이 없는 경우 NULL 값으로 채운다.

모든 좌측 테이블을 가져오되 조인 가능한 것은 붙이고, 조인 불가능한 것은 NULL로 채운다.

RIGHT OUTER JOIN 는 모든 좌측 테이블을 가져오되 조인 가능한 것은 붙이고, 조인 불가능한 것은 NULL로 채운다.LEFT OUTER JOIN의 반대


INNER 조인 vs LEFT 조인
INNER 조인의 경우 양측 모두에 존재하는 것만 결과로 만든다.
반면 LEFT 조인은 좌측 테이블 중 조인 불가능한 것들도 모두 결과로 만든다.
이때, 조인 불가능한 우측 테이블은 값은 NULL로 채워진다.

(1) 모든 사진의 "파일명", 게시자가 있다면 "닉네임"도 함께 조회
SELECT photos.filename, users.nickname
FROM photos
LEFT JOIN users ON users.id = photos.user_id
여기서 FROM 의 photos 테이블이 LEFT 조인이 된다.
LEFT JOIN 키워드를 쓰면 Join 되지 않는 나머지 데이터도 함께 출력 된다.

Left Join이 아니라 그냥 JOIN 을 하는 경우 Inner Join이 되며, Join 되지 않는 부분은 출력되지 않는다.

FULL OUTER JOIN

조인 수행시 좌측, 우측 테이블의 모든 데이터를 읽어
JOIN하여 결과를 생성한다. 중복 데이터는 삭제한다.

FULL JOIN은 좌측,우측 상관없이 데이터가 있는 것은 모두 가져오고 없는 것은 모두 Null 이 되는 것이다.

왼쪽에만 있고 오른쪽에 없는 데이터도 left join에서 모두 조회했던것 처럼
full join은 왼쪽에만 있는것도 오른쪽에만 있는것도 모두 조회한다.

profile
최종윤
https://github.com/jyzayu
이전 포스트

SQL 기본

다음 포스트

10->2진수 변환

0개의 댓글