SQL

SQL Operators

⚡ SQL Operators are special symbols and keywords used to perform operations on data within SQL statements. They are commonly used in SELECT, WHERE, HAVING, JOIN, and UPDATE statements to compare values, combine conditions, perform calculations, and filter records.

📖 What are SQL Operators?

An operator tells the database how to evaluate one or more values. For example, you can use operators to find employees whose salary is greater than a specific amount, retrieve students from a particular department, or calculate the total price of an order.

Information

Operators are essential for writing flexible and powerful SQL queries. They help retrieve only the data that matches specific conditions.

🗂️ Types of SQL Operators

SQL operators are commonly divided into the following categories:

⚖️ Comparison Operators
🔗 Logical Operators
🧮 Arithmetic Operators
🔍 Special Operators
📝 Bitwise Operators (supported by some database systems)

⚖️ Comparison Operators

Comparison operators compare two values and return either TRUE, FALSE, or UNKNOWN (when NULL is involved).

Operator	Description	Example
=	Equal to	Salary = 50000
<> or !=	Not equal to	Department <> 'HR'
>	Greater than	Age > 18
<	Less than	Price < 100
>=	Greater than or equal to	Marks >= 90
<=	Less than or equal to	Quantity <= 10

Comparison Operator Example

SELECT *
FROM Employees
WHERE Salary >= 50000;

🔗 Logical Operators

Logical operators combine multiple conditions in a query.

Operator	Description
AND	Returns rows only if all conditions are true.
OR	Returns rows if at least one condition is true.
NOT	Reverses the result of a condition.

Logical Operator Example

SELECT *
FROM Employees
WHERE Department = 'Sales'
AND Salary > 40000;

Using OR

SELECT *
FROM Students
WHERE Department = 'Computer Science'
OR Department = 'Mathematics';

Using NOT

SELECT *
FROM Products
WHERE NOT Category = 'Electronics';

🧮 Arithmetic Operators

Arithmetic operators perform mathematical calculations on numeric values.

Operator	Description	Example
+	Addition	Price + Tax
-	Subtraction	Salary - Deduction
*	Multiplication	Quantity * Price
/	Division	Total / Count
%	Modulus (supported by many databases)	Age % 2

Arithmetic Operator Example

SELECT ProductName,
       Quantity,
       Price,
       Quantity * Price AS TotalAmount
FROM Products;

🔍 Special Operators

SQL provides several special operators for searching, filtering, and testing values.

1️⃣ BETWEEN

Returns values within a specified range (inclusive).

Using BETWEEN

SELECT *
FROM Employees
WHERE Salary BETWEEN 40000 AND 70000;

2️⃣ IN

Checks whether a value matches any value in a list.

Using IN

SELECT *
FROM Students
WHERE Department IN ('Computer Science', 'Physics');

3️⃣ LIKE

Searches for values matching a pattern using wildcard characters.

Using LIKE

SELECT *
FROM Employees
WHERE FullName LIKE 'A%';

Tip

% matches zero or more characters, while _ matches exactly one character.

4️⃣ IS NULL

Checks whether a column contains a NULL value.

Using IS NULL

SELECT *
FROM Employees
WHERE Email IS NULL;

5️⃣ EXISTS

Returns TRUE if a subquery returns one or more rows.

Using EXISTS

SELECT CustomerName
FROM Customers c
WHERE EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.CustomerID = c.CustomerID
);

📝 Bitwise Operators

Some database systems support bitwise operators for manipulating binary values. Availability and syntax may vary depending on the database platform.

Operator	Description
&	Bitwise AND
\|	Bitwise OR
^	Bitwise XOR (database-dependent)
~	Bitwise NOT

📊 Operator Precedence

When multiple operators appear in the same expression, SQL evaluates them according to a precedence order unless parentheses are used.

Priority	Operator Group
1 (Highest)	Arithmetic operators
2	Comparison operators
3	NOT
4	AND
5 (Lowest)	OR

Important

Use parentheses to make complex conditions easier to read and to ensure they are evaluated in the intended order.

💼 Real-World Example

Using Multiple Operators

SELECT EmployeeID,
       FullName,
       Salary
FROM Employees
WHERE Department = 'Sales'
  AND Salary BETWEEN 40000 AND 70000
ORDER BY Salary DESC;

This query retrieves employees from the Sales department whose salaries are between 40000 and 70000, then sorts the results in descending order by salary.

⚠️ Best Practices

Best Practice

Use the most appropriate operator for each condition, prefer IN instead of multiple OR conditions when checking several values, handle NULL values using IS NULL or IS NOT NULL, and use parentheses to improve readability in complex expressions.

🚀 Key Points to Remember

📌 Operators are used to compare, calculate, and filter data.
📌 Comparison operators evaluate relationships between values.
📌 Logical operators combine multiple conditions.
📌 Arithmetic operators perform mathematical calculations.
📌 Special operators like BETWEEN, IN, LIKE, IS NULL, and EXISTS simplify common filtering tasks.
📌 Parentheses help control the order of evaluation.

>>"SQL operators transform simple queries into powerful tools for retrieving exactly the data you need."

Summary

✅ SQL operators are fundamental to querying and manipulating data. By mastering comparison, logical, arithmetic, and special operators, you can write precise, efficient, and easy-to-read SQL queries for a wide range of database tasks.