SQL Operators

SQL operators are symbols or keywords that perform operations on one or more values (operands). These operations can include comparisons, arithmetic calculations, logical operations, and more.

Types of SQL Operators

SQL operators are generally categorized as follows:

1. Arithmetic Operators

These operators perform mathematical calculations.

Operator Description Example
+ Addition SELECT 10 + 5;
- Subtraction SELECT 10 - 5;
* Multiplication SELECT 10 * 5;
/ Division SELECT 10 / 5;
% Modulo (remainder of division) SELECT 10 % 3;

2. Comparison Operators

These operators compare two values and return a boolean result (TRUE, FALSE, or UNKNOWN).

Operator Description Example
= Equal to SELECT * FROM Products WHERE Price = 50.00;
> Greater than SELECT * FROM Products WHERE Price > 50.00;
< Less than SELECT * FROM Products WHERE Price < 50.00;
>= Greater than or equal to SELECT * FROM Products WHERE Price >= 50.00;
<= Less than or equal to SELECT * FROM Products WHERE Price <= 50.00;
<> or != Not equal to SELECT * FROM Products WHERE Price <> 50.00;

3. Logical Operators

These operators combine conditional statements and return TRUE, FALSE, or UNKNOWN.

Operator Description Example
AND Returns TRUE if both conditions are true SELECT * FROM Products WHERE Price > 50 AND Category = 'Electronics';
OR Returns TRUE if at least one condition is true SELECT * FROM Customers WHERE City = 'London' OR City = 'Paris';
NOT Reverses the result of the condition SELECT * FROM Products WHERE NOT Price > 50;

4. Set Operators

These operators combine the result-sets of two or more SELECT statements.

Operator Description Example
UNION Returns unique rows from both result sets SELECT Name FROM Customers UNION SELECT Name FROM Suppliers;
UNION ALL Returns all rows from both result sets, including duplicates SELECT Name FROM Customers UNION ALL SELECT Name FROM Suppliers;
INTERSECT Returns only rows that appear in both result sets SELECT Name FROM Customers INTERSECT SELECT Name FROM Suppliers;
EXCEPT (or MINUS in some RDBMS) Returns rows from the first result set that are not in the second result set SELECT Name FROM Customers EXCEPT SELECT Name FROM Suppliers;

5. Other Operators

Additional operators used for specific purposes.

Operator Description Example
BETWEEN Checks if a value is within a range (inclusive) SELECT * FROM Products WHERE Price BETWEEN 50 AND 100;
LIKE Searches for a pattern in a string SELECT * FROM Customers WHERE City LIKE 'L%';
IN Checks if a value matches any value in a list SELECT * FROM Products WHERE Category IN ('Electronics', 'Clothing');
IS NULL Checks if a value is NULL SELECT * FROM Customers WHERE Email IS NULL;
IS NOT NULL Checks if a value is not NULL SELECT * FROM Customers WHERE Email IS NOT NULL;

Example Usage

Let's say we have a table named Orders with columns OrderID, CustomerID, OrderDate, and TotalAmount.

To find orders placed by CustomerID 101 with a TotalAmount greater than 500:

SELECT OrderID, OrderDate, TotalAmount
FROM Orders
WHERE CustomerID = 101 AND TotalAmount > 500;

Understanding SQL operators is fundamental to writing effective and powerful queries to manipulate and retrieve data from your databases.