Welcome to The Coding College! This tutorial covers SQL Operators, essential building blocks for performing operations on data. Operators allow you to filter, manipulate, and calculate data efficiently within SQL queries.
What Are SQL Operators?
SQL Operators are symbols or keywords used to specify conditions, perform calculations, and manipulate data in SQL queries. They are categorized based on their functionality.
Types of SQL Operators
- Arithmetic Operators
- Comparison Operators
- Logical Operators
- Bitwise Operators
- Special Operators
1. Arithmetic Operators
Arithmetic operators are used for mathematical calculations in SQL.
| Operator | Description | Example |
|---|---|---|
+ | Addition | a + b |
- | Subtraction | a - b |
* | Multiplication | a * b |
/ | Division | a / b |
% | Modulus (Remainder) | a % b |
Example
SELECT 10 + 5 AS Addition,
10 - 5 AS Subtraction,
10 * 5 AS Multiplication,
10 / 5 AS Division,
10 % 3 AS Modulus; Result:
| Addition | Subtraction | Multiplication | Division | Modulus |
|---|---|---|---|---|
| 15 | 5 | 50 | 2 | 1 |
2. Comparison Operators
Comparison operators compare two values and return a boolean result (TRUE, FALSE, or NULL).
| Operator | Description | Example |
|---|---|---|
= | Equal to | a = b |
<> | Not equal to | a <> b |
!= | Not equal to (Alternate) | a != b |
> | Greater than | a > b |
< | Less than | a < b |
>= | Greater than or equal to | a >= b |
<= | Less than or equal to | a <= b |
Example
SELECT * FROM employees WHERE salary > 50000; This query retrieves employees with a salary greater than 50,000.
3. Logical Operators
Logical operators are used to combine multiple conditions in SQL.
| Operator | Description | Example |
|---|---|---|
AND | Returns TRUE if all conditions are TRUE | a AND b |
OR | Returns TRUE if any condition is TRUE | a OR b |
NOT | Reverses the result of a condition | NOT a |
Example
SELECT * FROM employees
WHERE department = 'IT' AND salary > 60000; This retrieves IT department employees with a salary above 60,000.
4. Bitwise Operators
Bitwise operators perform operations on binary data.
| Operator | Description | Example |
|---|---|---|
& | Bitwise AND | a & b |
| ` | ` | Bitwise OR |
^ | Bitwise XOR | a ^ b |
5. Special Operators
Special operators provide additional functionality for working with patterns, ranges, or null values.
| Operator | Description | Example |
|---|---|---|
BETWEEN | Checks if a value is within a range | a BETWEEN x AND y |
IN | Checks if a value matches any value in a list | a IN (x, y, z) |
LIKE | Checks if a value matches a pattern | a LIKE 'pattern%' |
IS NULL | Checks if a value is NULL | a IS NULL |
Example: Special Operators
SELECT * FROM employees
WHERE salary BETWEEN 40000 AND 60000; SQL Operator Precedence
SQL evaluates operators in a specific order of precedence. Parentheses can override this order.
Precedence Order:
- Arithmetic Operators (
+,-,*,/,%) - Comparison Operators (
=,>,<, etc.) - Logical Operators (
NOT,AND,OR)
Example
SELECT * FROM employees
WHERE NOT (department = 'HR' AND salary > 40000); Best Practices
- Use Parentheses for Clarity:
Parentheses ensure clarity and avoid ambiguity in complex queries. - Optimize Logical Conditions:
UseANDandORcarefully to minimize unnecessary conditions. - Test Complex Queries:
Break down queries to test individual conditions before combining them.
Conclusion
SQL Operators are powerful tools for building queries that filter, manipulate, and retrieve data efficiently. By mastering these operators, you can handle even the most complex database requirements.