[MySQL] 서브쿼리(Sub Query) - 단일행, 다중행, 스칼라, 인라인 뷰

서브쿼리는 SQL문 안에 들어가는 또 다른 SELECT문이다.

메인쿼리에 종속되어 있어 메인쿼리 없이 단독적으로 실행될 수 없으며

서브쿼리는 메인쿼리의 컬럼을 사용할수는 있지만

반대로 메인쿼리가 서브쿼리의 컬럼을 사용할수는 없다.

-- 서브쿼리 구조
select ... from where 컬럼 = (select ...);

---

서브쿼리 실행 순서

안쪽 SELECT 실행 > 컬럼에 저장 > 바깥쪽 SELECT 실행

---

1) 중첩

1_1) 단일 행 서브쿼리

결과가 하나의 값만 나오는 서브쿼리방식이다. (1행 1열)

단일행 비교 연산자와 함께 사용한다. ( =. >, <, >=, <=, <>)

서브쿼리 결과가 2건 이상 반활될경우 에러가 발생하기 때문에

보통은 MAX(), MIN(), AVG()와 같은 그룹함수와 자주 사용된다. (값이 하나만 출력되는!)

 

예제) 포인트가 가장 높은 회원정보 검색

select * from MyTest where point = (select max(point) from MyTest);

실행결과 ▶

---

1_2) 다중 행 서브쿼리

결과가 여러개의 값이 나오는 서브쿼리 방식이다.

단일 행 연산자(=, >)를 쓰면 에러가 발생하기 때문에

다중행 연산자(IN, ANY, ALL)와 함께 사용하여 결과를 검색한다.

 

예제) 포인트가 150이상인 userid 목록에 포함된 회원 정보 검색

select * from MyTest where userid in (select userid from MyTest where point >= 150);

실행결과 ▶

---

2) 인라인 뷰 - FROM절 서브쿼리

서브쿼리의 결과를 임시 테이블처럼 사용하는 방식이다.

SQL문이 실행되는 동안만 임시로 생성되며,

메인 쿼리에선 서브쿼리에서 만든 결과를 하나의 테이블로 사용가능하다.

※ 인라인 뷰를 사용할때는 반드시 별칭으로 테이블명을 지정해주어야한다.

 

예제) 포인트가 150이상인 회원들의 이름과 포인트를 서브쿼리 필터링 후, 임시테이블에서 데이터 조회

select name, point from (select name, point from MyTest where point >= 150) as high_point_member;

실행결과 ▶

---

3) 스칼라 - SELECT절 서브쿼리

조회 결과에 서브쿼리의 결과를 함께 보여주는 방식이다.

출력되는 데이터의 모든 행마다 서브쿼리가 실행되기 때문에

데이터양이 너무 많을 경우 속도가 느려질 수 있다.

 

예제) 각 회원의 포인트와 전체 평균 포인트를 함께 검색

select name, point, (select avg(point) from MyTest) as avg_point from MyTest;

실행결과 ▶

---

예제

예제문제를 위한 테이블구조 생성

-- MyTest 테이블 생성
create table MyTest (
	idx int auto_increment primary key, -- 회원가입 시 자동증가되는 숫자부여, PK
    userid varchar(20) unique not null, -- 영문 20글자, id는 유니크해야하고, null 안됨
    userpw varchar(20) not null, 
    name varchar(20) not null,
    hp varchar(20) not null,
    email varchar(50) not null,
    ssn1 char(6) not null,	-- 주민번호 앞자리
    ssn2 char(7) not null,	-- 주민번호 뒷자리
    zipcode varchar(5), -- 우편번호
    address1 varchar(100),
    address2 varchar(100),
    address3 varchar(100),
    regdate datetime default now(), 	-- 현재시간을 기본값으로 지정
    point int default 1000,
    gender varchar(10) not null
);

