Skip to main content

​
Arithmetic

Standard arithmetic with conventional precedence (*, /, % before +, -): 
SELECT price * quantity AS line_total FROM myschema.order_items
SELECT price * quantity - discount AS net_total FROM myschema.order_items
SELECT total % 100 AS cents_portion FROM myschema.orders
Unary plus and minus: 
SELECT -price AS negative_price FROM myschema.products
SELECT +total AS total FROM myschema.orders   -- unary plus is a no-op

​
Bitwise Operators

SELECT flags & 3 AS lower_two_bits FROM myschema.permissions  -- AND
SELECT flags | 4 AS with_flag_set FROM myschema.permissions   -- OR
SELECT flags ^ 1 AS toggled FROM myschema.permissions         -- XOR
SELECT flags << 2 AS shifted_left FROM myschema.permissions   -- left shift
SELECT flags >> 1 AS shifted_right FROM myschema.permissions  -- right shift
SELECT ~flags AS inverted FROM myschema.permissions           -- bitwise NOT

​
String Concatenation

Use || to concatenate strings: 
SELECT first_name || ' ' || last_name AS full_name FROM myschema.users
SELECT 'Order #' || CAST(id AS VARCHAR) AS label FROM myschema.orders

​
CASE

Searched form — each WHEN is a full condition: 
SELECT
  id,
  CASE
    WHEN total >= 1000 THEN 'large'
    WHEN total >= 100  THEN 'medium'
    ELSE 'small'
  END AS order_size
FROM myschema.orders
Simple form — compares a single expression against values: 
SELECT
  id,
  CASE status
    WHEN 'pending'   THEN 'Awaiting payment'
    WHEN 'shipped'   THEN 'On the way'
    WHEN 'delivered' THEN 'Complete'
    ELSE 'Unknown'
  END AS status_label
FROM myschema.orders

​
COALESCE

Returns the first non-NULL argument: 
SELECT COALESCE(nickname, first_name, 'Anonymous') AS display_name
FROM myschema.users

​
NULLIF

Returns NULL if both arguments are equal; otherwise returns the first argument: 
SELECT NULLIF(discount, 0) AS discount FROM myschema.orders

​
ISNULL

Returns true if the argument is NULL, false otherwise. Equivalent to IS NULL in a predicate context: 
SELECT id, ISNULL(shipped_at) AS not_yet_shipped FROM myschema.orders

​
Expression Aliases in ORDER BY

Aliases defined in SELECT can be used in ORDER BY: 
SELECT id, price * quantity AS line_total
FROM myschema.order_items
ORDER BY line_total

​
Literals

TypeExample
Integer42, -7
Decimal3.14, -0.5
String'hello', 'it''s' (doubled single quote)
BooleanTRUE, FALSE
NULLNULL

​
Not Supported

  • CAST / CONVERT
  • DATE, TIME, TIMESTAMP literals
  • Scientific notation literals (e.g., 1e3)
  • Hex string literals (X'0A')
  • Bit string literals (B'0101')