가볍게 배우고 깊게 즐기고 오래 남기기

SQL || JOIN ON과 LIKE문을 함께 사용할 때의 차이 본문

Programming & Tip/Google Bigquery

SQL || JOIN ON과 LIKE문을 함께 사용할 때의 차이

Awesomist 2020. 10. 7. 08:40
728x90

 

with
Employee_master as(
  select 001 No ,'우영우' nm,'F' gender union all
  select 002 No ,'한바다' nm,'F' gender union all
  select 003 No ,'김길동' nm,'M' gender union all
  select 004 No ,'이길동' nm,'F' gender union all
  select 005 No ,'홍길동' nm,'M' gender union all
  select 006 No ,'이삼사' nm,'M' gender union all
  select 007 No ,'이육사' nm,'M' gender union all),
Manager_master as(     
  select 001 No ,'우영우' nm,'N' MNG union all
  select 002 No ,'한바다' nm,'Y' MNG union all
  select 004 No ,'김길동' nm,'Y' MNG union all
  select 006 No ,'이길동' nm,'N' MNG union all
  select 007 No ,'홍길동' nm,'N' MNG union all
  select 008 No ,'이삼사' nm,'N' MNG union all
  select 007 No ,'이육사' nm,'Y' MNG union all)

 

select *
  from Employee_master a   left join Manager_master b on a.No = b.No and a.MNG like 'Y'

with
Employee_master as(
  select 001 No ,'우영우' nm,'F' gender union all
  select 002 No ,'한바다' nm,'F' gender union all
  select 003 No ,'김길동' nm,'M' gender union all
  select 004 No ,'이길동' nm,'F' gender union all
  select 005 No ,'홍길동' nm,'M' gender union all
  select 006 No ,'이삼사' nm,'M' gender union all
  select 007 No ,'이육사' nm,'M' gender),
Manager_master as(     
  select 001 No ,'우영우' nm,'N' MNG union all
  select 002 No ,'한바다' nm,'Y' MNG union all
  select 004 No ,'김길동' nm,'Y' MNG union all
  select 006 No ,'이길동' nm,'N' MNG union all
  select 007 No ,'홍길동' nm,'N' MNG union all
  select 008 No ,'이삼사' nm,'N' MNG union all
  select 007 No ,'이육사' nm,'Y' MNG)

select *
from Employee_master a  left  join Manager_master b on  a.No = b.No
where b.MNG like 'Y' 
order by 1

 

반응형
저작자표시 비영리 동일조건 (새창열림)
Comments