240627_SQL, Subquery, JOIN, INNER JOIN,LEFT JOIN, RIGHT JOIN

강의: [KDC] 엑셀보다 쉽고 빠른 SQL - 4주차

 

 

[문자변경]

1. REPLACE : 지정한 문자를 다른 문자로 변경

2. SUBSTRING : 특정 문자만 추출

3. CONCAT : 여러 문자를 합하여 포맷팅

 

[조건문]

1. IF : if(조건, 주건을 충족할 때, 조건을 충족하지 못할 때)

2. CASE WHEN END : 

    case when 조건1 then 값(수식)1

            when 조건2 then 값(수식)2

            else 값(수식)3

            end

 

 

 

---

[Subquery]

필요한 경우

• 여러번의 연산을 수행해야 할 때
• 조건문에 연산결과를 사용해야 할 떄
• 조건에 Query 결과를 사용하고 싶을 때

 

Subquery문의 기본 구조

select column1, special_column
from
    ( /* subquery */
    select column1, column2 special_column
    from table1
    ) a

• Query 안에 sub로 들어간 구문
• ()안에 SELECT, FROM을 반드시 기재
• 서브쿼리 마지막에는 ;기호 사용 불가

 

실행순서

• 서브커리 실핼(안쪽에 위치한 쿼리) -> 메인쿼리(바깥쪽에 위치한 쿼리) 실핼
• 쿼리의 가장 안쪽부터, 바깥쪽 쿼리를 실행하여 최종 결과값을 반환

종류

• 일반(중첩) 서브쿼리
  • where 절에서 사용
  • 서브쿼리의 결과에 따라 달라지는 조건절

# 중첩 서브쿼리 실습
# 문동은의 나이보다 나이가 많은 모든 데이터 반환하기
select *
from basic.theglory 
where 나이 > (select 나이 from basic.theglory where 이름='문동은')
;

 

• 스칼라 서브쿼리
  • select 절에서 사용
  • 하나의 컬럼처럼 사용
  • 스칼라 서브쿼리 이용을 위해서는 서로 다른 테이블이 필요

# 중첩 서브쿼리 실습
# theglory 의 이름과 theglory2 테이블의 이름이 일치하는 경우를 count 하여 
  #same_name_cnt 컬럼으로 반환
# theglory 의 이름과 theglory2 테이블의 이름이 일치하는 경우의 결제금액을 sum 하여 
  #same_name_sumamount 컬럼으로 반환
select 이름
, 나이
, (select count(*) from theglory2 where theglory2.이름=theglory.이름) as same_name_cnt
, (select sum(결제금액) from theglory2 where theglory2.이름=theglory.이름)as same_name_sumamount
from basic.theglory
;

 

• 인라인 뷰
  • from 절에서 사용
  • 하나의 테이블처럼 사용
  • AS 구문을 사용하여 명칭을 반드시 기재해야함
  • JOIN 및 UNION 시 가장 유용하게 사용.

# 중첩 서브쿼리 실습
# 나이가 33세 이상인 모든 데이터 중 나이와 직업 컬럼 반환하기 
select x.나이, x.직업
from(	select *
		  from basic.theglory
		  where 나이>=33
	  )as x

---

[JOIN]

필요한 경우

• 필요한 데이터를 여러 테이블에서 데이터를 불러올 때

JOIN 함수 이용 방법

1. 공통컬럼 찾기
2. 적절한 조인 방식 찾기

INNER JOIN (가장 많이 사용)	두 테이블에서 일치하는 값을 가진 행을 출력 (교집합)
LEFT JOIN (가장많이사용)	왼쪽 테이블의 모든 행과 오른쪽 테이블의 일치하는 행을 반환합니다. 일치하는 항목이 없으면 오른쪽 테이블의 열에 대해 NULL 값이 출력됩니다.
RIGHT JOIN	오른쪽 테이블의 모든 행과 왼쪽 테이블의 일치하는 행을 반환합니다. 일치하는 항목이 없으면 왼쪽 테이블의 열에 대해 NULL 값이 출력됩니다.
FULL OUTER JOIN	모든 데이터를 보고 싶을 때 사용합니다. 용량 이슈로 자주 사용하지 않아요! MySQL 환경에서는 제공하지 않아 LEFT JOIN 과 RIGHT 조인의 UNION으로 계산해야합니다. (합집합)

 

