계산 열과 수식 우선순위

SELECT의 계산 열

SELECT product,
       price,
       quantity,
       price * quantity AS line_total
FROM order_items;

산술 연산자

SELECT 7 + 3 AS sum_val,
       7 - 3 AS diff_val,
       7 * 3 AS prod_val,
       7 % 3 AS remainder;

정수 나눗셈의 함정

SELECT 5 / 2          AS int_div,    -- 2
       5 * 1.0 / 2    AS real_div,   -- 2.5
       CAST(5 AS DECIMAL) / 2 AS cast_div; -- 2.5

연산자 우선순위

SELECT 2 + 3 * 4      AS no_parens,  -- 14
       (2 + 3) * 4    AS with_parens; -- 20

실제 할인 쿼리에서의 우선순위

SELECT price,
       discount_rate,
       price - price * discount_rate / 100 AS net_price
FROM products;

문자열 연결

-- Postgres / standard
SELECT first_name || ' ' || last_name AS full_name
FROM employees;

-- Portable
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

NULL은 산술 연산을 무효화합니다

SELECT base_salary,
       bonus,
       base_salary + bonus            AS unsafe_total,
       base_salary + COALESCE(bonus,0) AS safe_total
FROM employees;

방언별 문자열 연결과 NULL

SELECT COALESCE(first_name,'') || ' ' || COALESCE(last_name,'') AS full_name
FROM employees;

표현식 안의 함수

SELECT product,
       ROUND(price * quantity * 1.0, 2) AS line_total,
       UPPER(product)                   AS product_caps
FROM order_items;

자료형 변환과 CAST

SELECT 'Order #' || CAST(order_id AS VARCHAR) AS label
FROM orders;

열로 사용하는 불리언 표현식

-- Portable
SELECT product,
       CASE WHEN price > 100 THEN 1 ELSE 0 END AS is_pricey
FROM products;

빠른 확인

복습