SQL Operators: A Quick Guide
SQL uses various operators to perform different operations on data. This guide provides a helpful overview of common SQL operators, categorized for easier understanding.
Arithmetic Operators
These operators perform basic mathematical calculations on numbers.
Arithmetic Operator Table
| Operator | Description | Example |
|---|---|---|
+ |
Addition | SELECT 5 + 2; -- Output: 7 |
- |
Subtraction | SELECT 10 - 4; -- Output: 6 |
* |
Multiplication | SELECT 3 * 7; -- Output: 21 |
/ |
Division | SELECT 20 / 5; -- Output: 4 |
% |
Modulo (remainder) | SELECT 17 % 3; -- Output: 2 |
Bitwise Operators
Bitwise operators perform operations on individual bits of numbers.
Bitwise Operator Table
| Operator | Description |
|---|---|
& |
Bitwise AND |
| |
Bitwise OR |
^ |
Bitwise XOR (exclusive OR) |
Comparison Operators
These operators compare two values.
Comparison Operator Table
| Operator | Description | Example |
|---|---|---|
= |
Equal to | SELECT * FROM table WHERE column = 10; |
> |
Greater than | SELECT * FROM table WHERE column > 10; |
< |
Less than | SELECT * FROM table WHERE column < 10; |
>= |
Greater than or equal to | SELECT * FROM table WHERE column >= 10; |
<= |
Less than or equal to | SELECT * FROM table WHERE column <= 10; |
!= or <> |
Not equal to | SELECT * FROM table WHERE column != 10; |
Compound Assignment Operators
These operators combine an arithmetic or bitwise operation with an assignment.
Compound Assignment Operator Table
| Operator | Description |
|---|---|
+= |
Add and assign |
-= |
Subtract and assign |
*= |
Multiply and assign |
/= |
Divide and assign |
%= |
Modulo and assign |
&= |
Bitwise AND and assign |
^= |
Bitwise XOR and assign |
|= |
Bitwise OR and assign |
Logical Operators
These operators combine or modify boolean expressions.
Logical Operator Table
| Operator | Description | Example |
|---|---|---|
ALL |
TRUE if all subquery values meet the condition. | SELECT * FROM table WHERE column > ALL (SELECT column FROM another_table); |
AND |
TRUE if all conditions are TRUE. | SELECT * FROM table WHERE column1 = 10 AND column2 = 20; |
ANY |
TRUE if any subquery value meets the condition. | SELECT * FROM table WHERE column > ANY (SELECT column FROM another_table); |
BETWEEN |
TRUE if the value is within a range (inclusive). | SELECT * FROM table WHERE column BETWEEN 10 AND 20; |
EXISTS |
TRUE if a subquery returns one or more rows. | SELECT * FROM table WHERE EXISTS (SELECT 1 FROM another_table WHERE condition); |
IN |
TRUE if the value is in a list. | SELECT * FROM table WHERE column IN (1, 2, 3); |
LIKE |
TRUE if the value matches a pattern. | SELECT * FROM table WHERE column LIKE '%pattern%'; |
NOT |
Reverses a condition's truth value. | SELECT * FROM table WHERE NOT column = 10; |
OR |
TRUE if at least one condition is TRUE. | SELECT * FROM table WHERE column1 = 10 OR column2 = 20; |
SOME |
TRUE if any subquery value meets the condition (same as ANY). | SELECT * FROM table WHERE column > SOME (SELECT column FROM another_table); |