INNER JOIN

# INNER JOIN 작성법(기초편)
select 컬럼1, 컬럼2... 
from 테이블1 as a
inner join 테이블2 as b
on a.공통컬럼=b.공통컬럼

• INNER JOIN 의 약간은 복잡한 예(subquery 사용)

# INNER JOIN 작성법 (서브쿼리 활용편)
select a.name, a.age, b.name, b.goods_nm
from(   select name, age 
				from s1
		 )as a #첫번째 셀렉트 절의 결과를 'a' 로 명시 
inner join #(또는 join) #테이블1과 테이블2를 inner join 하겠다는 의미
	  (   select name, goods_nm 
			  from s2
		 )as b #두번째 셀렉트 절의 결과를 'b' 로 명시 
on a.name =b.name# 공통된 의미의 컬럼으로, 컬럼이름이 같을필요는 없음
# 공통컬럼은 ON 절의 조건절이 되며, 조건절은 1개가 아닐 수 있음
# 즉, on a.공통컬럼=b.공통컬럼 and a.공통컬럼2=b.공통컬럼2 가 될 수 있음

• 각 테이블에 대해 먼저 필터링을 적용하고, 필요한 열만 선택하여 조인하는 방식.
• 복잡한 데이터 전처리나 특정 열만 선택할 때 유용.
• 서브쿼리를 사용하면 개별 쿼리로 나눠진 구조가 복잡한 경우에 더 읽기 쉬울 수 있음.

LEFT JOIN

• 쿼리에서 ’LEFT JOIN’ 이라는 문구를 기준으로 위쪽(왼쪽)에 작성하는 테이블.
• ==’LEFT에 위치해 있는 테이블’
• ==’LEFT JOIN 에서 기준이 되는 테이블’
• 기준이 되는 테이블은, 조인 조건을 만족해도/만족하지 못해도 모두 출력됩니다.

• 쿼리에서 ’LEFT JOIN’ 이라는 문구를 기준으로 아래쪽(오른쪽)에 작성하는 테이블’
• ==’RIGHT 에 위치해 있는 테이블’
• ==’LEFT JOIN 에서 조건에 따라 출력되는 테이블’.
• LEFT JOIN에서 RIGHT에 위치한 테이블은, 조인 조건을 만족하는 경우 출력되며, 만족하지 못할경우 NULL 값으로 출력됩니다.

# LEFT JOIN 작성법(기초편)
select 컬럼1, 컬럼2... 
from 테이블1 as 테이블명1  -- left에 위치한 테이블. (기준)
left join 테이블2 as 테이블명2 -- right에 위치한 테이블.  
on a.공통컬럼=b.공통컬럼

 

 

RIGHT JOIN

 

• 쿼리에서 ’ RIGHT JOIN’ 이라는 문구를 기준으로 아래쪽(오른쪽)에 작성하는 테이블.
• ==’RIGHT에 위치해 있는 테이블’
• ==’RIGHT JOIN 에서 기준이 되는 테이블’
• 기준이 되는 테이블은, 조인 조건을 만족해도/만족하지 못해도 모두 출력됩니다.
• 쿼리에서 ’RIGHT JOIN’ 이라는 문구를 기준으로 위쪽(왼쪽)에 작성하는 테이블’
• ==’LEFT 에 위치해 있는 테이블’
• ==’RIGHT JOIN 에서 조건에 따라 출력되는 테이블’.
• RIGHT JOIN에서 LEFT에 위치한 테이블은, 조인 조건을 만족하는 경우 출력되며, 만족하지 못할경우 NULL 값으로 출력됩니다.

# RIGHT JOIN 작성법(기초편)
select 컬럼1, 컬럼2... 
from 테이블1 as 테이블명1 -- left에 위치하는 테이블. 
right join basic.theglory2 as 테이블명2   -- RIGHT 에 위치 하는 테이블. (기준)
on a.공통컬럼=b.공통컬럼