필요한 데이타베이스 만들기

create database sqljoins;

 

커스터머 데이타

create table sqljoins.customer 
(
    CustomerID int,
    CityID int,
    CustomerName varchar(50)
);

insert into sqljoins.customer (CustomerID, CityID, CustomerName) values (1, 1, 'Bob Smith');
insert into sqljoins.customer (CustomerID, CityID, CustomerName) values (2, 1, 'Sally Smith');
insert into sqljoins.customer (CustomerID, CityID, CustomerName) values (3, 2, 'Tom Smith');
insert into sqljoins.customer (CustomerID, CityID, CustomerName) values (4, NULL, 'Mary Smith');

 

시티 데이타

create table sqljoins.city
(
    CityID int,
    CityName varchar(50)
);

insert into sqljoins.city (CityID, CityName) values (1, 'Kansas City');
insert into sqljoins.city (CityID, CityName) values (2, 'New York');
insert into sqljoins.city (CityID, CityName) values (3, 'Houston');

 

제대로 들어가있는지 체크

select * from sqljoins.Customer;
select * from sqljoins.City;

 

inner조인

select * from sqljoins.customer
inner join sqljoins.city on customer.cityid=city.cityid;

 

레프트 조인

select * from sqljoins.customer
left join sqljoins.city on customer.cityid=city.cityid;

 

라이트 조인

select * from sqljoins.customer
right join sqljoins.city on customer.cityid=city.cityid;

 

위와 같은 내용 즉 레프트 조인만 잘 알면 됨.

select * from sqljoins.city
left join sqljoins.customer on customer.cityid=city.cityid;

 

풀 조인은 mysql에 명령어가 없습니다.  아래와 같이 union으로 처리한다고 합니다.

select * from sqljoins.customer
left join sqljoins.city on customer.cityid=city.cityid
union
select * from sqljoins.customer
right join sqljoins.city on customer.cityid=city.cityid;

 

https://youtu.be/2HVMiPPuPIM?list=PLD20298E653A970F8