SQL Operators – Arithmetic, Comparison, Logical & More
Introduction – What Are SQL Operators?
SQL operators are symbols or keywords used to perform operations on data in a SQL statement. They help you compare, calculate, combine, and evaluate values in expressions.
In this guide, you’ll learn:
- Types of SQL operators: arithmetic, comparison, logical, bitwise
- Syntax and usage in WHERE, SELECT, and expressions
- Best practices for writing readable and efficient queries
1. Arithmetic Operators
Used for basic mathematical calculations.
| Operator | Operation | Example | Result Description |
|---|---|---|---|
| + | Addition | price + tax | Adds two values |
| – | Subtraction | total - discount | Subtracts values |
| * | Multiplication | quantity * price | Multiplies values |
| / | Division | total / count | Divides values |
| % | Modulo | age % 2 | Remainder of division (even/odd) |
2. Comparison Operators
Used to compare values in WHERE, CASE, joins, etc.
| Operator | Meaning | Example |
|---|---|---|
| = | Equal to | WHERE age = 25 |
| <> or != | Not equal to | WHERE status != 'new' |
| > | Greater than | WHERE salary > 50000 |
| < | Less than | WHERE age < 30 |
| >= | Greater or equal to | WHERE score >= 80 |
| <= | Less or equal to | WHERE height <= 180 |
3. Logical Operators
Combine multiple conditions in a query.
| Operator | Meaning | Example |
|---|---|---|
| AND | Both conditions true | WHERE age > 18 AND country = 'USA' |
| OR | At least one is true | WHERE role = 'Admin' OR role = 'Manager' |
| NOT | Reverses a condition | WHERE NOT status = 'inactive' |
4. Special/Other Operators
Include pattern matching, set membership, range filtering.
| Operator | Description | Example |
|---|---|---|
| LIKE | Pattern matching | name LIKE 'A%' |
| IN | Value is in a list | status IN ('active', 'new') |
| BETWEEN | Value within a range | price BETWEEN 10 AND 50 |
| IS NULL | Value is NULL | email IS NULL |
| IS NOT NULL | Value is not NULL | email IS NOT NULL |
5. Bitwise Operators (SQL Server / MySQL)
Rare but used for binary-level calculations.
| Operator | Meaning | Example |
|---|---|---|
| & | Bitwise AND | 5 & 3 → 1 |
| Bitwise OR | ||
| ^ | Bitwise XOR | 5 ^ 3 → 6 |
| ~ | Bitwise NOT (negate) | ~5 → -6 |
Best Practices
| Recommended | Avoid This |
|---|---|
| Use parentheses in logical groups | Mixing AND/OR without parentheses |
| Use aliases for expressions | Leaving long calculations unaliased |
| Combine operators logically | Overusing nested/unclear logic |
Summary – Recap & Next Steps
SQL operators give your queries logical power and expression-building capabilities. They enable filtering, math, conditionals, and control flows.
Key Takeaways:
- Arithmetic:
+,-,*,/,% - Comparison:
=,<>,<,>,<=,>= - Logical:
AND,OR,NOT - Special:
LIKE,IN,BETWEEN,IS NULL
Real-World Relevance:
Used in business logic, analytics, dashboards, search filtering, and data validation.
Next: Explore advanced expressions like CASE, COALESCE, and NULLIF.
FAQ – SQL Operators
What are the most common SQL operators?
=, AND, OR, LIKE, IN, and arithmetic operators like + and *.
What’s the difference between = and LIKE?
= checks exact matches. LIKE allows wildcard pattern matching.
Can I mix logical and comparison operators?
Yes. Use parentheses to control evaluation order.
Are bitwise operators used often?
Rare in SQL queries, mostly for low-level or binary data processing.
Share Now :
