[mysql] join,Aliases

조인(join)

create database studydb;

use studydb;

create table dept
(
no int primary key auto_increment,
name varchar(20)
);

create table employee
(
no int primary key auto_increment,
name varchar(20),
dept_no int
);

insert into dept(name) values('A');
insert into dept(name) values('B');
insert into dept(name) values('C');
insert into employee(name,dept_no) values('ga',2);
insert into employee(name,dept_no) values('na',3);
insert into employee(name,dept_no) values('da',3);
insert into employee(name,dept_no) values('ba',2);
insert into employee(name,dept_no) values('ma',2);
insert into employee(name,dept_no) values('sa',null);

1. EQUI JOIN = INNER JOIN - 동등조인

 

select employee.no, employee.name, dept.name from employee, dept;
-- 3행 x 5행 = 15행

select employee.no, employee.name , dept.name from dept, employee where employee.dept_no = dept.no;
-- 사원번호와 부서번호를 join, 이 값이 동등한 행을 하나로 엮어준다.

---

+ Aliases

select e.no, e.name , d.name from dept d, employee e where e.dept_no = d.no;

---

2. LEFT (OUTER) JOIN

 

select e.no, e.name , d.name from dept d left join employee e where e.dept_no = d.no;

3. RIGHT (OUTER) JOIN

select e.no, e.name , d.name from dept d right join employee e where e.dept_no = d.no;

4. FULL OUTER JOIN

select employee.no, employee.name, dept.name from employee left join dept on dept.no = employee.dept_no
union
select employee.no, employee.name, dept.name from employee right join dept on dept.no = employee.dept_no;

MySQL은 FULL OUTER JOIN을 지원하지 않는다.

대신 같은 조건으로

left join과 right join을 하고 union을 하면 된다.

​

https://futurists.tistory.com/18