SQL operators are essential for performing various operations within SQL queries. They can be categorized into several types, including arithmetic, comparison, logical, and others. Here’s a detailed overview:
Press enter or click to view image in full size
1. Arithmetic Operators
These operators perform mathematical operations:
Addition (+): Adds two numeric values.
SELECT price + tax AS total_price FROM products;
Subtraction (-): Subtracts one numeric value from another.
SELECT price - discount AS net_price FROM products;
Multiplication (*): Multiplies two numeric valuesSELECT quantity * unit_price AS total_value FROM orders;
Division (/): Divides one numeric value by another.
SELECT total_amount / quantity AS average_amount FROM sales;
Modulus (%) (MySQL and some other databases): Returns the remainder of a division.
SELECT total_amount % 100 AS remainder FROM transactions;
Exponentiation: SQL does not have a built-in exponentiation operator. Instead, use functions like POWER() or POW().
SELECT POWER(number, 3) AS cube FROM numbers;
2. Comparison Operators
These operators compare two values and return a boolean result:
Equal to (=): Checks if two values are equal.
SELECT * FROM employees WHERE department_id = 10;
Not equal to (<> or !=): Checks if two values are not equal.
SELECT * FROM products WHERE status <> 'discontinued';
Greater than (>): Checks if the first value is greater than the second.
SELECT * FROM orders WHERE order_date > '2024-01-01';
Less than (<): Checks if the first value is less than the second.
SELECT * FROM products WHERE price < 100;
Greater than or equal to (>=): Checks if the first value is greater than or equal to the second.
SELECT * FROM employees WHERE salary >= 50000;
Less than or equal to (<=): Checks if the first value is less than or equal to the second.
SELECT * FROM students WHERE age <= 18;
BETWEEN: Checks if a value falls within a range.
SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';
IN: Checks if a value is in a set of values.
SELECT * FROM products WHERE category_id IN (1, 2, 3);
LIKE: Searches for a specified pattern.
SELECT * FROM customers WHERE name LIKE 'A%';
IS NULL: Checks if a value is NULL.
SELECT * FROM employees WHERE manager_id IS NULL;
IS NOT NULL: Checks if a value is not NULL.
SELECT * FROM orders WHERE shipped_date IS NOT NULL;
Get All-in-one StudyPoint’s stories in your inbox
Join Medium for free to get updates from this writer.
3. Logical Operators
These operators combine multiple conditions:
AND: Combines multiple conditions where all must be true.
SELECT * FROM employees WHERE department_id = 10 AND salary > 50000;
OR: Combines multiple conditions where at least one must be true
SELECT * FROM products WHERE category = 'Electronics' OR category = 'Appliances';
NOT: Negates a condition.
SELECT * FROM orders WHERE NOT (status = 'Shipped');
4. Other Operators
CONCAT: Concatenates multiple strings into one.
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
COALESCE: Returns the first non-NULL value in a list.
SELECT COALESCE(phone_number, 'No phone number') AS contact FROM customers;
CASE: Provides conditional logic in SQL queries.
SELECT name, CASE WHEN salary < 30000 THEN 'Low' WHEN salary BETWEEN 30000 AND 70000 THEN 'Medium' ELSE 'High' END AS salary_level FROM employees;
NULLIF: Returns NULL if two expressions are equal; otherwise, it returns the first expression.
SELECT NULLIF(total_amount, 0) AS adjusted_amount FROM transactions;
IFNULL (MySQL) / ISNULL (SQL Server): Replaces NULL with a specified value.
SELECT IFNULL(phone_number, 'No phone number') AS contact FROM customers;
5. String Operators
String Concatenation (|| or +): Concatenates strings. Note that the operator can vary by database.
SELECT first_name || ' ' || last_name AS full_name FROM employees;
6. Bitwise Operators (SQL Server, MySQL)
Bitwise AND (&): Performs a bitwise AND operation.
SELECT value1 & value2 AS result FROM bitwise_table;
Bitwise OR (|): Performs a bitwise OR operation.
SELECT value1 | value2 AS result FROM bitwise_table;
Bitwise XOR (^): Performs a bitwise XOR opera
SELECT value1 ^ value2 AS result FROM bitwise_table;
Bitwise NOT (~): Performs a bitwise NOT operation.
