Python SQL Operators: Arithmetic, Bitwise & String Concatenation
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.
"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
Common Beginner Traps & How to Fix Them
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.
Live Interactive Example
Hit Run Code to see it liveYour Turn: Micro Challenge
No pressure! Edit the starter code below and test your solution with instant feedback.
Concatenate Strings with ||
- Select `city || ', ' || country AS location` from `destinations`.
Finished reading and practicing?
Mark this lesson as completed to update your course progress.