MySQL - 05

join

RDBMS에서 여러 테이블에 흩어져 있는 정보 중 사용자가 필요한 정보만 가져와서 가상의 결과 테이블을 만들고 결과를 보여주는 기술

from 테이블1 join 테이블2 on 조인 조건식

join 예제

owner 테이블과 car테이블을 만들어 foreign key를 만들어 종속관계로 만든다.
부모테이블은 owner, 자식테이블은 car테이블로 만들 것이다.

create table owner(
	id varchar(300) primary key,
    name varchar(300),
    phone varchar(300),
    age int, 
    addr varchar(300)
);
create table car(
	carnum varchar(300) primary key,
    brand varchar(300),
    color varchar(300),
    price int, 
    id varchar(300),
    constraint car_owner_fk foreign key(id) reference owner(id)
);

각 테이블에 데이터를 알맞는 형식으로 넣는다.

insert into owner values('apple', '김사과', '01012341234', 20, '서울시 송파구 잠실동');
insert into owner values('banana', '반하나', '01011111111', 65, '서울시 강남구');
insert into owner values('cherry', '이체리', '01022222222', 35, '서울시 은평구');

insert into car values('135다7961', 'K8', 'White', '4000', 'apple');
insert into car values('246다2468', 'Porsche', 'Yellow', '18000', 'apple');
insert into car values('99구9999', 'Avante', 'White', 1800, 'cherry');
insert into car values('22이2222', 'GV80', 'Blue', 8600, 'banana');
insert into car values('11가1111', 'Bentley', 'White', 30000, 'banana');

각각의 테이블에 데이터가 담겨있다. 우리가 원하는 데이터는 회원의 이름과 전화번호 회원의 자동차 번호, 그 자동차의 브랜드, 가격 정보를 원한다.
이때 서로 다른 테이블의 정보를 합성시켜 하나의 테이블 정보로 나타내는 것이 join이다.

select name, phone, carnum, brand, price from owner join car;

위와 같이 표현을 하게되면 오류는 발생하지 않지만 명시적으로 어떤 데이터가 어떤 테이블에서 존재하는지 작성하지 않으면 가독성과 데이터 추적이 어려워 되도록 데이터의 테이블을 명시한다.
데이터 테이블을 명시하는 방법은 다음과 같이 테이블명을 그대로 사용가능하지만 쿼리문의 길이가 길어짐으로 별칭을 사용할 수 있다.

select o.name, o.phone, c.carnum, c.brand, c.price from owner o join car c;

이제 출력해보면 15개의 행이 출력되는 것을 볼 수 있다. 이는 join문의 조건식 달지않아 가능한 경우의 수를 모두 보여주기 때문에 발생하는 에러이다.
우리는 이 에러를 피하기위해 조건식을 붙여서 원하는 정보를 만들어낸다.
원하는 정보는 회원 이름과 그 회원의 자동차 정보를 원한다. 따라서 자동차의 id가 회원의 id와 일치하는 정보만 가져와야 한다.

select o.name, o.phone, c.carnum, c.brand, c.price 
from owner o join car c on c.id = o.id;

서브쿼리

SQL문 내부에 SQL문을 선언하는 기법
우리가 원하는 정보가 만약 데이터의 값 자체로 사용하고 싶을 때 즉 테이블에서 결과로 나온 그 값을 쿼리문에 넣고 싶을때나 혹은 테이블 그 자체를 쿼리문에 넣을 때 사용한다.
각각 서브쿼리의 위치에 따라 다음과 같이 부른다.
from : inline view
select : scalar
where : sub query
여기서 select와 where는 어떤 조건으로 사용하기 때문에 값으로 출력되는 쿼리문만 서브쿼리로 넣을 수 있다.

예시

apple이 가지고 있는 차 가격의 평균보다 높은 차의 이름(apple 제외)

# 평균을 구하고 
select avg(price) from car where id = 'apple';
# 이 평균값보다 높은 차를 검색하면 된다.
select brand, id from car where price > 11000 and id != 'apple';

이렇게 작성 할 수 있지만 한번에 작성하는 방법이 있다.
서브쿼리를 적용하면 된다.

select brand, id from car 
where price > (select round(avg(price),2) from car where id = 'apple') 
and id != 'apple';

쿼리문 결과의 테이블에서 검색하고 싶은 경우
apple car테이블에서 가장 가격이 높은 차의 가격과 브랜드

select brand, price from car 
where price = (select max(price) from (select * from car where id = 'apple') r);

테이블 자체를 이용하여 값을 추려내고 그 값을 이용해서 원하는 정보를 출력하고 있다.