Logical Operators

Operator Precedence

---

1. Parenthesis
2. Arithmetic Operators
3. Concatenation Operators
4. Comparison Conditions
5. IS NULL, LIKE, NOT IN, etc.
6. NOT
7. AND
8. OR

만약 연산자의 우선순위가 동일하면, 연산자는 순서대로(왼쪽->오른쪽) 적용된다.

AND / OR

---

AND

SELECT emp_no, first_name, last_name
FROM employees
WHERE first_name = 'Georgi' AND last_name = 'Facello';

 emp_no | first_name | last_name
--------+------------+-----------
  10001 | Georgi     | Facello
  55649 | Georgi     | Facello

OR

SELECT emp_no, first_name, last_name
FROM employees
WHERE first_name = 'Georgi'OR last_name = 'Facello'LIMIT 5;

 emp_no | first_name | last_name
--------+------------+-----------
  10001 | Georgi     | Facello
  10327 | Roded      | Facello
  10909 | Georgi     | Atchley
  11029 | Georgi     | Itzfeldt
  11430 | Georgi     | Klassen

NOT

---

SELECT emp_no, first_name, last_name, gender
FROM employees
WHERE NOT gender = 'M' LIMIT 5;

 emp_no | first_name | last_name | gender
--------+------------+-----------+--------
  10002 | Bezalel    | Simmel    | F
  10006 | Anneke     | Preusig   | F
  10007 | Tzvetan    | Zielinski | F
  10009 | Sumant     | Peac      | F
  10010 | Duangkaew  | Piveteau  | F

IN

---

나열된 값들의 리스트 안에 비교하고자 하는 값이 있는지 확인

SELECT <column> FROM <table> WHERE <column> IN (value1, value2, ...)

-- Example 
SELECT firstname, lastname, statefrom customers
WHERE state IN ('SD', 'OH', 'AZ') LIMIT 5;

 firstname |  lastname  | state
-----------+------------+-------
 VKUUXF    | ITHOMQJNYX | SD
 HQNMZH    | UNUKXHJVXB | AZ
 JTNRNB    | LYYSHTQJRE | OH
 PGDTDU    | ETBYBNEGUT | AZ
 IXEENV    | RXEKSWOTYG | SD

BETWEEN

---

최솟값과 최댓값이 주어졌을 때 비교하고자 하는 값이 범위(inclusive) 안에 있는지 확인.

SELECT <column> FROM <table> WHERE <column> BETWEEN X AND Y -- Y(inclusive)

-- Example
SELECT firstname, lastname, age
FROM customers
WHERE age BETWEEN 30 AND 50 LIMIT 5;

 firstname |  lastname  | age
-----------+------------+-----
 JTNRNB    | LYYSHTQJRE |  47
 XMFYXD    | WQLQHUHLFE |  44
 FUOHXX    | WMOEHWMMWM |  44
 LURLDP    | PNPJHXMEPN |  43
 IAYPUX    | YELMUQZEHW |  32

LIKE

---

와일드카드 연산자를 이용해 비교하고자 하는 값과 비슷한 값들을 찾을 때 사용(partial lookup)

Pattern Matching

• % : Any number of characters
• _ : 1 character

Casting

PostgreSQL 의 경우 LIKE 연산자는 텍스트에만 적용되기 때문에 반드시 텍스트로 형변환(casting)을 해야 함.

CAST(salary AS text);
salary::text;

Examples

-- Q1. Get everyone who's name start with 'M'
SELECT first_name FROM employees WHERE first_name LIKE 'M%' LIMIT 5;

-- Q2. How many people's name start with A and end with R?
SELECT count(emp_no) FROM employees
WHERE first_name ILIKE 'A%R'; --ILIKE: case-insensitive matching

-- Q3. How many people's zipcode start with 2 with the 3rd character being a 1?
SELECT count(customerid) FROM customers
WHERE zip::text LIKE '2_1%';

-- Q1. Get everyone who's name start with 'M'
first_name
------------
 Mary
 Mayuko
 Magy
 Mingsen
 Moss

-- Q2. How many people's name start with A and end with R?
  count
-------
  1846

-- Q3. How many people's zipcode start with 2 with the 3rd character being a 1?
 count
-------
   109

IS NULL

---

값과 NULL 을 비교

SELECT <column> FROM <table> WHERE <column> IS NOT NULL;

1. NULL 은 비어있는(empty) 값을 표현하기 위해 사용되며, 0이나 공백과는 다름.
2. Three-valued logic

• 논리식은 참과 거짓 이외에도 'unknown' 을 반환할 수 있음
• 어떠한 값도 NULL 값과는 다름(각각의 NULL 값은 서로 다름)
• NULL 값에 대한 연산은 항상 NULL 임.
• 예를 들어, NULL = NULL 은 NULL 이고 NULL <> NULL 또한 NULL 임

3. Coalesce: NULL value substitution

SELECT COALESCE(<column>, '<string>') AS "<column_alias>" FROM <table>;

-- Coalesce returns the first non-null value in a list
SELECT COALESCE(<column1>, <column2>, <column3>, '<string>')
AS "<combined_columns>" FROM <table>;

EXISTS

---

서브쿼리가 하나 이상의 레코드를 반환할 때 참(True)을 반환

SELECT <column>
FROM <table>
WHERE EXISTS
(SELECT <column> FROM <table> WHERE <condition>);

-- Example
-- Show customers whose income is greater than 90000
-- and totalamount of orders is greater than 400.

SELECT firstname, lastname, income
FROM customers AS c
WHERE EXISTS (
    SELECT * FROM orders AS o
    WHERE c.customerid = o.customerid AND o.totalamount > 400
) AND income > 90000;

firstname |  lastname  | income
-----------+------------+--------
 KHBAOL    | GPIRBAMOMY | 100000
 KUWNRR    | MYSTRFMBHH | 100000
 MNJRDB    | FKQTVUDPHF | 100000
 GZOTZN    | JKHRVQGCHV | 100000
 UBMFOY    | NVXWSSNIIN | 100000
             ...

ANY

---

서브쿼리의 결과 중 어느 것이라도 조건에 맞으면 True 를 반환.

-- Example
-- Q. Show product whose category is Comedy or Family or Classic.

SELECT prod_id
FROM products
WHERE category = ANY (
    SELECT category FROM categories
    WHERE categoryname IN ('Comedy', 'Family', 'Classics')
);

ALL

---

서브쿼리의 결과의 모든 값이 조건에 맞으면 True 반환.

-- Example
-- Q. Show product whose sales is greater than all of average sales per category

SELECT prod_id, title, sales
FROM products
JOIN inventory AS i USING (prod_id)
WHERE i.sales > ALL (
    SELECT AVG(sales) FROM inventory
    JOIN products AS p1 USING (prod_id)
    GROUP BY p1.category);

 prod_id |          title           | sales
---------+--------------------------+-------
       2 | ACADEMY ACE              |    19
       5 | ACADEMY AFRICAN          |    13
       6 | ACADEMY AGENT            |    14
       9 | ACADEMY ALABAMA          |    27
      10 | ACADEMY ALADDIN          |    16
      12 | ACADEMY ALASKA           |    13
                   ...