Operators and expressions

Comparison and predicates

Operator	Meaning
`=`, `<>`, `!=`, `<`, `<=`, `>`, `>=`	comparison
`<=>`	equal, with `NULL <=> NULL` true
`IS [NOT] NULL`, `IS [NOT] TRUE`, `IS [NOT] FALSE`, `IS [NOT] UNKNOWN`
`IS [NOT] DISTINCT FROM`	null-safe inequality
`[NOT] BETWEEN a AND b`
`[NOT] IN (list)`, `[NOT] IN (subquery)`, `(a, b) IN (...)`
`[NOT] LIKE pattern [ESCAPE 'c']`, `LIKE BINARY`	`%` and `_`; `LIKE BINARY` compares bytes
`[NOT] REGEXP`, `[NOT] RLIKE`	regular expression
`SOUNDS LIKE`
`EXISTS (subquery)`
`x op ANY \| SOME \| ALL (subquery)`

Logic

AND / &&, OR, XOR, NOT / !.

|| is OR, as in MySQL. With sql_mode PIPES_AS_CONCAT or ANSI, it joins strings instead.

Arithmetic and bits

+, -, *, /, DIV (integer division), % / MOD, and the bit operators |, &, ^, ~, <<, >>.

Other forms

Form	Meaning
`CASE [x] WHEN ... THEN ... [ELSE ...] END`
`CAST(x AS type)`, `x::type`	conversion; `::` takes PostgreSQL type names too (`int4`, `float8`, `text`, `bool`, `timestamptz`, ...)
`x COLLATE name`	a `_bin` or `binary` collation compares bytes
`doc->'$.path'`, `doc->>'$.path'`	JSON extract, and extract and unquote
`@v := expr`	assign a user variable inside an expression
`agg(...) FILTER (WHERE cond)`	aggregate only matching rows

Literals

Strings in 'single' or "double" quotes; hex X'4F' or 0x4F; bits b'101' or 0b101. Identifiers in `backticks`. Comments: --, #, /* */, and MySQL's /*! ... */ (its contents run).

SELECT clauses

SELECT [DISTINCT] cols
FROM t1
  [INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER] | CROSS | NATURAL] JOIN t2 ON ... | USING (cols)
  [, LATERAL (SELECT ...) | generate_series(...)]
WHERE ...
GROUP BY ... [WITH ROLLUP]
HAVING ...
WINDOW w AS (...)
ORDER BY ...
LIMIT n [OFFSET m]   -- or LIMIT m, n
[FOR UPDATE | FOR SHARE [NOWAIT | SKIP LOCKED] | LOCK IN SHARE MODE]

GROUP BY and ORDER BY take column positions and aliases.
LIMIT and OFFSET take numbers, not expressions.
SQL_NO_CACHE, SQL_CALC_FOUND_ROWS, STRAIGHT_JOIN, HIGH_PRIORITY and the other MySQL modifiers are accepted. SQL_NO_CACHE skips the result cache.
FETCH FIRST n ROWS is not supported; use LIMIT.