인공지능/데이터분석

고급 SQL 기법 활용하기: UNION, SubQuery, View

hyunji00pj 2024. 10. 22. 05:41

데이터베이스 관리에서 다양한 고급 기법을 이해하고 적용하는 것은 매우 중요하다. 복잡한 데이터 요구 사항에 효율적으로 대응하기 위해 union, subQuery, view를 사용하는 방법을 살펴보자.

 

1. UNION과 UNION ALL

UNION과 UNION ALL 명령은 서로 다른 쿼리 결과를 하나로 합치는 데 사용된다. 이들의 주된 차이는 UNION이 중복된 결과를 제거하는 반면, UNION ALL은 모든 결과를 포함한다는 점이다.

 

union의 기본 구조

    - 합집합을 나타내는 연산자로 중복된 값을 제거함
    - 서로 같은 종류의 테이블(컬럼이 같아야 함)에서만 적용 가능
    select 컬럼1,컬럼2,... from 테이블1 union select 컬럼1,컬럼2,...from 테이블2

 

예제를 통한 설명을 위해 아래 두 테이블을 추가했다

create table product(
	code varchar(5) not null,
    name varchar(20) not null,
    detail varchar(1000),
    price int default 0,
    regdate datetime default now()
);

insert into product values('00001','맥북에어','가벼워요',1500000,now());
insert into product values('00002','모니터','잘보여요',400000,now());
insert into product values('00003','지갑','잘담겨요',100000,now());
insert into product values('00004','아이패드','글쎄요',700000,now());
insert into product values('00005','그래픽카드','잘돌아요',500000,now());
select * from product;

create table product_new(
	code varchar(5) not null,
    name varchar(20) not null,
    detail varchar(1000),
    price int default 0,
    regdate datetime default now()
);

insert into product_new values('10001','맥북프로','무거워요',2500000,now());
insert into product_new values('10002','동전지갑','이것도 잘 담겨요',400000,now());
insert into product_new values('10003','LG그램','글쎄요',100000,now());
select * from product_new;

product 테이블

 

product_new테이블

 

#두 개의 테이블의 데이터를 모두 보여줌
select code, name, price from product union select code, name, price from product_new;

UNION 은 중복된 데이터를 제거하고 보여줍니다.

#product에 있는 데이터를 product_new에 동일하게 삽입
insert into product_new values('00003','지갑','잘담겨요',100000,now());
select * from product_new;

#union은 중복 데이터를 제거해서 보여줌
select code, name, price from product union select code, name, price from product_new;

아래 처럼 필드 하나라도 데이터가 다르다면 중복데이터로 치지 않고 표시된다는 점 주의 

#날짜/시간이 다르기 때문에 중복데이터가 아님!!
select code, name, price,regdate from product union select code, name, price,regdate from product_new;

 

UNION ALL은 중복된 데이터를 포함하여 모든 결과를 보여줍니다.

#union all은 중복 데이터를 제거하지 않고 모두 보여줌
select code, name, price from product union all select code, name, price from product_new;

 

2. 서브쿼리(SubQuery)

서브쿼리는 한 SQL 질의 내부에 포함된 다른 SQL 질의로, 데이터 처리를 더 유연하게 만든다.

 

subQuery

    - 다른 쿼리 내부에 포함되어 있는 select문을 의미
    - 서브쿼리를 포함하고 있는 쿼리를 외부쿼리라고 부르고, 서브쿼리는 내부 쿼리라고도 부름
    - 서브쿼리는 괄호()를 사용해서 표현
    - select,where, from, having절 등에서 사용할 수 있음

select * from product;
#상품코드가 '00005'의 가격보다 크거나 같은 가격을 가지고 있는 상품의 정보를 모두 출력
select * from product where price >= (select price from product where code='00005');

product 테이블 전체 데이터

위의 product 테이블에서 서브쿼리를 사용해 상품코드가 '00005'의 가격보다 크거나 같은 가격을 가지고 있는 상품의 정보를 모두 출력해 보았다 위 보기 처럼 서브쿼리는 복잡한 쿼리를 단순화 하고  자주 사용하는 데이터 조회를 간편화 할 수 있다.

#상품코드가 '00005'의 가격보다 크거나 같은 가격을 가지고 있는 상품의 정보를 모두 출력한 것

 

서브쿼리는 처음 쓰기에 조금 헷갈릴 수 있기에 몇가지 예시를 더 준비해 보았다

#product 테이블에서 모든 상품의 code,name,price,'가장 비싼 상품의 가격'을 출력
/*
	00001 맥북에어 1500000 1500000
	00002 모니터 400000 1500000
    ...
*/
select code,name,price,(select max(price) from product) as '가장 비싼 상품의 가격' from product;

이 쿼리는 product 테이블에서 모든 상품의 코드(code), 이름(name), 가격(price)을 선택하고, 전체 상품 중에서 가장 비싼 상품의 가격을 모든 행에 같은 값으로 표시한다.

 

 

데이터베이스에서 지금 까지 배운걸 활용해서 서브쿼리를 사용하기 위해 데이블을 두개 더 만들어 보겠다

AUTO_INCREMENT 속성은 번호를 자동으로 할당한다

