This module closes with a reference lesson pulling every operator category together: comparison and logical operators already covered across Modules 1 and 2, plus arithmetic and string concatenation, two categories this course hasn't covered directly until now.
Learning Objectives
- Use arithmetic operators (+, -, *, /, %) directly inside a query.
- Concatenate strings using the correct operator or function for a given database.
- Locate any operator from this course in one consolidated reference.
Comparison and Logical Operators
| Operator | Meaning | Covered In |
|---|---|---|
| =, <> or !=, <, >, <=, >= | Equality and ordering comparisons | Module 1, WHERE |
| AND, OR, NOT | Combine or negate conditions | Module 2 |
| IN, BETWEEN, LIKE | Set membership, ranges, pattern matching | Module 2 |
| IS NULL, IS NOT NULL | Test for a missing value | Module 1 |
| EXISTS, ANY, ALL | Subquery-based comparisons | Module 6 |
Arithmetic Operators
+, -, *, and / work identically across every database in this course, standard arithmetic directly inside a query, no function call needed. salary / 12 above divides, turning an annual figure into a monthly one, right inside the SELECT list.
SELECT first_name, salary, salary * 1.10 AS with_raise, salary / 12 AS monthly, salary - 5000 AS after_deduction FROM employees;
The Modulo Operator
| Database | Modulo (Remainder) Syntax |
|---|---|
| MySQL / MariaDB / PostgreSQL / SQLite | % or MOD(a, b) |
| SQL Server | % |
| Oracle | MOD(a, b) only, no % operator |
SELECT first_name, id, MOD(id, 2) AS is_odd FROM employees;
MOD(a, b) works on every database in this course including Oracle, making it the portable choice; the % symbol alone doesn't work on Oracle at all.
String Concatenation: The Real Split
| Database | Concatenation Syntax |
|---|---|
| PostgreSQL / SQLite / Oracle | || |
| MySQL / MariaDB | CONCAT(a, b, ...) only, no || by default |
| SQL Server | + , or CONCAT(a, b, ...) (SQL Server 2012+) |
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
CONCAT() is the most broadly portable choice here, it works on MySQL, MariaDB, PostgreSQL, and SQL Server (2012+); only pre-2012 SQL Server lacks it, where + is the fallback. || remains the ANSI-standard operator, and the natural choice on PostgreSQL, SQLite, and Oracle specifically.
Try It Yourself
Write a query, using CONCAT(), that returns each employee's name formatted as "last_name, first_name".
SELECT CONCAT(last_name, ', ', first_name) AS display_name FROM employees;
Frequently Asked Questions
Why doesn't MySQL support || for concatenation by default?
By default, MySQL treats || as logical OR instead (a non-standard extension of its own). A special PIPES_AS_CONCAT SQL mode can change this, but it isn't the default behavior, which is exactly why CONCAT() is the safer, portable choice for MySQL and MariaDB.
Does operator precedence (order of operations) work the way it does in math?
Yes, * and / evaluate before + and -, the same rule from ordinary arithmetic, and parentheses override the default order exactly like in math class. This mirrors the AND-before-OR precedence covered in Module 2.
Common Beginner Mistakes
Using || on MySQL expecting concatenation
By default it's interpreted as logical OR there instead, producing a 1 or 0, not a concatenated string, a genuinely confusing silent behavior difference rather than an outright error.
Using % for modulo on Oracle
Oracle has no % operator at all, this is a syntax error. MOD(a, b) is required, and happens to also work everywhere else in this course, making it the safer default regardless of target database.
Best Practices
- Default to CONCAT() for string concatenation and MOD() for remainders, both are the most broadly portable choices across this course's six databases.
- Never rely on || for concatenation on a query that might run on MySQL or MariaDB.
- Keep this lesson as a lookup reference, not something to memorize in one pass.
Interview Questions
Why is CONCAT() generally preferred over || for portable string concatenation?
|| is the ANSI-standard operator and works on PostgreSQL, SQLite, and Oracle, but MySQL and MariaDB interpret || as logical OR by default instead. CONCAT() works on MySQL, MariaDB, PostgreSQL, and SQL Server (2012+), covering far more of this course's databases safely.
Write a portable query, using MOD(), that labels each employee's id as 'Even' or 'Odd'.
SELECT id, CASE WHEN MOD(id, 2) = 0 THEN 'Even' ELSE 'Odd' END AS parity FROM employees; — MOD(id, 2) works on every database in this course, combined with the CASE expression from earlier in this module.
Summary
Arithmetic operators (+, -, *, /) are portable everywhere; modulo and string concatenation are the real exceptions, MOD() and CONCAT() are the safe, portable choices, while % and || each have at least one database in this course where they don't behave as expected.
That completes Module 6, Subqueries and Advanced SELECT: EXISTS, ANY, and ALL for subquery-based comparisons, SELECT INTO and INSERT INTO SELECT for copying query results into tables, CASE for conditional logic, NULL-substitution functions, stored procedures, comments, and this operators reference.
What's Next?
Module 7, Database and Tables, is next: CREATE DATABASE, DROP DATABASE, BACKUP DATABASE, and the big payoff, CREATE TABLE statements that actually build the departments, employees, projects, and project_assignments tables this course has been querying since Module 4.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.