SQL Operators

SQL Operators are useful in performing data manipulations or calculations.

Here is a list of the most commonly used SQL Operators, that can be useful while writing SQL queries.

SQL Arithmetic Operators

SQL Arithmetic Operators can be used to perform arithmetic operations like ADD, SUBTRACT, etc., within the SQL queries.

Operator Description Example
+ Add SELECT 20 + 10;
- Subtract SELECT 20 - 10;
* Multiply SELECT 20 * 10;
/ Divide SELECT 20 / 10;
% Modulo SELECT 25 % 10;

SQL Bitwise Operators

SQL Bitwise Operators can be used to perform bit-level operations within SQL queries.

Operator Description Example
& Bitwise AND Live Demo
| Bitwise OR  
^ Bitwise exclusive OR  

SQL Comparison Operators

SQL Comparison Operators can be used to compare values within the SQL queries.

Operator Description Example
= Equal to SELECT * FROM products WHERE price = 20;
> Greater than SELECT * FROM products WHERE price > 20;
< Less than SELECT * FROM products WHERE price < 20;
>= Greater than or equal to SELECT * FROM products WHERE price >= 20;
<= Less than or equal to SELECT * FROM products WHERE price <= 20;
<> Not equal to SELECT * FROM products WHERE price <> 20;

SQL Compound Operators

SQL Compound Operators can be used to perform operations like are useful in performing arithmetic operations like ADD, SUBTRACT, etc.,

These operators calculate the value based on the operator and then assigns it back to the column.

Operator Description Example
+= Add equals SELECT (salary += 100) FROM employees;
-= Subtract equals SELECT (salary -= 100) FROM employees;
*= Multiply equals SELECT (salary *= 2) FROM employees;
/= Divide equals SELECT (salary /= 2) FROM employees;
%= Modulo equals SELECT (salary %= 2) FROM employees;
&= Bitwise AND equals  
|*= Bitwise OR equals  
^-= Bitwise exclusive OR equals  

SQL Logical Operators

SQL Logical Operators can be used to perform logical operations within SQL queries.

Operator Description Example
ALL Sets to TRUE if all of the subquery values meet the condition. SELECT * FROM products
WHERE product_id = ALL (SELECT product_id FROM order_details WHERE quantity = 10);
AND Sets to TRUE if all the conditions separated by AND is TRUE. SELECT * FROM customers WHERE city = 'London' AND country = 'UK';
ANY Sets to TRUE if any of the subquery values meet the condition. SELECT * FROM products
WHERE unit_price > ANY (SELECT unit_price FROM products WHERE unit_price > 50);
BETWEEN Sets to TRUE if the operand is within the range of comparisons. SELECT * FROM products WHERE unit_price BETWEEN 30 AND 60;
EXISTS Sets to TRUE if the subquery returns one or more records. SELECT * FROM suppliers
WHERE EXISTS (SELECT product_name FROM products WHERE products.supplier_id = suppliers.supplier_id AND unit_price > 50);
IN Sets to TRUE if the operand is equal to one of a list of expressions. SELECT * FROM customers WHERE city IN ('Paris', 'London');
LIKE Sets to TRUE if the operand matches a pattern. SELECT * FROM customers WHERE city LIKE 'm%';
NOT Displays a record if the condition(s) is NOT TRUE. SELECT * FROM customers WHERE city NOT IN ('Paris', 'London');
SELECT * FROM customers WHERE city NOT LIKE 'm%';
OR Sets to TRUE if any of the conditions separated by OR is TRUE. SELECT * FROM customers WHERE city = 'London' OR country = 'UK';
SOME Sets to TRUE if any of the subquery values meet the condition. SELECT * FROM products
WHERE unit_price > SOME (SELECT unit_price FROM products WHERE unit_price > 50);

Overall

We now know the list of all the available SQL operators.

Related Links