create table orders(
	no int auto_increment primary key,
    userid varchar(20) not null,
    product_code varchar(5) not null,
    product_cnt int default 1,
    regdate datetime default now(),
    foreign key(userid) references member(userid)
);
insert into orders values (1, 'apple', '00005',1,now());
insert into orders values (5, 'banana', '00003',1,now());
#auto_increment는 직접 넣을수도 있고 안넣으면 마지막번호에 +1되어서 들어감
insert into orders (userid,product_code,product_cnt) values ('apple','00002',1);
#insert into orders values (5, 'orange', '00002',1,now());#중복된 키
insert into orders (userid,product_cnt) values ('apple',1);
insert into orders (userid,product_code,product_cnt) values ('apple','00004',1);
select * from orders;
delete from orders where no = 7;
select * from orders;
#7이 삭제 되더라도 다시 7로 들어가지 않고 8로 들어감
insert into orders (userid,product_code,product_cnt) values ('apple','00004',1);
insert into orders (userid,product_code,product_cnt) values ('orange','00001',1);
select * from orders;

AUTO_INCREMENT 활용 테이블

 

 

아래 쿼리에선 product 테이블에 기본키를 추가하고, orders 테이블에 설정된 외래키의 동작을 변경하여 상품 코드가 삭제되거나 변경될 때 관련된 orders  데이터도 영향을 받도록 설정한다.

 

마지막 줄에서 서브쿼리를 사용해 특정 사용자가 두 번 이상 주문한 경우, 해당 사용자의 ID, 이름, 성별을 조회한다.

# product 테이블에 기본키 추가하기
alter table product add primary key(code);
# orders 테이블에 외래키 추가하기
alter table orders add foreign key(product_code) references product(code) on delete cascade on update cascade;
#기본키에 있는 값이 삭제되거나 변경되어도 따라서 같이 삭제되거나 변경되게 하기 위해 on update cascade on update cascade사용

#특정 상품을 최소 두번이상 구입한 횟수가 있는 회원의 아이디와 이름, 성별을 출력
select userid,name,gender from member where userid in (select  userid from orders group by userid having count(no) >=2);

특정 상품을 최소 두번이상 구입한 횟수가 있는 회원의 아이디와 이름, 성별

 

create table orders_new(
	no int auto_increment primary key,
    userid varchar(20) not null,
    product_code varchar(5) not null,
    product_cnt int default 1,
    regdate datetime default now(),
    foreign key(userid) references member(userid),
    foreign key(product_code) references product(code)
);
insert into orders_new(select * from orders);
select * from orders_new;

create table orders_new_new(select * from orders);
select * from orders_new_new;

위 서브쿼리를 이용한 쿼리문으로 orders 테이블의 모든 데이터를 새로운 테이블인 orders_new와 orders_new_new로 복사한다. 이 과정은 기존 데이터를 새로운 테이블 구조로 이전할 때 사용된다.

 

위와같이 서브쿼리를 사용하면 SQL문을 더 다양하고 간편히 사용할 수 있다

 

3. 뷰(View)

뷰(View)는 데이터베이스의 하나 이상의 테이블에서 파생된 가상 테이블이다. 이 가상 테이블은 실제 데이터를 저장하지 않고, 정의된 SQL 쿼리에 따라 데이터를 동적으로 표시한다. 뷰는 복잡한 쿼리를 단순화하고, 데이터 접근을 제어하는데 도움을 준다. 또한, 사용자에게 필요한 데이터만을 제한적으로 보여주어 보안성을 강화할 수 있다.

 

뷰의 생성 및 조회

뷰(view) 생성하기
create view 뷰이름 as 쿼리

 

뷰를 생성하는 방법은 create view문을 사용한다. 예를 들어, member 테이블과 profile 테이블을 조인하여 사용자 정보를 보여주는 뷰를 만들 수 있다.

create view vw_userinfo as select m.userid, m.name, m.gender, p.mbti from member as m inner join profile as p on m.userid = p.userid;
select * from vw_userinfo;
insert into vw_userinfo values ('grapes','표도','남자','INFP');

생성된 뷰는 아래와 같이 일반 테이블처럼 조회할 수 있다.

create view

뷰는 가상의 테이블이기 때문에 위의 3번째 쿼리문처럼 insert는 할 수 없다

뷰는 가상의 테이블이기 때문에 insert 안돼서 뜬 에러 메시지

 

뷰(view) 변경하기
alter view 뷰이름 as 쿼리

 

기존 뷰의 정의를 변경하려면 alter view 문을 사용한다. 예를 들어, vw_userinfo 뷰에 사용자의 키(height)를 추가하고 싶다면 다음과 같이 수정할 수 있다.

alter view vw_userinfo as select m.userid, m.name, m.gender, p.height,p.mbti from member as m inner join profile as p on m.userid = p.userid;
select * from vw_userinfo;

변경한 뷰

 

뷰(view) 삭제하기

drop view 뷰이름

 

뷰는 drop view 문을 사용하여 삭제할 수 있다. 뷰를 더 이상 사용하지 않을 때는 다음과 같이 삭제한다.

drop view vw_userinfo;

 

뷰의 제한 사항

  • 성능 문제: 뷰는 매번 조회할 때마다 원본 테이블에서 데이터를 가져오기 때문에, 큰 데이터셋이나 복잡한 쿼리를 사용하는 경우 성능 저하가 발생할 수 있다.
  • 데이터 변경 제한: 뷰에는 데이터를 직접 삽입하거나 수정할 수 없는 경우가 많으며, 특히 복잡한 쿼리를 사용한 뷰는 데이터 변경이 제한된다.

뷰는 데이터베이스 관리와 데이터 보안을 향상시킬 수 있다. 적절하게 사용하면 데이터베이스 쿼리의 복잡성을 줄이고, 사용자에게 필요한 데이터만을 제공하여 시스템의 보안을 강화할 수 있다.