[우리FISA] SQL Query :: Functions && JOIN

SQL Functions & JOIN

• SQL 함수는 데이터베이스에서 데이터를 처리하거나 조작하기 위해 사용되는 함수이다.
• 내장 함수(Built-in Function)와 사용자 정의 함수(User Defined Function)로 나뉜다.

내장 함수(Built-in Function)

• 데이터베이스에서 기본적으로 제공하는 함수이다.

문자열 함수

함수	설명
LENGTH()	문자열의 길이를 반환한다.
CONCAT()	문자열을 결합한다.
SUBSTRING()	문자열의 일부분을 반환한다.
REPLACE()	문자열을 치환한다.
UPPER()	문자열을 대문자로 변환한다.
LOWER()	문자열을 소문자로 변환한다.
TRIM()	문자열의 양쪽 공백을 제거한다.
LTRIM()	문자열의 왼쪽 공백을 제거한다.
RTRIM()	문자열의 오른쪽 공백을 제거한다.

숫자 함수

함수	설명
ROUND()	반올림한다.
CEIL()	올림한다.
FLOOR()	내림한다.
ABS()	절대값을 반환한다.
MOD()	나머지를 반환한다.
SQRT()	제곱근을 반환한다.
POWER()	거듭제곱을 반환한다.
RAND()	무작위 수를 반환한다.
SIGN()	부호를 반환한다.

날짜 함수

함수	설명
NOW()	현재 날짜와 시간을 반환한다.
CURDATE()	현재 날짜를 반환한다.
CURTIME()	현재 시간을 반환한다.
DATE()	날짜를 반환한다.
TIME()	시간을 반환한다.
DAY()	일을 반환한다.
MONTH()	월을 반환한다.
YEAR()	년을 반환한다.
HOUR()	시를 반환한다.
MINUTE()	분을 반환한다.
SECOND()	초를 반환한다.
DAYNAME()	요일을 반환한다.
MONTHNAME()	월을 반환한다.
PERIOD_ADD()	날짜에 개월 수를 더한다.

집계 함수

함수	설명
COUNT()	행의 개수를 반환한다.
SUM()	합계를 반환한다.
AVG()	평균을 반환한다.
MAX()	최대값을 반환한다.
MIN()	최소값을 반환한다.

윈도우 함수

• 윈도우 함수는 행과 행 사이의 관계를 계산하는 함수이다.
• 윈도우 함수는 OVER 절을 사용하여 정의한다.
• 윈도우 함수는 집계 함수와 윈도우 함수를 함께 사용할 수 있다.

SELECT
    column1,
    column2,
    AGGREGATE_FUNCTION(column3) OVER (PARTITION BY column4 ORDER BY column5)
FROM
    table_name;

# PARTITION BY : 그룹을 나누는 기준
# ORDER BY : 정렬 기준

함수	설명
ROW_NUMBER()	행 번호를 반환한다.
RANK()	순위를 반환한다.
DENSE_RANK()	밀집 순위를 반환한다.
NTILE()	그룹의 순위를 반환한다.
LAG()	이전 행의 값을 반환한다.
LEAD()	다음 행의 값을 반환한다.
FIRST_VALUE()	첫 번째 행의 값을 반환한다.
LAST_VALUE()	마지막 행의 값을 반환한다.
PERCENT_RANK()	백분율 순위를 반환한다.
CUME_DIST()	누적 분포를 반환한다.
PERCENTILE_CONT()	백분위 값을 반환한다.

SQL 변수

• SQL도 다른 일반적인 프로그래밍 언어처럼 변수를 선언하고 사용할 수 있다.
  • 변수는 데이터베이스에 저장된 값을 저장하고 사용하는 데 사용된다.
• 변수는 다음과 같이 선언한다.
  • SET @variable_name = value;
  • SELECT @variable_name;

SET @variable_name = value;
SELECT @variable_name;

SET @변수이름 = 변수의 값; -- 변수 선언 및 값 대입
SELECT @변수이름; -- 변수의 값 출력

SET @count = 5;
SELECT code, name, continent, region, population
  FROM country
 WHERE population > 100000000
 ORDER BY @count ASC; -- 변수 사용

• SELECT문에서 행의 개수를 제한하는 LIMIT문에는 변수를 사용할 수 없다.
  • 이 때는 PREPARE, EXCUTE문을 사용한다.
• PREPARE문은 SQL문을 준비하고, EXECUTE문은 준비된 SQL문을 실행한다.

SET @count = 5;
PREPARE mySQL FROM 'SELECT code, name, continent, region, population
  FROM world.country
 WHERE population > 100000000
 ORDER BY 1 ASC
 LIMIT ?';
EXECUTE mySQL USING @count;

사용자 정의 함수(User Defined Function)

• 사용자 정의 함수는 사용자가 직접 정의한 함수이다.

CREATE FUNCTION 함수이름(매개변수1 데이터타입, 매개변수2 데이터타입, ...)
RETURNS 반환값 데이터타입

BEGIN
    함수 내용
END;

CREATE FUNCTION getPopulation(country_code CHAR(3))
RETURNS INT

BEGIN
    DECLARE population INT;

    SELECT population INTO population
      FROM country
     WHERE code = country_code;

    RETURN population;
END;

