개키우는개발자 : )

WHERE 절에서 여러 값을 조회할 때 IN을 많이 쓴다. 그런데 IN, ANY, EXISTS 중에 뭐가 가장 빠를까? PostgreSQL의 실행계획을 분석하면서 정리한다.IN, ANY, EXISTS 기본 문법먼저 세 가지 문법을 비교해보자.IN 절SELECT * FROM usersWHERE id IN (1, 2, 3, 4, 5);-- 서브쿼리 사용SELECT * FROM usersWHERE id IN (SELECT user_id FROM orders WHERE status = 'completed');ANY 연산자SELECT * FROM usersWHERE id = ANY(ARRAY[1, 2, 3, 4, 5]);-- 서브쿼리 사용SELECT * FROM usersWHERE id = ANY(SELECT u..

CREATE TABLE은 가장 기본적인 DDL이지만, 제대로 활용하면 성능과 유지보수에 큰 차이가 난다. 기본 문법은 이미 알고 있다고 가정하고, 실무에서 자주 쓰는 고급 기능들을 정리한다.목차CREATE TABLE IF NOT EXISTSCREATE TABLE AS SELECT (CTAS)제약조건 심화테이블 상속 (INHERITS)파티셔닝 (PARTITION BY)임시 테이블 (TEMPORARY)UNLOGGED 테이블LIKE 절로 테이블 복사1. CREATE TABLE IF NOT EXISTS테이블이 이미 있으면 에러 없이 넘어간다. 스크립트 여러 번 실행할 때 유용하다.-- 테이블이 있으면 에러 발생CREATE TABLE users ( id SERIAL PRIMARY KEY, name VA..

다른 테이블의 값을 참조해서 UPDATE 해야 할 때가 있다. 예를 들어 주문 테이블의 상품명을 상품 테이블에서 가져와서 수정하는 경우다. PostgreSQL에서는 FROM 절을 사용해서 JOIN UPDATE를 할 수 있다.MySQL과 문법이 다르다MySQL에서는 UPDATE에 JOIN을 직접 쓴다.-- MySQL 문법UPDATE orders oJOIN products p ON o.product_id = p.idSET o.product_name = p.name;PostgreSQL에서는 이 문법이 안 된다. FROM 절을 사용해야 한다.-- PostgreSQL 문법UPDATE orders oSET product_name = p.nameFROM products pWHERE o.product_id = p.id..

UPDATE를 실행하고 나서 수정된 데이터를 다시 SELECT 하는 경우가 많다. PostgreSQL에서는 RETURNING 절을 사용하면 UPDATE와 SELECT를 한번에 처리할 수 있다.RETURNING이 필요한 상황보통 이런 식으로 작성한다.-- 1. 데이터 수정UPDATE usersSET point = point + 100WHERE id = 1;-- 2. 수정된 데이터 조회SELECT id, name, point FROM users WHERE id = 1;쿼리를 2번 실행해야 한다. API 개발할 때 이런 패턴이 자주 나온다. RETURNING을 쓰면 한번에 처리된다.기본 문법UPDATE 끝에 RETURNING 절을 추가한다.UPDATE TABLE_NAMESET COLUMN = VAL..

UPDATE는 단순히 데이터를 수정하는 것처럼 보이지만, 대량의 데이터를 처리할 때는 생각보다 많은 문제가 발생한다. 실무에서 100만 건 이상의 데이터를 UPDATE 할 때 겪었던 문제들과 해결 방법을 정리한다.UPDATE가 느린 이유PostgreSQL에서 UPDATE는 내부적으로 DELETE + INSERT와 비슷하게 동작한다. 기존 행을 삭제 표시하고 새로운 행을 추가하는 방식이다. 이를 MVCC(Multi-Version Concurrency Control)라고 한다.그래서 UPDATE를 많이 하면테이블 크기가 커진다 (Dead Tuple 증가)인덱스도 새로 추가된다VACUUM이 필요해진다-- 테이블의 Dead Tuple 확인SELECT relname, n_live_tup, n_d..

테이블 구조 변경 한번 만들어진 테이블 이여도 테이블 구조를 변경 할 수 있습니다. 이 기능으로 인해 업무변화에 유연하게 대처할 수 있습니다. 실습 - LINKS 테이블 생성 CREATE TABLE LINKS ( LINK_ID SERIAL PRIMARY KEY, TITLE VARCHAR(512) NOT NULL, URL VARCHAR(1024) NOT NULL UNIQUE ); - LINKS 테이블에 ACTIVE 컬럼을 추가 ALTER TABLE LINKS ADD COLUMN ACTIVE BOOLEAN; - LINKS 테이블에 ACTIVE 컬럼을 제거 ALTER TABLE LINKS DROP COLUMN ACTIVE; - TITLE 컬럼을 LINK_TITLE 컬럼으로 변경 ALTER TABLE LINKS..

CTAS 란 CREATE TABLE AS SELECT 의 약어로써 SELECT 문을 기반으로 CREATE TABLE을 할 수 있는 CREATE문이다. 기본문법 - 새로운 테이블명의 이름을 작성 후 SELECT 문을 작성합니다. CREATE TABLE NEW_TABLE AS SELECT - 새로운 테이블명의 이름과 컬럼명을 작성 후 SELECT 문을 작성합니다. CREATE TABLE NEW_TABLE(NEW_COLUMN_1, NEW_COLUMN_2) AS SELECT - 기존의 테이블이 존재하지 않는 경우만 생성되며 생성된 후 SELECT 문을 작성합니다. CREATE TABLE IF NOT EXISTS NEW_TABLE AS SELECT 실습 - FILM 테이블과 FILM_CATEGORY 테이블을 조인..

테이블 생성 테이블은 데이터를 담는 그릇으로써 반드시 생성해야만 데이터를 저장 할 수 있습니다. 테이블 생성시 컬럼의 제약 조건 제약조건명 설명 NOT NULL 해당 제약 조건이 있는 컬럼은 NULL이 저장될 수 없습니다. UNIQUE 해당 제약 조건이 있는 컬럼의 값은 테이블 내에서 유일해야 합니다.(중복X) PRIMARY KEY 해당 제약 조건이 있는 컬럼의 값은 테이블내에서 유일해야 하고 반드시 NOT NULL 이어야 합니다. CHECK 해당 제약 조건이 있는 컬럼은 지정하는 조건에 맞는 값이 들어가야 합니다. REFERENCES 해당 제약 조건이 있는 컬럼의 값은 참조하는 테이블의 특정 컬럼에 값이 존재해야 합니다. 실습 - ACCOUNT_TEST 테이블 생성 1. USER_ID는 기본키 이고 N..

데이터 타입 테이블은 컬럼으로 이루어져 있고 컬럼은 다양한 데이터 타입을 지원합니다. 이는 RDBMS가 제 역할을 하는데 있어서 매우 중요한 부분입니다. Boolean, Character, Numeric 데이터 타입 설명 Boolean Boolean 참(true)과 거짓(false)의 값을 저장 Character CHAR 고정형 길이의 문자열을 저장. 만약CHAR(10)인데 'ABCDE'만 입력할 경우 실제로는 'ABCDE '로 뒤에 공백을 붙여 저장 됩니다. VARCHAR 가변형 길이의 문자열을 저장합니다. 만약 VARCHAR(10) 인데, 'ABCDE'만 입력할 경우 실제로 'ABCDE'만 저장합니다.( 공백X ) TEXT 대용량의 문자데이터를 저장합니다. Numeric INT 정수형 데이터를 저장합..

IMPORT 작업 IMPORT는 다른 형식의 데이터를 테이블에 넣는 작업을 말합니다. 데이터 구축시 자주 사용됩니다. 실습준비 - CATEGORY_IMPORT 테이블을 생성합니다. CREATE TABLE CATEGORY_IMPORT ( CATEGORY_ID SERIAL NOT NULL, "NAME" VARCHAR(25) NOT NULL, LAST_UPDATE TIMESTAMP NOT NULL DEFAULT NOW(), CONSTRAINT CATEGORY_IMPORT_PKEY PRIMARY KEY (CATEGORY_ID) ); 실습 - CATEGORY 테이블에서 EXPORT한 DB_CATEGORY_2.csv 파일을 CATEGORY_IMPORT 테이블로 IMPORT합니다. COPY CATEGORY_IMPOR..

EXPORT 작업 이란 EXPORT는 테이블의 데이터를 다른 형태의 데이터로 추출 하는 작업입니다. 대표적으로 CSV 형식으로 가장 많이 추출합니다. 실습 - C드라이브에 TMP폴더 생성 후 CATEGORY 테이블의 데이터를 csv형식 파일로 저장합니다. COPY CATEGORY(CATEGORY_ID,NAME,LAST_UPDATE) TO 'C:\TMP\DB_CATEGORY.csv' DELIMITER ',' CSV HEADER; TMP 디렉토리는 반드시 있어야 합니다. - TXT형식으로 저장 COPY CATEGORY(CATEGORY_ID,NAME,LAST_UPDATE) TO 'C:\TMP\DB_CATEGORY.txt' DELIMITER '|' CSV HEADER; - 컬럼 없이 csv형식으로 저장 COPY..

UPSERT 문 INSERT를 시도할때 조건(상황)에 따라 UPDATE를 할 수 있는 구문입니다. 복잡한 업무 처리에 자주 사용 됩니다. 기본문법 - INSERT가 충돌 시 다른 액션을 취합니다. INSERT INTO TABLE_NAME(COLUMN_1) VALUES(VALUE_1) ON CONFLICT TARGET ACTION; 실습준비 - CUSTOMERS 테이블 생성 후 데이터 저장 CREATE TABLE CUSTOMERS ( CUSTOMER_ID SERIAL PRIMARY KEY, NAME VARCHAR UNIQUE, EMAIL VARCHAR NOT NULL, ACTIVE BOOL NOT NULL DEFAULT TRUE ); INSERT INTO CUSTOMERS (NAME,EMAIL) VALUE..

DELETE 문 테이블의 특정 데이터를 삭제하거나 테이블 내에서 존재하는 모든 데이터를 삭제 할 수 있습니다. 기본문법 삭제할 테이블 작성 후 조건 입력 DELETE FROM TARGET_TABLE A WHERE 조건식; 실습 - LINK테이블의 ID가 5인 행을 삭제 DELETE FROM LINK WHERE ID = 5; COMMIT; - DELETE JOIN 사용하여 LINK_TMP 테이블과 LINK 테이블의 ID값으로 조인 후 삭제 DELETE FROM LINK_TMP A USING LINK B WHERE A.ID = B.ID; COMMIT; 중복값 데이터 삭제 - LINK테이블의 모든 데이터를 삭제 DELETE FROM LINK; COMMIT; - 경고 문구를 확인하면 모든데이터는 삭제가 됩니다.

UPDATE JOIN 문 UPDATE시 다른 테이블의 내용을 참조 하고 싶을 때 UPDATE JOIN 문을 사용 합니다. 복잡한 업무를 처리하는데 매우 유용한 방법 입니다. 기본문법 - UPDATE할 테이블을 작성 후 특정 컬럼을 UPDATE합니다. 참조 테이블을 작성하고 JOIN조건을 입력합니다. UPDATE TARGET_TABLE A SET A.COLUMN_1 = 표현식 FROM REF_TABLE B WHERE A.COLUMN_1 = B.COLUMN_1; 실습준비 - PRODUCT_SEGMENT 테이블 생성 후 데이터 저장 CREATE TABLE PRODUCT_SEGMENT ( ID SERIAL PRIMARY KEY, SEGMENT VARCHAR NOT NULL, DISCOUNT NUMERIC (4,..

UPDATE 문법 테이블의 존재하는 데이터를 수정하는 작업이다. 업무를 처리하는데 필수적인 것이며 동시성에 유의해야 한다. 기본문법 - 업데이트 할 테이블을 작성하고 수정할 컬럼과 데이터를 입력 후 조건을 입력 UPDATE TABLE_NAME SET COLUMN_1 = VALUE1, COLUMN_2 = VALUE2 WHERE 조건; - UPDATE는 대상 행에 대해서 락을 잡습니다. - 락이란 다른 사용자는 해당 행에 대해서 작업을 할 수 없습니다. - UPDATE를 한 후 빠르게 COMMIT을 하지 않는다면 RDBMS의 동시성이 낮아집니다. - SELECT 에 명시적(선택적)으로 락을 잡을 수 있습니다. 예를 들어 인터넷으로 영화를 예매 할 때 여러 인원이 제한된 좌석을 선택하는 상황이라면 동시에 A좌..

INSERT 란? 테이블이 만들어지면 빈 공간이 만들어 지는 것이며 테이블 안에 데이터를 저장하는것 입니다. 기본문법 - 테이블 이름만 작성하면 테이블에 포함되어 있는 컬럼 순서대로 입력 INSERT INTO TABLE_NAME VALUES ( VALUE1, VALUE2, VALUE3, ... ); - 테이블의 특정 컬럼을 작성하면 컬럼을 지정하여 저장할 수 있습니다. INSERT INTO TABLE_NAME ( COLUMN1, COLUMN2 ) VALUES ( VALUE1, VALUE2 ); 실습준비 - LINK 테이블 만들기 CREATE TABLE LINK( ID SERIAL PRIMARY KEY, URL VARCHAR(255) NOT NULL, NAME VARCHAR(255) NOT NULL, DE..

실습문제 - SQL문은 EXCEPT 연산을 사용하여 재고가 없는 영화를 구하고 있습니다. 해당 SQL문은 EXCEPT연산을 사용하지 말고 같은 결과를 조회 하십시오. 문제 SQL SELECT FILM_ID, TITLE FROM FILM EXCEPT SELECT DISTINCT INVENTORY.FILM_ID, TITLE FROM INVENTORY INNER JOIN FILM ON FILM.FILM_ID = INVENTORY.FILM_ID ORDER BY TITLE; 재고가 없는 영화를 조회합니다. 첫번째 방법 SELECT A.FILM_ID, A.TITLE FROM FILM A WHERE NOT EXISTS ( SELECT 1 FROM INVENTORY B, FILM C WHERE B.FILM_ID = C..

실습문제 - FILM 테이블을 2번이나 스캔하고 있다. FILM 테이블을 한번만 SCAN하여 동일한 결과 집합을 구하는 SQL을 작성하세요. 문제 SQL SELECT FILM_ID, TITLE, RENTAL_RATE FROM FILM WHERE RENTAL_RATE > ( SELECT AVG(RENTAL_RATE) FROM FILM ) 1. 평균값을 집계함수를 활용하여 AVG값을 보여줍니다. 2. 인라인 뷰 서브쿼리를 활용하여 FILM 테이블 A가 됩니다. 3. 조건을 넣어 원하는 결과값을 조회 합니다. SELECT FILM_ID, TITLE, RENTAL_RATE FROM ( SELECT FILM_ID, TITLE, RENTAL_RATE, AVG(A.RENTAL_RATE) OVER() AS AVG_RE..

EXISTS 연산자란? 서브쿼리 내에 집합이 존재하는지 존재 여부만을 판단한다. 존재 여부만을 판단하므로 연산 시 부하가 줄어든다. 실습 - CUSTOMER 테이블 에서 지불내역이 있고 11달러를 초과한 고객을 조회합니다. SELECT FIRST_NAME, LAST_NAME FROM CUSTOMER C WHERE EXISTS ( SELECT 1 FROM PAYMENT P WHERE P.CUSTOMER_ID = C.CUSTOMER_ID AND P.AMOUNT > 11 ) ORDER BY FIRST_NAME, LAST_NAME 해당 집합이 존재하기만 하면 더이상 연산을 멈추므로 성능상 유리합니다. - CUSTOMER 테이블 에서 지불내역이 있고 11달러를 초과한 적이 없는 고객을 조회합니다. SELECT F..

ALL 연산자란? 값을 서브 쿼리에 의해 반환된 값 집합과 비교한다. ALL 연산자는 서브쿼리의 모든 값이 만족을 해야만 조건이 성립된다. 실습 - FILM 테이블의 영화 분류별 상영시간이 가장 긴 영화의 모든 상영시간 보다 크거나 같아야만 조건 성립, 영화 분류별 상영시간이 가장 긴 상영시간을 구합니다. SELECT TITLE, LENGTH FROM FILM WHERE LENGTH >= ALL ( SELECT MAX(LENGTH) FROM FILM A, FILM_CATEGORY B WHERE A.FILM_ID = B.FILM_ID GROUP BY B.CATEGORY_ID ) 상영시간이 가장 긴 영화는 185분 인것을 알 수 있습니다. ALL이 없으면 ERROR가 납니다. - FILM 테이블의 평가 기준..

ANY 연산자란? 값을 서브 쿼리에 의해 반환된 값 집합과 비교한다. ANY 연산자는 서브쿼리의 값이 어떠한 값이라도 만족을 하면 조건이 성립됩니다. 실습 - FILM 테이블의 영화 분류별 상영시간이 가장 긴 영화의 제목 및 상영시간을 조회합니다. 상영시간이 가장 긴 상영 시간을 구합니다. SELECT TITLE, LENGTH FROM FILM WHERE LENGTH >= ANY ( SELECT MAX(LENGTH) FROM FILM A, FILM_CATEGORY B WHERE A.FILM_ID = B.FILM_ID GROUP BY B.CATEGORY_ID ) 만약 ANY 연산자가 없다면 에러를 발생합니다. SELECT TITLE, LENGTH FROM FILM WHERE LENGTH >= ( SELEC..

서브쿼리 란? 서브쿼리는 SQL문 내에서 메인 쿼리가 아닌 하위에 존재하는 쿼리를 말하며, 서브쿼리를 활용함으로써 다양한 결과를 도출 할 수 있다. 실습 - FILM 테이블의 RENTAL_RATE 의 평균값을 구하고, 계산된 평균값 보다 큰 RENTAL_RATE 의 값을 조회합니다. 1. FILM 테이블의 RENTAL_RATE컬럼의 평균값을 구하기. SELECT AVG(RENTAL_RATE) FROM FILM; 2. RENTAL_RATE의 평균 보다 큰 RENTAL_RATE 구하기 SELECT FILM_ID, TITLE, RENTAL_RATE FROM FILM WHERE RENTAL_RATE > 2.98; 3. 2개의 SQL을 결합하여 하나의 SQL문으로 두번째의 결과값과 같은 결과를 조회합니다. 중첩 ..

EXCEPT 연산 이란? 맨위에 SELECT문의 결과 집합에서 그 아래에 있는 SELECT문의 결과 집합을 제외한 결과를 반환합니다. LEFT ONLY 기본문법 두개의 SELECT문 간 컬럼의 개수는 동일해야 하고 해당 순서의 열에는 서로 호환되는 데이터 유형 이어야 합니다. SELECT COLUMN_1_1, COLUMN_1_2 FROM TABLE_NAME_1 EXCEPT SELECT COLUMN_2_1, COLUMN_2_2 FROM TABLE_NAME_2 ORDER BY ... 실습 - INVENTORY 테이블과 FILM테이블을 FILM_ID 컬럼 기준으로 INNER JOIN 합니다. SELECT DISTINCT INVENTORY.FILM_ID, TITLE FROM INVENTORY INNER JOIN..

INTERSECT 연산 이란? 두 개 이상의 SELECT 문들의 결과 집합을 하나의 결과 집합으로 결합 합니다. INTERSECT 연산자에 의해 조회된 결과는 A와B의 교집합의 값을 반환 합니다. 기본문법 두개의 SELECT문 간 컬럼의 개수는 동일해야 하며 해당 순서의 열에는 서로 호환되는 데이터 유형 이어야 합니다. ORDER BY로 정렬하고자 할 경우 맨 마지막 SELECT문에 ORDER BY절을 사용합니다. SELECT COLUMN_1_1, COLUMN_1_2 FROM TABLE_NAME_1 INTERSECT SELECT COLUMN_2_1, COLUMN_2_2 FROM TABLE_NAME_2 실습 - KEYS 테이블의 EMPLOYEE_ID컬럼 기준 값과 HIPOS 테이블의 EMPLOYEE_ID컬..

UNION 이란? 두 개 이상의 SELECT 문들의 결과 집합을 단일 결과 집합으로 결합하며 결합 시 중복된 데이터는 제거된다. 기본 문법 두 개의 SELECT문 간 컬럼의 개수는 동일해야 하며 해당 순서의 열에는 서로 호환되는 데이터 유형 이어야 합니다. 두 개의 SELECT문에서 중복되는 데이터 값이 있다면 중복은 제거된다. ORDER BY로 정렬하고자 할 경우 맨 마지막 SELECT문에 ORDER BY 절을 사용한다. SELECT COLUMN_1_1, COLUMN_1_2 FROM TABLE_NAME_1 UNION SELECT COLUMN_2_1, COLUMN_2_2 FROM TABLE_NAME_2; 실습 - SALES2007_1 , SALES2007_2 UNION 연산 조회 SELECT * FROM..