Where

‘=‘ 기호 사용

SELECT
	last_name,
	first_name
FROM
	customer
WHERE
	first_name = 'Jamie';

‘AND’ 연산자

SELECT
	last_name,
	first_name
FROM
	customer
WHERE
	first_name = 'Jamie' 
	AND 
    last_name = 'Rice';

‘OR’ 연산자

SELECT
	first_name,
	last_name
FROM
	customer
WHERE
	last_name = 'Rodriguez' 
	OR 
	first_name = 'Adam';

‘IN’ 연산자

SELECT
	first_name,
	last_name
FROM
	customer
WHERE 
	first_name IN ('Ann','Anne','Annie');

‘LIKE’ 연산자

SELECT
	first_name,
	last_name
FROM
	customer
WHERE 
	first_name LIKE 'Ann%'

‘BETWEEN’ 연산자

SELECT
	first_name,
	LENGTH(first_name) name_length
FROM
	customer
WHERE 
	first_name LIKE 'A%' AND
	LENGTH(first_name) BETWEEN 3 AND 5
ORDER BY
	name_length;

‘<>’ 연산자

SELECT 
	first_name, 
	last_name
FROM 
	customer 
WHERE 
	first_name LIKE 'Bra%' 
	AND 
	last_name <> 'Motley';

참고로 <> 연산자는 != 연산자랑 동일하게 사용 가능하다.

Limit

Limit : 상위 x개의 행 조회

SELECT
	film_id,
	title,
	release_year
FROM
	film
ORDER BY
	film_id
LIMIT 5;

OFFSET : x행 부터 조회

SELECT
	film_id,
	title,
	release_year
FROM
	film
ORDER BY
	film_id
LIMIT 4 
OFFSET 3;

4행(OFFSET 3 지정)부터 4개의 행(LIMIT 4 지정) 조회

하위 x개의 행을 조회하고 싶을 때

SELECT
	film_id,
	title,
	rental_rate
FROM
	film
ORDER BY
	rental_rate DESC
LIMIT 10;

그냥 간단하게 오름차순(DESC)으로 바꿔주면 해결

Fetch

Fetch First {조회할 행 개수 | 생략가능} Row Only

SELECT
    film_id,
    title
FROM
    film
ORDER BY
    title 
FETCH FIRST ROW ONLY;

조회할 행 개수를 생략했다.
그렇다면,
이 쿼리는 첫 행만 조회해온다.

SELECT
    film_id,
    title
FROM
    film
ORDER BY
    title 
FETCH FIRST 5 ROW ONLY;

5행까지 조회를 한다.
만약 5 -> 1로 조회하면
FETCH FIRST ROW ONLY;
이렇게 안 적는 것과 동일한 결과를 가져온다.

결과를 보면
fetch는 limit와 동일한 기능을 한다.

Fetch + Offset

SELECT
    film_id,
    title
FROM
    film
ORDER BY
    title 
OFFSET 5 ROWS 
FETCH FIRST 5 ROW ONLY; 

In

In

SELECT customer_id,
	rental_id,
	return_date
FROM
	rental
WHERE
	customer_id IN (1, 2)
ORDER BY
	return_date DESC;

‘=‘ & ‘OR’

SELECT
	rental_id,
	customer_id,
	return_date
FROM
	rental
WHERE
	customer_id = 1 
	OR 
	customer_id = 2
ORDER BY
	return_date DESC;

NOT IN & <>

SELECT
	customer_id,
	rental_id,
	return_date
FROM
	rental
WHERE
	customer_id NOT IN (1, 2);

위 쿼리는 아래의 코드와 동일하다

SELECT
	customer_id,
	rental_id,
	return_date
FROM
	rental
WHERE
	customer_id <> 1
	AND 
	customer_id <> 2;

In & Subquery

SELECT
	customer_id,
	first_name,
	last_name
FROM
	customer
WHERE
	customer_id IN (
		SELECT customer_id
		FROM rental
		WHERE CAST (return_date AS DATE) = '2005-05-27'
	)
ORDER BY customer_id;

CAST 사용법
CAST(‘{변환하고자 하는 데이터}’ AS {데이터형식})

Between

between

SELECT
	customer_id,
	payment_id,
	amount
FROM
	payment
WHERE
	amount BETWEEN 8 AND 9;

not between

SELECT
	customer_id,
	payment_id,
	amount
FROM
	payment
WHERE
	amount NOT BETWEEN 8 AND 9;

Date를 조건으로 줄 때

SELECT
	customer_id,
	payment_id,
	amount,
 payment_date
FROM
	payment
WHERE
	payment_date 
	BETWEEN '2007-02-07' AND '2007-02-15';

YYYY-MM-DD 형식으로 조건을 주면,
Timestamp 형식 적힌 것도 조건을 줄 수 있다.

Like

like

SELECT
	first_name,
        last_name
FROM
	customer
WHERE
	first_name LIKE 'Jen%';

위 커리는 Jen으로 시작하는 first_name을 조회한다.
만약 앞 뒤 중간 어디든 포함되어 있는 것을 원한다면
‘%{찾을 키워드}%’
끝나는 키워드를 찾는다면
‘%{찾을 키워드}’

whildcard(%) & underscore(_)

SELECT
	first_name,
	last_name
FROM
	customer
WHERE
	first_name LIKE '_her%'
ORDER BY 
        first_name;

참고

SELECT
	'foo' LIKE 'foo', -- true
	'foo' LIKE 'f%', -- true
	'foo' LIKE '_o_', -- true
	'bar' LIKE 'b_'; -- false

NOT LIKE

SELECT
	first_name,
	last_name
FROM
	customer
WHERE
	first_name NOT LIKE 'Jen%'
ORDER BY 
        first_name

ILIKE

SELECT
	first_name,
	last_name
FROM
	customer
WHERE
	first_name ILIKE 'BAR%';

LIKE와 ILIKE 차이점
LIKE : 대소문자 구분
ILIKE : 대소문자 구분X

Is Null

(예제를 위한)테이블 추가 및 INSERT

CREATE TABLE contacts(
    id INT GENERATED BY DEFAULT AS IDENTITY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(15),
    PRIMARY KEY (id)
);

INSERT INTO contacts(first_name, last_name, email, phone)
VALUES ('John','Doe','john.doe@example.com',NULL),
    ('Lily','Bush','lily.bush@example.com','(408-234-2764)');

PostgreSQL 버전에 따라 위 쿼리대로 테이블 추가가 안될 수 있다.
그럴 경우 아래의 쿼리를 이용

CREATE TABLE contacts(
    id SERIAL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(15),
    PRIMARY KEY (id)
);

INSERT INTO contacts(first_name, last_name, email, phone)
VALUES ('John','Doe','john.doe@example.com',NULL),
    ('Lily','Bush','lily.bush@example.com','(408-234-2764)');

IS NULL

SELECT
    id,
    first_name,
    last_name,
    email,
    phone
FROM
    contacts
WHERE
    phone IS NULL;

잘못된 예

SELECT
    id,
    first_name,
    last_name,
    email,
    phone
FROM
    contacts
WHERE
    phone = NULL;

IS NOT NULL

SELECT
    id,
    first_name,
    last_name,
    email,
    phone
FROM
    contacts
WHERE
    phone IS NOT NULL;