-- MyTest 테이블 데이터 추가
insert into MyTest (userid, userpw, name, hp, email, ssn1, ssn2, zipcode, address1, address2, address3, point, gender) 
values ('apple', '1111', '김사과', '010-1111-1111', 'apple@apple.com', '001011', '4011111', '12345', '서울 서초구', '양재동', '111-11', '150', '여자');

insert into MyTest (userid, userpw, name, hp, email, ssn1, ssn2, zipcode, address1, address2, address3, point, gender) 
values ('banana', '2222', '반하나', '010-2222-2222', 'banana@banana.fruit', '001011', '4011111', '12345', '부산 해운대구', '양재동', '111-11', '200', '여자');

insert into MyTest (userid, userpw, name, hp, email, ssn1, ssn2, zipcode, address1, address2, address3, point, gender) 
values ('orange', '3333', '오렌지', '010-3333-3333', 'orange@orange.jeju', '001011', '4011111', '12345', '경기도 하남시', '양재동', '111-11', '100', '남자');

insert into MyTest (userid, userpw, name, hp, email, ssn1, ssn2, zipcode, address1, address2, address3, point, gender) 
values ('melon', '4444', '이메론', '010-4444-4444', 'melon@melon.com', '001011', '4011111', '12345', '서울 강남구', '양재동', '111-11', '250', '남자');

insert into MyTest (userid, userpw, name, hp, email, ssn1, ssn2, zipcode, address1, address2, address3, point, gender) 
values ('cherry', '5555', '채리', '010-5555-5555', 'cherry@cherry.com', '001011', '4011111', '12345', '부산 중구', '양재동', '111-11', '150', '여자');

insert into MyTest (userid, userpw, name, hp, email, ssn1, ssn2, zipcode, address1, address2, address3, point, gender, regdate) 
values ('berry', '6666', '배애리', '010-6666-6666', 'berry@berry.fruit', '001011', '4011111', '12345', '대구 북구', '양재동', '111-11', '100', '여자', null);

-- orders 테이블 생성
create table orders (
    order_id int auto_increment primary key,
    member_idx int not null,
    product_name varchar(100) not null,
    price int not null,
    order_date datetime default now(),
    foreign key (member_idx) references member(idx)
);

-- orders 테이블 데이터 추가
insert into orders(member_idx, product_name, price)
values
(1, '키보드', 50000),
(1, '마우스', 30000),
(2, '모니터', 250000),
(2, '노트북 거치대', 40000),
(3, 'USB', 15000),
(4, '노트북', 1200000),
(4, '헤드셋', 90000);

---

예제1 ) 주문한 적이 있는 회원들만 조회

-- 주문한적 있는 회원
select member_idx from orders;

-- 해당 회원들 조회
select * from MyTest where idx in (select member_idx from orders);

실행결과 ▶

---

 

 예제2 )  주문한 적이 없는 회원들만 조회

select * from MyTest where idx not in (select member_idx from orders);

실행결과 ▶

---

예제3 )  가장 비싼 상품을 주문한 회원 조회

-- 가장 비싼 상품의 가격
select max(price) from orders;

-- 가장 비싼 상품을 산 회원의 번호
select * from orders where price = (select max(price) from orders);	

-- 해당 회원 정보를 조회
select * from MyTest where idx =(select member_idx from orders where price = (select max(price) from orders));

실행결과 ▶

---

예제4 )  가장 비싼 상품을 주문한 회원의 이름과 상품명을 조회

-- 가장 비싼 상품
select max(price) from orders;

-- 두 테이블을 join(이름과 제품명을 가져와야하기 때문)
select m.name, o.product_name, o.price from MyTest as m 
join orders as o 
on m.idx = o.member_idx;

-- 최종 : where로 join한 테이블에서 가장 비싼 상품 검색
select m.name, o.product_name, o.price from MyTest as m 
join orders as o 
on m.idx = o.member_idx
where o.price=(select max(price) from orders);

실행결과 ▶