[MySQL] UNION, JOIN, SUB QUERY

이번 장을 진행하기전에 기존의 sale table을 삭제하고 

아래 새로운 테이블을 작성한다.

 

create table sale(
    number varchar(10),
    sales int,
    month int
);
 
create table employ(
    number varchar(10),
    name varchar(10),
    age int
);
 
create table another_employ(
    number varchar(10),
    name varchar(10),
    age int
);
 
create table loc(
    number varchar(10),
    city varchar(5)
);

 

insert into sale values('A103', 101, 4);
insert into sale values('A102', 54, 5);
insert into sale values('A104', 181, 4);
insert into sale values('A101', 184, 4);
insert into sale values('A103', 17, 5);
insert into sale values('A101', 300, 5);
insert into sale values('A102', 205, 6);
insert into sale values('A104', 93, 5);
insert into sale values('A103', 12, 6);
insert into sale values('A107', 87, 6);
 
 
insert into employ values('A101', '강신우', 49);
insert into employ values('A102', '김기덕', 28);
insert into employ values('A103', '김민호', 20);
insert into employ values('A104', '문소리', 23);
insert into employ values('A105', '박문수', 35);
 
insert into another_employ values('A106', '권명철', 26);
insert into another_employ values('A107', '김우진', 24);
insert into another_employ values('A108', '남수현', 23);
insert into another_employ values('A109', '박지수', 25);
insert into another_employ values('A110', '서연재', 27);
 
 
insert into loc values('A101', '서울');
insert into loc values('A102', '부산');
insert into loc values('A103', '대구');
insert into loc values('A104', '대전');
insert into loc values('A105', '인천');
 

 여러 개의 추출 결과를 함께 표시(UNION)

 

select column_name1 FROM table_name UNION SELECT column_name2 FROM table_name_1;
 
SELECT * FROM employ 
UNION
SELECT * FROM another_employ;

 

 여러 개의 테이블 결합(Join)

SELECT column_name
FROM table_name
JOIN joined_table_name
ON table_name.column = joined_table_name.column
 
 
select *
from sale
JOIN employ
ON sale.number = employ.number;

 

  

 내부 조인(inner-Join)

select * from sale INNER JOIN employ ON sale.number = employ.number;
 

 

 

 결합한 테이블에 WHERE 조건 설정

 
select sale.number AS 사원번호, employ.name AS 이름, sale.sales AS 매출
from sale
join employ USING(number)
WHERE sale.sales >= 100

 

 

 여러 테이블에서 데이터 추출

 
select sale.number, sale.sales, employ.name, loc.city
from sale
join employ using (number)
join loc using (number)

 

 

 하위 질의 

select * from sale 
where sales in (select max(sales) from sale);

 

 

 평균 이상의 레코드 추출 - 하위 질의 

select * from employ where age >= (select avg(age) from employ)