Left Outer Join
select stadium_name,stadium.stadium_id,seat_count,hometeam_id,team_name
from stadium left join team
on stadium.hometeam_id=team.team_id
order by stadium.hometeam_id;
Right outer Join
select *
from emp right join dept
on emp.deptno=dept.deptno
Union / 일반 합집합, 전남,GK, 서울, 65
select region_name, player_name, position, weight
from team natural join player
where region_name='전남' and position ='GK'
UNION
select region_name, player_name, position, weight
from team natural join player
where region_name='서울' and weight>=65;
일반
select region_name, player_name, position, weight
from team natural join player
where (region_name='전남' and position ='GK')
or (region_name='서울' and weight>=65)
order by player_name,position;
minus / 일반 차집합, 전남,GK,
select region_name, player_name, position, weight
from team natural join player
where region_name='전남'
minus
select region_name, player_name, position, weight
from team natural join player
where position = 'GK'
order by player_name,position
일반
select region_name, player_name, position, weight
from team natural join player
where region_name='전남' and not position ='GK'
intersect / 교집합, 전남,GK
select t.region_name, p.player_name, p.position,p.weight
from team t join player p on (t.team_id = p.team_id)
where t.region_name = '전남'
intersect
select t.region_name, p.player_name, p.position,p.weight
from team t join player p on (t.team_id = p.team_id)
where p.position<>'GK'
다중컬럼 서브쿼리
select team_name, player_name, position, weight
from player natural join team
where (team_name,weight) in
(
select team_name,max(weight)
from player natural join team
group by team_name
)
