Skip to content
Tutorials Center
My learning

Topics

Lesson 18 of 125 ยท Querying Data

SQL Operators

A complete guide to SQL operators - arithmetic, comparison, logical, string concatenation with ||, bitwise operators - plus an operator precedence table.

An operator is a symbol or keyword that combines values into a new value: + adds two numbers, = compares them, AND combines two conditions, || joins two strings. You've already used many of them in WHERE clauses. This lesson collects them all in one place, explains the details that trip people up (integer division, the meaning of % with negative numbers, string concatenation in different databases) and ends with a precedence table that tells you which operator runs first when you mix them.

Every example runs in SQLite. Where other databases behave differently, you'll see a note or a comparison table, because operators are one of the places where SQL dialects disagree the most.

Arithmetic operators

SQL has the five arithmetic operators you'd expect, plus unary minus:

Operator Meaning Example Result in SQLite
+ addition 7 + 2 9
- subtraction (or negation: -x) 7 - 2 5
* multiplication 7 * 2 14
/ division 7 / 2 3 (integer division)
% remainder (modulo) 7 % 2 1

You can use them anywhere a value is allowed: in the SELECT list, in WHERE, in ORDER BY. Run this query and look closely at the division columns:

SQL Arithmetic

Press Run (or Ctrl + Enter) to see the result here.

Three results deserve attention:

  • 7 / 2 is 3. When both sides are integers, SQLite (like PostgreSQL and SQL Server) performs integer division and drops the fraction. Make one side a real number (7 / 2.0 or 7 * 1.0 / 2) to get 3.5. MySQL is the exception: its / always returns a decimal, and it has a separate DIV operator for integer division. Calculated Columns shows how this affects real reports.
  • -7 % 3 is -1. The remainder takes the sign of the left operand in SQLite, PostgreSQL, MySQL and SQL Server. If you need a result that's always positive (for example, to wrap around a week), use ((x % n) + n) % n.
  • 1 / 0 is NULL in SQLite (and MySQL, which also issues a warning). PostgreSQL, SQL Server and Oracle raise a division by zero error instead, which stops the whole query. COALESCE, IFNULL and NULLIF shows the portable way to avoid it with NULLIF.

Arithmetic with NULL gives NULL: price + NULL is unknown (see Working with NULL).

Here are the operators at work on real data: the line total of each order item, with a 10% discount applied to lines over 200:

SQL Arithmetic on columns ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The biggest line is 567 (three Ultralight Tents at 189), which becomes 510.3 after the discount.

Comparison operators

Comparison operators compare two values and return true, false or (when NULL is involved) unknown:

Operator Meaning Notes
= equal SQLite also accepts ==
<> not equal standard; != works in every major database too
<, > less than, greater than
<=, >= less or equal, greater or equal
IS, IS NOT equal / not equal, treating NULLs as equal SQLite; the standard spelling is IS NOT DISTINCT FROM
IS NULL, IS NOT NULL missing / present
BETWEEN a AND b in a range, inclusive see IN and BETWEEN
IN (...) in a list see IN and BETWEEN
LIKE, GLOB matches a pattern see Pattern Matching with LIKE

In SQLite and MySQL, a comparison is just a value: 1 for true and 0 for false. That lets you select it, which is a great way to experiment:

SQL Comparisons are values

Press Run (or Ctrl + Enter) to see the result here.

Text is compared character by character using the collation (see Sorting with ORDER BY), so with SQLite's default BINARY collation 'Zebra' < 'apple' is true: uppercase letters come before lowercase ones. NULL = NULL is NULL, while NULL IS NULL is 1.

PostgreSQL returns real booleans (true/false) for comparisons. SQL Server has no boolean type in the SELECT list at all: SELECT 3 = 3 is a syntax error there, and you'd write CASE WHEN 3 = 3 THEN 1 ELSE 0 END instead.

Comparing different types. Comparing a number with text is a classic source of bugs. Without a column type to guide it, SQLite considers every number smaller than every text value, so 10 < '9' is true. A column declared as INTEGER or REAL converts text to a number first, which hides the problem until you compare two expressions. Compare numbers with numbers and text with text; Types and CAST explains the rules.

Logical operators

AND, OR and NOT combine conditions, as you saw in AND, OR and NOT:

Operator True whenโ€ฆ
a AND b both are true
a OR b at least one is true
NOT a a is false

With NULL they follow three-valued logic: NULL AND false is false, NULL OR true is true, and everything else involving NULL is unknown. The full truth tables are in Working with NULL.

String concatenation: ||

The standard operator for joining strings is || (two vertical bars). SQLite, PostgreSQL and Oracle support it:

SQL Concatenation ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Numbers are converted to text automatically, so 'Customer #' || id works. But a NULL anywhere in a || chain makes the whole result NULL, as the second query shows. The concat() function skips NULLs instead, and concat_ws() ("with separator") puts a separator between the non-NULL values. Both are available in SQLite 3.44 and later, PostgreSQL, MySQL and SQL Server 2012+ (concat_ws in SQL Server 2017+).

