Operators in SQL

Operators in SQL:-

Arithmetic Operators in SQL

• Arithmetic operators manipulate numeric operands

Operator	Symbol	Usage	Result
Addition	+	15 + 5	20
Subtraction	–	15 – 5	10
Multiplication	*	15 * 5	75
Division	/	15 / 5	3

Comparison Operators

Operator	Symbol	Usage	Result
Equal to	=	15 = 5	false
Not equal to	<>	15 <> 5	true
Greater than	>	15 > 5	true
Greater than equal to	>=	15 >= 5	true
Less than	<	15 < 5	false
Less than equal to	<=	15 <= 5	false

• There is one important difference between the Equal To comparison operator in programming languages and SQL. While SQL uses a single ‘=’, programming languages typically use double ‘=’ to distinguish it from the assignment operator.

Operation	Python Operator	SQL Operator
Assignment	=	=
Equality check	==	=

Table – Arithmetic Operators

Operator	Description	Example
+ (unary)	Makes operand positive	SELECT +3 FROM DUAL;
– (unary)	Negates operand	SELECT -4 FROM DUAL;
/	Division (numbers and dates)	SELECT SAL / 10 FROM EMP;
*	Multiplication	SELECT SAL * 5 FROM EMP;
+	Addition (numbers and dates)	SELECT SAL + 200 FROM EMP;
–	Subtraction (numbers and dates)	SELECT SAL – 100 FROM EMP;

Other Comparison Operators in SQL

Operator	Symbol	Usage	Example
Range	BETWEEN <lower limit> AND <upper limit>	Matches value between a range of values (Both inclusive)	Salary BETWEEN 2500 AND 3000
List	IN (List of values)	Matches any of a list of values	Dept IN (‘IVS’, ‘ETA’, ‘ICP’)
String pattern matching	LIKE	Matches a character pattern	SupplierId LIKE ‘S%’
NULL Test	IS NULL	Is a null value	Bonus IS NULL

Comparison Operators in SQL

• Comparison operators used in conditions that compare one expression with another are listed in the table The result of a comparison can be TRUE, FALSE, or UNKNOWN.

Table Comparison Operators

Operator	Description	Example
=	Equality test.	SELECT ENAME “Employee” FROM EMP WHERE SAL = 1500;
!=, ^=, <>	Inequality test.	SELECT ENAME FROM EMP WHERE SAL ^= 5000;
>	Greater than the test.	SELECT ENAME “Employee”, JOB “Title” FROM EMP WHERE SAL > 3000;
<	Less than the test.	SELECT * FROM PRICE WHERE MINPRICE < 30;
>=	Greater than or equal to the test.	SELECT * FROM PRICE WHERE MINPRICE >= 20;
<=	Less than or equal to the test.	SELECT ENAME FROM EMP WHERE SAL <= 1500;
IN	“Equivalent to any member of” test. Equivalent to “=ANY”.	SELECT * FROM EMP WHERE ENAME IN (‘SMITH’, ‘WARD’);
ANY/ SOME	Compares a value to each value in a list or returned by a query. Must be preceded by =, !=, >, <, <= or >=. Evaluates to FASLE if the query returns no rows.	SELECT * FROM DEPT WHERE LOC = SOME (‘NEW YORK’,’DALLAS’);
NOT IN	Equivalent to “!=ANY”. Evaluates to FALSE if any member of the set is NULL.	SELECT * FROM DEPT WHERE LOC NOT IN (‘NEW YORK’, ‘DALLAS’);
ALL	Compares a value with every value in a list or returned by a query. Must be preceded by =, !=, >, <, <= or >=. Evaluates to TRUE if the query returns no rows.	SELECT * FROM emp WHERE sal >= ALL (1400, 3000);
[NOT] BETWEEN x and y	[Not] greater than or equal to x and less than or equal to y.	SELECT ENAME, JOB FROM EMP WHERE SAL BETWEEN 3000 AND 5000;
EXISTS	TRUE if a subquery returns at least one row.	SELECT * FROM EMP WHERE EXISTS (SELECT ENAME FROM EMP WHERE MGR IS NULL);
x [NOT] LIKE y [ESCAPE z]	TRUE if x does

match the pattern y. Within y, the character “%” matches any string of zero or more characters except null. The character “_” matches any single character. Any character following ESCAPE is interpreted literally, useful when y contains a percent (%) or underscore (_).

SELECT * FROM EMP WHERE ENAME LIKE ‘%E%’;

IS [NOT] NULL

Tests for nulls. This is the only operator that should be used to test for nulls.

SELECT * FROM EMP WHERE COMM IS NOT NULL AND SAL > 1500;

Logical Operators in SQL

Operator	Symbol	Usage	Example
And	AND	Returns TRUE if both conditions are true	Salary >= 30000 AND Dept = ‘ETA’
Or	OR	Returns TRUE if any one of the conditions is true	Salary > 75000 OR Dept = ‘ICP’
Not	NOT	Returns TRUE if the following condition is false	Id NOT IN (2,3)

Logical operators which manipulate the results of conditions are listed in the table

Table – Logical Operators

Operator	Description	Example
NOT	Returns TRUE if the following condition is FALSE. Returns FALSE if it is TRUE. If it is UNKNOWN, it remains UNKNOWN.	SELECT * FROM EMP WHERE NOT (job IS NULL) SELECT * FROM EMP WHERE NOT (sal BETWEEN 1000 AND 2000)
AND	Returns TRUE if both component conditions are TRUE. Returns FALSE if either is FALSE; otherwise returns UNKNOWN.	SELECT * FROM EMP WHERE job=’CLERK’ AND deptno=10
OR	Returns TRUE if either component condition is TRUE. Returns FALSE if both are FALSE. Otherwise, returns UNKNOWN.	SELECT * FROM emp WHERE job=’CLERK’ OR deptno=10

 Set Operators

• Set operators that combine the results of two queries into a single result are listed in the table.

Table  Set Operators

Operator	Description	Example
UNION	Returns all distinct rows selected by either query.	SELECT * FROM (SELECT ENAME FROM EMP WHERE JOB = ‘CLERK’ UNION SELECT ENAME FROM EMP WHERE JOB = ‘ANALYST’);
UNION ALL	Returns all rows selected by either query, including all duplicates.	SELECT * FROM (SELECT SAL FROM EMP WHERE JOB = ‘CLERK’ UNION SELECT SAL FROM EMP WHERE JOB = ‘ANALYST’);
INTERSECT	Returns all distinct rows selected by both queries.	SELECT * FROM orders_list1 INTERSECT SELECT * FROM orders_list2
MINUS	Returns all distinct rows selected by the first query but not the second.	SELECT * FROM (SELECT SAL FROM EMP WHERE JOB = ‘PRESIDENT’ MINUS SELECT SAL FROM EMP WHERE JOB = ‘MANAGER’);

So these are the Operators in SQL which are used in SQL

Read the more interesting article: click here

Read the more interesting article in python: click here