Operators in SQL

Operators in SQL:-

Arithmetic Operators in SQL

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
Operators in sql
  • There is one important difference between 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 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 test. SELECT ENAME “Employee”, JOB “Title” FROM EMP WHERE SAL > 3000;
Less than test. SELECT * FROM PRICE WHERE MINPRICE < 30;
>= Greater than or equal to test. SELECT * FROM PRICE WHERE MINPRICE >= 20;
<= Less than or equal to 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 condition is true Salary > 75000 OR Dept = ‘ICP’
Not NOT Returns TRUE if following condition is false Id NOT IN (2,3)

Logical operators which manipulate the results of conditions are listed in  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

Leave a Reply

Your email address will not be published. Required fields are marked *