Department에 대한 테이블 작성을 합니다.

 

create table simpledb.department
(
    department_number int primary key,
    department_name varchar(50),
    location varchar(50)
);

insert into simpledb.department (department_number, department_name, location) values (10, 'ACCOUNTING', 'NEW YORK');
insert into simpledb.department (department_number, department_name, location) values (20, 'RESEARCH', 'DALLAS');
insert into simpledb.department (department_number, department_name, location) values (30, 'SALES', 'CHICAGO');
insert into simpledb.department (department_number, department_name, location) values (40, 'OPERATIONS', 'BOSTO');

 

SQL Inner Join

select * from simpledb.department;

select *
from simpledb.employee
inner join simpledb.department
on  employee.department_number = department.department_number;

select department.department_name, employee.*
from simpledb.employee
inner join simpledb.department
on  employee.department_number = department.department_number;

select d.department_name, e.*
from simpledb.employee as e
inner join simpledb.department as d
on  e.department_number = d.department_number;

select d.department_name, e.*
from simpledb.employee as e
inner join simpledb.department as d
on  e.department_number = d.department_number
where d.department_name='Sales';

https://www.youtube.com/watch?v=yMOmuFiw__Y&list=PLD20298E653A970F8&index=22

SQL Outer Join

join만 쓰면 inner join과 같네요.

select * from simpledb.customer
join simpledb.employee
on customer.saleman_employee_number=employee.employee_number;

select * from simpledb.customer
left outer join simpledb.employee
on customer.saleman_employee_number=employee.employee_number;

https://www.youtube.com/watch?v=ge9aXvDhjnw&list=PLD20298E653A970F8&index=23

SQL Right Outer Join

select * from simpledb.employee
inner join simpledb.customer
on employee.employee_number=customer.saleman_employee_number;

select * from simpledb.employee
right outer join simpledb.customer
on employee.employee_number=customer.saleman_employee_number;

select * from simpledb.customer
left outer join simpledb.employee
on customer.saleman_employee_number=employee.employee_number;

https://www.youtube.com/watch?v=DiKNwdnX9Io&list=PLD20298E653A970F8&index=24

SQL Left Outer Join

select * from simpledb.customer
inner join simpledb.employee
on customer.saleman_employee_number=employee.employee_number;

select * from simpledb.customer
left outer join simpledb.employee
on customer.saleman_employee_number=employee.employee_number;

https://www.youtube.com/watch?v=BuVd9_UQyrQ&list=PLD20298E653A970F8&index=25