sql (join, alias, distinct, limit)

• join : 두 개 이상의 테이블 결합

select table_a.column1, table_b.column2 ...
from table_a
inner join table_b
on table_a.column = table_b.column # 기준
where condition

mysql에서는 full outer join 지원x -> (left join) union (right join)

select a.name, a.age, b.name, b.age
from a
left join b 
on a.name= b.name
union # 중복 제거
select a.name, a.age, b.name, b.age
from a
right join b 
on a.name= b.name
where condition

• self join ( inner join과 같은 결과)

select a.name, a.age, b.name, b.age
from table_a, table_b
where a.name= b.name;

• concat : 여러 문자열 하나로 합치거나 연결

select concat ('concat',' ','test');

select concat('이름: ', name) from celeb;

• alias : 컬럼이나 테이블 이름에 별칭 생성

select concat(name, ' : ', job_title) as profile
-> from celeb;

# 컬럼 별칭 생성
select column1 as a1, column2 as a2
from tablename;

# 테이블 별칭 생성
select column1, column2..
from tablename as alias;

# name과 job_title 합쳐서 profile이라는 별칭 생성
select concat(name, ' : ', job_title) as profile from celeb;

# as 생략 가능
select concat(s.season, '-', s.episode, '(' , s.broadcast_date, ')') as '방송정보',
   -> concat(c.name,'(',c.job_title, ')') as '출연자정보'
   -> from celeb as c, snl_show as s 
   -> where c.name=s.host;
   

• distinct : 중복 제거

select distinct agency from celeb;

• limit : 검색결과를 정렬된 순으로 주어진 숫자만큼만 조회

# 3개만 조회
select * from celeb
    -> limit 3;
    
 # 나이가 가장 적은 연예인 3명 검색
 select * from celeb
 order by age limit 3;