Concatenation is where the dialects disagree the most:

Database Operator Notes
SQLite, PostgreSQL, Oracle a || b standard; Oracle treats NULL as an empty string here
MySQL / MariaDB CONCAT(a, b) || means OR unless the PIPES_AS_CONCAT mode is on
SQL Server a + b or CONCAT(a, b) + returns NULL if either side is NULL

In MySQL, 'a' || 'b' is not a string. With the default settings it's a logical OR of two values, which returns 0. Use CONCAT() in MySQL code, or concat() everywhere if you want one spelling that works in all four databases.

Bitwise operators

Bitwise operators work on the individual bits of integers. They're rare in everyday queries but useful for flags: storing several yes/no options in one integer, where each option is a power of two.

Operator Meaning Example Result
& bitwise AND 5 & 3 1
| bitwise OR 5 | 3 7
~ bitwise NOT ~5 -6
<< shift left 1 << 4 16
>> shift right -16 >> 2 -4

SQLite has no bitwise XOR operator (PostgreSQL uses #, MySQL and SQL Server use ^). Here's a flags column in action: read = 1, write = 2, delete = 4.

SQL Permission flags

Press Run (or Ctrl + Enter) to see the result here.

permissions & 2 keeps only the "write" bit, so the first SELECT returns editor (3 = 1 + 2) and admin (7 = 1 + 2 + 4). permissions | 2 switches the bit on, turning cleanup from 5 into 7. Flags are compact, but they're hard to read and impossible to index well; in a new design, separate BOOLEAN columns or a permissions table are usually clearer (see Normalization).

Operator precedence

When an expression mixes operators, precedence decides which runs first, just like multiplication before addition in school math. Here is SQLite's order, from the tightest-binding operators to the loosest:

Precedence Operators
1 (highest) unary -, unary +, ~
2 COLLATE
3 ||, ->, ->>
4 *, /, %
5 +, -
6 &, |, <<, >>
7 ESCAPE
8 <, <=, >, >=
9 =, ==, <>, !=, IS, IS NOT, IS DISTINCT FROM, IN, LIKE, GLOB, BETWEEN, IS NULL
10 NOT
11 AND
12 (lowest) OR

Operators on the same row are evaluated left to right. Most of this matches intuition and the other databases, with two surprises: in SQLite || binds tighter than arithmetic (PostgreSQL ranks it below + and -), and all four bitwise operators share one level. Test it:

SQL Precedence in action

Press Run (or Ctrl + Enter) to see the result here.

The results are 14, 20 and 3 as you'd expect. Then 'Total: ' || 2 + 3 gives 3, not 'Total: 5': SQLite first builds the string 'Total: 2', then adds 3 to it, and a string that doesn't start with a number counts as 0 in arithmetic. With parentheses you get Total: 5. 1 OR 0 AND 0 is 1 because AND runs before OR, and NOT 0 + 1 is 0 because NOT applies to the whole sum 0 + 1.

When in doubt, add parentheses. They cost nothing, make your intent obvious to the next reader, and protect you from the small precedence differences between databases. This is especially true for AND mixed with OR.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does SELECT 9 / 4, 9 / 4.0; return in SQLite?

    Two integers give integer division, which drops the fraction (2). If either side is real, you get 2.25.
  2. 2. What is -10 % 3 in SQLite?

    The remainder takes the sign of the left operand. 10 % 3 is 1, so -10 % 3 is -1.
  3. 3. What does SELECT 'abc' || NULL; return in SQLite?

    Any NULL in a || chain makes the whole result NULL. Use concat() to skip NULLs.
  4. 4. You want to join first and last names in MySQL. Which expression works with default settings?

    In MySQL || means logical OR by default, and + is numeric addition. CONCAT() is the MySQL way.
  5. 5. What does SELECT 1 OR 1 AND 0; return?

    AND binds tighter than OR, so this is 1 OR (1 AND 0), which is 1 OR 0, which is 1.

Summary

  • Arithmetic: + - * / %. Integer divided by integer is integer division in SQLite, PostgreSQL and SQL Server; use 2.0 or * 1.0 for a fractional result. % keeps the sign of the left side. Division by zero is NULL in SQLite and an error in PostgreSQL and SQL Server.
  • Comparison: =, <> (or !=), <, >, <=, >=, plus IS, IN, BETWEEN and LIKE. SQLite and MySQL return 1/0; any comparison with NULL is unknown.
  • Logical: AND, OR, NOT, with three-valued logic for NULL.
  • Concatenation: || in standard SQL, SQLite, PostgreSQL and Oracle; CONCAT() in MySQL; + in SQL Server. concat() skips NULLs.
  • Bitwise: & | ~ << >> for integer flags.
  • Precedence: unary operators, ||, * / %, + -, bitwise, comparisons, NOT, AND, OR. Use parentheses whenever it isn't obvious.

Next: Calculated Columns, where you'll put these operators to work building new columns from existing ones.