SQL Operators
SQL (Structured Query Language) operators are essential components of SQL queries and expressions, serving various purposes in data manipulation, comparison, and filtering. Operators allow you to perform mathematical calculations, combine strings, compare values, and more.
Arithmetic Operators
Arithmetic operators in SQL are used for performing mathematical calculations on numeric data types. These operators include:
Addition (+)
The addition operator combines two numeric values and returns their sum.
SELECT price + tax AS total_price
FROM products;
In this example, the + operator calculates the total price of products by adding the base price and tax.
Subtraction (-)
The subtraction operator subtracts one numeric value from another.
SELECT quantity - sold AS remaining_stock
FROM inventory;
Here, the - operator computes the remaining stock by subtracting the quantity sold from the initial inventory.
Multiplication (*)
The multiplication operator multiplies two numeric values together.
SELECT price * quantity AS total_cost
FROM orders;
This query uses the * operator to calculate the total cost of items in an order by multiplying the price and quantity.
Division (/)
The division operator divides one numeric value by another.
SELECT revenue / customers AS average_revenue_per_customer
FROM sales;
In this case, the / operator computes the average revenue per customer by dividing total revenue by the number of customers.
Modulus (%)
The modulus operator calculates the remainder when one numeric value is divided by another.
SELECT order_id, order_total % 100 AS remainder
FROM orders;
Here, the % operator is used to find the remainder when the order total is divided by 100.
Comparison Operators
Comparison operators are used to compare values in SQL expressions, returning a Boolean result (true or false). These operators include:
Equal (=)
The equal operator checks if two values are equal.
SELECT product_name
FROM products
WHERE category = 'Electronics';
In this query, the = operator is used to filter products where the category is equal to ‘Electronics.’
Not Equal (<> or !=)
The not equal operator checks if two values are not equal.
SELECT employee_name
FROM employees
WHERE department_id <> 3;
The <> operator filters employees whose department ID is not equal to 3.
Greater Than (>)
The greater than operator checks if one value is greater than another.
SELECT product_name
FROM products
WHERE price > 100;
Here, the > operator filters products with a price greater than 100.
Less Than (<)
The less than operator checks if one value is less than another.
SELECT customer_name
FROM customers
WHERE registration_date < '2023-01-01';
This query uses the < operator to filter customers who registered before January 1, 2023.
Greater Than or Equal To (>=)
The greater than or equal to operator checks if one value is greater than or equal to another.
SELECT order_id
FROM orders
WHERE order_date >= '2023-06-01';
The >= operator filters orders placed on or after June 1, 2023.
Less Than or Equal To (<=)
The less than or equal to operator checks if one value is less than or equal to another.
SELECT product_name
FROM products
WHERE stock_quantity <= 10;
In this example, the <= operator filters products with a stock quantity of 10 or less.
Logical Operators
Logical operators allow you to combine multiple conditions in SQL queries. These operators include:
AND
The AND operator combines two or more conditions and returns true if all conditions are true.
SELECT product_name
FROM products
WHERE price > 50 AND stock_quantity > 0;
Here, the AND operator filters products with a price greater than 50 and a positive stock quantity.
OR
The OR operator combines two or more conditions and returns true if at least one condition is true.
SELECT customer_name
FROM customers
WHERE city = 'New York' OR city = 'Los Angeles';
The OR operator filters customers who are either from New York or Los Angeles.
NOT
The NOT operator negates a condition, returning the opposite Boolean value.
SELECT employee_name
FROM employees
WHERE NOT department_id = 3;
In this query, the NOT operator selects employees who do not belong to department 3.
String Operators
String operators in SQL are used for manipulating text values. These operators include:
Concatenation (|| or CONCAT())
The concatenation operator (||) or the CONCAT() function is used to combine two or more strings.
SELECT first_name || ' ' || last_name AS full_name
FROM employees;
This query concatenates the first name and last name of employees to create their full names.
LIKE
The LIKE operator is used for pattern matching within strings. It is often used with wildcard characters % (matches any sequence of characters) and _ (matches any single character).
SELECT product_name
FROM products
WHERE product_name LIKE 'Laptop%';
The LIKE operator filters products with names starting with ‘Laptop.’
Special Operators
SQL also provides special operators that serve unique purposes:
IS NULL
The IS NULL operator checks if a value is null (lacking a value).
SELECT order_id
FROM orders
WHERE shipping_address IS NULL;
This query filters orders with no specified shipping address.
IS NOT NULL
The IS NOT NULL operator checks if a value is not null.
SELECT customer_name
FROM customers
WHERE email IS NOT NULL;
The IS NOT NULL operator selects customers with specified email addresses.
SQL operators are fundamental tools for manipulating and comparing data in relational databases. Whether you’re performing mathematical calculations, comparing values, or combining conditions, understanding and effectively using SQL operators is essential for crafting precise and powerful database queries. By mastering these operators, you can unlock the full potential of SQL for data manipulation and analysis.