Operators in SQL

An operator is a symbol which perform an operation. Below are the operators in SQL.

1. Arithmetic operators

Operator	Description	Example
+	Addition	20+30
-	Subtraction	20-30
*	Multiplication	20*30
/	Division	20/30
%	Modulus : Get the remainder	20%30

2. Comparison Operators

Used to compare one expression with another.

Operator	Description	Example
=	Checks for equality	SELECT name FROM emp WHERE sal = 1000;
!=, ^=, <>	Checks for inequality	SELECT name FROM emp WHERE sal != 1000;
>	Greater than test	SELECT name FROM emp WHERE sal > 1000;
<	Less than test	SELECT name FROM emp WHERE sal < 1000;
>=	Greater than or equal to test.	SELECT name FROM emp WHERE sal >= 1000;
<=	Less than or equal to test.	SELECT name FROM emp WHERE sal <= 1000;
IN	"Equivalent to any member of" test.	SELECT name FROM emp WHERE name IN ('Krishna', 'Arjun');
ANY/ SOME	Compares a value to each value in a list or returned by a query.	SELECT * FROM emp WHERE name = SOME ('Krishna','Arjun');
NOT IN	Equivalent to "!= ANY".	SELECT name FROM emp WHERE name NOT IN ('Krishna', 'Arjun');
ALL	Compares a value with every value in a list or returned by a query.	SELECT * FROM emp WHERE sal >= ALL (10000, 30000);
BETWEEN x and y	greater than or equal to x and less than or equal to y.	SELECT name FROM emp WHERE sal BETWEEN 5000 AND 25000;
EXISTS	TRUE if a sub-query returns at least one row.	SELECT * FROM emp WHERE EXISTS (SELECT name FROM EMP WHERE salary=50000);
LIKE	x LIKE y : Returns true, if x matches the pattern y.	SELECT * FROM emp WHERE name LIKE '%K%';
IS NULL	Tests for null	SELECT name FROM emp WHERE lastName IS NULL;
NOT LIKE	x NOT LIKE y : Returns true, if x doesn't matches the pattern y.	SELECT * FROM emp WHERE name NOT LIKE '%K%';
NOT NULL	Tests for not null	SELECT name FROM emp WHERE lastName IS NOT NULL;

3. Logical Operators

Operator	Description	Example
NOT	Returns TRUE if the following condition is FALSE. Returns FALSE if it is TRUE.	SELECT * FROM EMP WHERE NOT (lastName IS NULL);
AND	Returns TRUE if both component conditions are TRUE, else FALSE.	SELECT * FROM EMP WHERE name='Krishna' AND salary=50000
OR	Returns TRUE if either component condition is TRUE, else FALSE	SELECT * FROM EMP WHERE name='Krishna' OR salary=50000

Prevoius                                                 Next                                                 Home