[SQL] 07. 원하는 조건만 뽑아오기 (WHERE) - 1

WHERE절을 통해 원하는 데이터만 추출할 수 있다

SELECT 원하는 컬럼
FROM 조회하는 테이블
WHERE 필터링하고 싶은 조건

예시를 통해 다뤄보자.

 

1. 100달러 미만 숙소 찾아보기

SELECT id, name, neighbourhood_group, neighbourhood, room_type, price
FROM data.airbnb_nyc
WHERE price < 100

 

2. Manhattan에 있는 숙소 찾아보기

SELECT id, name, neighbourhood_group, neighbourhood, room_type, price
FROM data.airbnb_nyc
WHERE neighbourhood_group = 'Manhattan'

SELECT id, name, neighbourhood_group, neighbourhood, room_type, price
FROM data.airbnb_nyc
WHERE neighbourhood_group =  "Manhattan"

 3. Manhattan에 있지 않은 숙소 찾아보기

SELECT id, name, neighbourhood_group, neighbourhood, room_type, price
FROM data.airbnb_nyc
WHERE neighbourhood_group != 'Manhattan'

4. 숙소이름이 Brooklyn Writer's Nook 찾아보기

SELECT id, name, neighbourhood_group, neighbourhood, room_type, price
FROM data.airbnb_nyc
WHERE name = "Brooklyn Writer's Nook"

5. 맨해튼에 있는 100달러 미만 숙소 찾아보기

SELECT id, name, neighbourhood_group, neighbourhood, room_type, price
FROM data.airbnb_nyc
WHERE neighbourhood_group = "Manhattan"
AND	price < 100

6. 맨해튼에 있는 50달러 이상 150달러 이하 숙소 찾아보기

SELECT id, name, neighbourhood_group, neighbourhood, room_type, price
FROM data.airbnb_nyc
WHERE neighbourhood_group = "Manhattan"
AND	price >= 50
AND price <= 150

SELECT id, name, neighbourhood_group, neighbourhood, room_type, price
FROM data.airbnb_nyc
WHERE neighbourhood_group = "Manhattan"
AND	price BETWEEN 50 AND 150

7. 맨해튼에 있는 100 달러 미만이면서,
Midtown이거나 SoHo에 있는 숙소 찾아보기

SELECT id, name, neighbourhood_group, neighbourhood, room_type, price
FROM data.airbnb_nyc
WHERE neighbourhood_group = "Manhattan"
AND price < 100
AND (neighbourhood = 'Midtown' OR neighbourhood = 'SoHo')

SELECT id, name, neighbourhood_group, neighbourhood, room_type, price
FROM data.airbnb_nyc
WHERE neighbourhood_group = "Manhattan"
AND price < 100
AND neighbourhood IN ('Midtown', 'SoHo')

8. 맨해튼에 있는 100 달러 미만이면서,
미드타운이나 소호에 있는 숙소는 싫어 = 미드타운도 싫고 소호도 싫어

SELECT id, name, neighbourhood_group, neighbourhood, room_type, price
FROM data.airbnb_nyc
WHERE neighbourhood_group = "Manhattan"
AND price < 100
AND neighbourhood != 'Midtown'
AND	neighbourhood != 'SoHo'

SELECT id, name, neighbourhood_group, neighbourhood, room_type, price
FROM data.airbnb_nyc
WHERE neighbourhood_group = "Manhattan"
AND price < 100
AND neighbourhood NOT IN ('Midtown', 'SoHo')

 

* 문자를 크기로 비교하면, 알파벳을 기준으로 조회한다.

SELECT id, name, neighbourhood_group, neighbourhood, room_type, price
FROM data.airbnb_nyc
WHERE neighbourhood_group > "Manhattan"

 

WHERE 정리

• > (~ 보다 크다)
• < (~ 보다 작다)
• >= (~보다 크거나 같다)
• <= (~보다 작거나 같다)
• = (~와 같다)
• != (~와 같지 않다)
• A이상 B이하일 때는 BETWEEN A AND B
• 조건을 모두 만족해야 할 때 AND / 둘 중 하나만 만족해도 될 때 OR
  • 참고) 여러 조건을 하나로 묶고 싶을 때는 ()를 꼭 쓴다
• 하나의 컬럼을 여러 값과 비교할 때: IN / NOT IN