SQL Operators
SQL Operators
SQL (Structured Query Language) operators are fundamental components used to perform operations on data within a database. These operators manipulate values, filter records, and establish logical conditions.
Operators in SQL can be categorized into several types:
- Arithmetic Operators
- Comparison Operators
- Logical Operators
- Bitwise Operators
- Assignment Operators
1.Arithmetic Operators
Arithmetic operators perform mathematical operations on numerical values.
| Operator | Description | Example |
|---|---|---|
+ |
Addition | SELECT salary + 5000 FROM employees; |
- |
Subtraction | SELECT salary - 2000 FROM employees; |
* |
Multiplication | SELECT quantity * price FROM orders; |
/ |
Division | SELECT total / count FROM statistics; |
% |
Modulus (remainder) | SELECT 10 % 3; = 1 |
Syntax
SELECT column_name, (column_name operator value) AS result FROM table_name;
Example:
SELECT employee_id, salary, salary * 1.10 AS increased_salary FROM employees;
2. Comparison Operators
Comparison operators are used to compare values and return a Boolean result.
| Operator | Description | Example |
|---|---|---|
= |
Equal to | SELECT * FROM employees WHERE department = 'IT'; |
!= or <> |
Not equal to | SELECT * FROM employees WHERE age <> 30; |
> |
Greater than | SELECT * FROM orders WHERE price > 100; |
< |
Less than | SELECT * FROM orders WHERE price < 50; |
>= |
Greater than or equal to | SELECT * FROM employees WHERE experience >= 5; |
<= |
Less than or equal to | SELECT * FROM employees WHERE age <= 40; |
Syntax
SELECT column_name, (column_name operator value) AS result FROM table_name;
Example:
SELECT name, age FROM users WHERE age >= 18;
3. Logical Operators
Logical operators combine multiple conditions in a query.
| Operator | Description | Example |
|---|---|---|
AND |
Returns true if both conditions are met | SELECT * FROM employees WHERE department = 'HR' AND age > 30; |
OR |
Returns true if at least one condition is met | SELECT * FROM employees WHERE department = 'HR' OR department = 'IT'; |
NOT |
Reverses the condition result | SELECT * FROM products WHERE NOT price < 50; |
Syntax
SELECT column_name FROM table_name WHERE condition1 operator condition2;
Example:
SELECT name, salary FROM employees WHERE department = 'Finance' AND salary > 50000;
4. Bitwise Operators
Bitwise operators work on binary values at the bit level.
| Operator | Description | Example |
|---|---|---|
& |
Bitwise AND | SELECT 5 & 3; -- Returns 1 |
| |
Bitwise OR | SELECT 5 | 3; -- Returns 7 |
^ |
Bitwise XOR | SELECT 5 ^ 3; -- Returns 6 |
~ |
Bitwise NOT | SELECT ~5; -- Returns -6 |
Syntax
SELECT number1 operator number2;
Example:
SELECT 12 | 8; -- Returns 12 as a result of bitwise OR operation
5. Assignment Operators
Assignment operators are used to assign values to variables.
| Operator | Description | Example |
|---|---|---|
= |
Assigns a value | SET @total = 100; |
Syntax
SET @variable_name = value;
Example:
SET @total_sales = (SELECT SUM(price) FROM orders WHERE status = 'completed'); SELECT @total_sales;
Conclusion
SQL operators are crucial for manipulating and filtering data in queries. By leveraging arithmetic, comparison, logical, bitwise, and assignment operators, developers can craft powerful SQL queries that enhance data processing efficiency.