Lesson 09 of 61

5 mins read

SQL Operators: Arithmetic, Bitwise & String Concatenation

In Plain English

SQL supports rich scalar operators for mathematical computation, bit manipulation, and string operations. Understanding integer division rules, modulo (`%`), and string concatenation operators (`||` in ANSI/Postgres/SQLite vs `CONCAT()` in MySQL) is essential for data transformations.

The Mental Model

"Operators are the mathematical and verbal toolset of SQL: transforming raw raw numbers and text into polished metrics, formatted addresses, and calculations."

Deep Dive: How It Works

Arithmetic Operators: `+` (addition), `-` (subtraction), `*` (multiplication), `/` (division), `%` (modulo / remainder).

Integer Division Trap: In many SQL engines, `5 / 2` evaluates to `2` (integer truncation). To obtain `2.5`, cast to float: `5.0 / 2` or `CAST(5 AS FLOAT) / 2`.

String Concatenation: ANSI SQL and SQLite/Postgres use `||` (`first_name || ' ' || last_name`). MySQL uses `CONCAT(a, ' ', b)`. SQL Server uses `+`.

Syntax Blueprint

SELECT
  price * 0.9 AS discount_price,
  quantity % 12 AS remaining_units,
  first_name || ' ' || last_name AS full_name
FROM products;

Use arithmetic operators for calculations and || for standard string concatenation.

Core Rules to Remember

String Concatenation ||: The standard ANSI SQL string join operator is double pipe (||).

Modulo % Operator: Calculates the remainder of integer division, ideal for batching and round-robin allocations.

Operator Precedence: Multiplication and division evaluate before addition and subtraction; use parentheses for clarity.

Common Beginner Traps & How to Fix Them

Mistake:Concatenating with NULL using `||` in standard SQL (`'Hello ' || NULL`).

Why it happens: In standard SQL, any scalar operation with NULL yields NULL.

How to fix: Use `COALESCE(col, '')` to replace NULL with an empty string before concatenating.

Pro Tip: Use `ROUND(amount, 2)` to format financial calculations to 2 decimal places.

Live Interactive Example

Hit Run Code to see it live

Mathematical Calculations and String Concatenation

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: || concatenated full name, * multiplied unit price, and % computed modulo remainder.

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

Concatenate Strings with ||

Select `city || ', ' || country AS location` from `destinations`.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.