• 먼저 함수를 선언하고, BEGIN과 END 사이에 함수 내용을 작성한다.
• DECLARE문을 사용하여 변수를 선언하고, SELECT문을 사용하여 데이터를 조회한다.

JOIN

• JOIN은 두 개 이상의 테이블을 연결하여 데이터를 조회하는 방법.
• JOIN은 INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN으로 나뉜다.

ssollacc.tistory.com

INNER JOIN

• INNER JOIN은 교집합을 반환한다.
• INNER JOIN은 ON 절을 사용하여 조인 조건을 지정한다.

SELECT column_name(s)
  FROM table1
 INNER JOIN table2
    ON table1.column_name = table2.column_name;

LEFT JOIN

• LEFT JOIN은 왼쪽 테이블의 모든 행과 오른쪽 테이블의 일치하는 행을 반환한다.
• LEFT JOIN은 LEFT JOIN 또는 LEFT OUTER JOIN으로 사용한다.
  • LEFT JOIN과 LEFT OUTER JOIN은 동일한 결과를 반환한다.

SELECT column_name(s)
  FROM table1
  LEFT JOIN table2
    ON table1.column_name = table2.column_name;

RIGHT JOIN

• RIGHT JOIN은 오른쪽 테이블의 모든 행과 왼쪽 테이블의 일치하는 행을 반환한다.
• RIGHT JOIN은 RIGHT JOIN 또는 RIGHT OUTER JOIN으로 사용한다.
  • RIGHT JOIN과 RIGHT OUTER JOIN은 동일한 결과를 반환한다.
• RIGHT JOIN은 LEFT JOIN과 반대로 작동한다.
• 보통은 LEFT JOIN을 사용. RIGHT JOIN은 LEFT JOIN으로 대체할 수 있다.

SELECT column_name(s)
  FROM table1
 RIGHT JOIN table2
    ON table1.column_name = table2.column_name;

FULL JOIN

• FULL JOIN은 왼쪽 테이블과 오른쪽 테이블의 모든 행을 반환한다.
  • LEFT JOIN과 RIGHT JOIN의 결과를 합친 것과 같다. = 합집합
• FULL JOIN은 FULL JOIN 또는 FULL OUTER JOIN으로 사용한다.
  • FULL JOIN과 FULL OUTER JOIN은 동일한 결과를 반환한다.

SELECT column_name(s)
  FROM table1
  FULL JOIN table2
    ON table1.column_name = table2.column_name;

CROSS JOIN

• CROSS JOIN은 두 테이블의 카테시안 곱을 반환한다.
  • 카테시안 곱: 두 테이블의 모든 행을 조합한 결과
  • 행의 개수가 n개, m개인 두 테이블을 CROSS JOIN하면 n * m개의 행이 반환된다.
• CROSS JOIN은 CROSS JOIN 또는 INNER JOIN으로 사용한다.
  • CROSS JOIN과 INNER JOIN은 동일한 결과를 반환한다.

SELECT column_name(s)
  FROM table1
 CROSS JOIN table2;

SELF JOIN

• SELF JOIN은 테이블 자신을 조인하는 것이다.
  • 테이블을 복사하여 조인하는 것이 아니라, 동일한 테이블을 조인한다.
  • 테이블을 별칭(alias)을 사용하여 구분한다.
• SELF JOIN은 SELF JOIN 또는 SELF INNER JOIN으로 사용한다.

SELECT column_name(s)
  FROM table1 T1, table1 T2
 WHERE condition;

JOIN에서 유의할 점

• JOIN은 두 개 이상의 테이블을 연결하여 데이터를 조회하는 방법이다.

조인하는 테이블에는 같은 값을 가진 칼럼이 있어야 한다

조인에 참여하는 테이블은 이름이 같을 필요는 없으나 값은 같은 값을 가진 조인 칼럼이 있어야 한다.
물론 조인 칼럼의 이름까지 동일하게 만드는 경우가 많다.
--> 테이블 간 조인 칼럼을 쉽게 찾을 수 있기 때문.

2개 이상의 테이블을 조인할 수 있다

하나의 조인은 기본으로 2개 테이블을 대상으로 이뤄진다.
하지만 원하는 정보가 3개나 4개 테이블에 흩어져 있어도 조인 칼럼이 있다면, 하나의 SELECT 문장에서 여러 테이블을 조인해 원하는 정보를 조회할 수 있다.
어떤 식으로 조인이 이뤄질지 == 테이블을 어떻게 설계했느냐에 따라 달라진다.

조인할 때 테이블에 대한 별칭(as, Alias)을 사용한다

조인한다는 것은 SELECT 문에서 여러 테이블을 사용한다는 것이다.
--> 조인할 때 보통 테이블에 대한 별칭을 사용한다.

조인 시 조인 조건이 필요하다

조인할 때는 조인 조건이 필요하다.

조인에 속하지만 엄밀히 말하면 조인이 아닌, 즉 조인 조건이 없는 조인도 있다.
이를 크로스조인의 결과로 나오는 카테시안 곱이라고 한다.

INNER JOIN vs OUTER JOIN

INNER JOIN은 교집합을 반환한다.
OUTER JOIN은 INNER JOIN에 포함되지 않는 나머지를 반환한다.