SQL Operators | Arithmetic, Comparison & Logical Operators
As a SQL developer, often there are scenarios when you tend to manipulate and retrieve the data in a specific way. In other words, you would want to perform various operations. And knowing SQL operators is the key solution to all such requirements. So let us first understand
What is an Operator?
An operator in SQL is a reserved keyword or a symbol (special character) that operates up on the operands (or a set of operands) to perform various operations to return a result.
What is an Operand?
An operand can be defined as the data item or an argument that is used by an operator to perform different operations like arithmetic, logical, comparison, etc.
SQL Arithmetic Operators
Arithmetic operators operate on numeric operands. These are mainly used for mathematical operations such as addition, subtraction, etc.
There are two types of arithmetic operators as shown below:
1. Unary Arithmetic Operators:
These operators act upon only one operand. The format followed by the Unary operator is ‘OPERATOR OPERAND’.
2. Binary Arithmetic Operators:
These operators act upon two operands. The format followed by the Binary operator is ‘OPERAND OPERATOR OPERAND’.
Unary Arithmetic Operators
OPERATOR | OPERATION | EXPLANATION | |
---|---|---|---|
1 | + | UNARY POSITIVE | To make the operand or a value positive. |
2 | – | UNARY NEGATIVE | To negate the operand or make a value negative. |
Binary Arithmetic Operators
OPERATOR | OPERATION | EXPLANATION | |
---|---|---|---|
3 | + | ADDITION | Adds up the operand values specified on either side of the operator. |
4 | – | SUBTRACTION | Performs subtraction of right hand side value from the left hand side value. |
5 | * | MULTIPLICATION | Multiplies the operand values present on either side of operator. |
6 | / | DIVISION | Performs division of left hand side value by right hand side value and returns the quotient. |
7 | % | MODULUS | Performs division of left hand side value by right hand side value and returns the remainder. |
For a better understanding of the usage of Arithmetic operators in the SQL queries, you can refer the examples provided below.
1.Unary Positive (+)
Select +10 as VALUE from dual;
Output:
2. Unary Negative (-)
Select –20 as VALUE from dual;
Output:
3. Addition (+)
Select 10+20 as VALUE from dual;
Output:
4. Subtraction (-)
Example 1:
Select 10-20 as VALUE from dual;
Output:
Example 2:
Select 20-10 as VALUE from dual;
Output:
5. Multiplication (*)
Select 10*20 as VALUE from dual;
Output:
6. Division (/)
Example 1:
Select 10/20 as VALUE from dual;
Output:
Example 2:
Select 20/10 as VALUE from dual;
Output:
7. Modulus (%)
Example 1:
Select 10%20 as VALUE from dual;
Output:
Example 2:
Select 20%10 as VALUE from dual;
Output:
SQL Comparison Operators
Comparison operators compare two operand values or can also be used in conditions where one expression is compared to another that often returns a result (Result can be true or false).
OPERATOR | OPERATION | EXPLANATION | |
1 | = | Equality test | Performs the test on two operand values to check if they are equal. If they are equal, condition (in the where clause) becomes true and returns the corresponding rows. |
2 | <> or != or ^= | Inequality test | Performs the test on two operand values to check if they are not equal. If they are not equal, returns true else returns false. |
3 | > | Greater than test | Tests if the left hand side operand value is greater than that of operand value on the right hand side. If yes, returns true. Another case is if you use it in where clause of a select query, then it returns all those rows having column value greater than the specified value. |
4 | < | Less than test | Tests if the left hand side operand value is less than that of operand value on the right hand side. If yes, returns true. Another case where you can use it in where clause of a select query. In such a case, it returns all those rows having column values less than the specified value. |
5 | <= | Less than or equal to test | Evaluates if the left hand side operand value is less than or equal to the operand value on the right hand side. If yes, returns true. |
6 | >= | Greater than or equal to test | Evaluates if the left hand side operand value is greater than or equal to the operand value on the right hand side. If yes, returns true. |
For a better understanding of the usage of comparison operators in the SQL queries, you can refer to the examples provided below.
Consider the table below which is being used as a reference for the next examples.
Sample Table – FACULTY_DETAILS
1.Equal to (=)
Example 1: [Scenario of retrieving rows with the value having NUMBER datatype]
SELECT * FROM FACULTY_DETAILS WHERE FACULTY_ID = 103;
Output
Example 2: [Scenario of retrieving rows with the value having VARCHAR2 datatype]
SELECT * FROM FACULTY_DETAILS WHERE FIRST_NAME = ‘MARK’;
Output
2. Not Equal to (<> or != or ^=)
SELECT * FROM FACULTY_DETAILS WHERE SALARY != 20000 OR
OR SELECT * FROM FACULTY_DETAILS WHERE SALARY <> 20000;
OR SELECT * FROM FACULTY_DETAILS WHERE SALARY ^= 20000;
Output
3. Greater than (>)
SELECT * FROM FACULTY_DETAILS WHERE SALARY > 20000;
Output
4. Less than (<)
SELECT * FROM FACULTY_DETAILS WHERE SALARY < 20000;
Output
5. Less than or equal to (<=)
SELECT * FROM FACULTY_DETAILS WHERE SALARY <= 30000;
Output
6. Greater than or equal to (>=)
SELECT * FROM FACULTY_DETAILS WHERE SALARY => 30000;
Output
SQL Logical Operators
Logical operators are the special type of operators that helps us to retrieve data from the database with even more specificity.
OPERATOR | EXPLANATION | |||
ALL | ALL is used to compare a value with all other values in the set. This set of values can either be a list or the result of the subquery. ‘ALL’ has to be preceded with any of the comparison operators always. Suppose, a query returns no rows then it evaluates to be TRUE. | |||
AND | AND permits the occurrence of multiple conditions in a SQL query. Only those rows are returned that satisfy all the conditions mentioned in a SQL statement. | |||
ANY | ANY is used to compare a value with any of the values specified either in a list or in the result of the subquery. ‘ANY’ has to be preceded with any of the comparison operators always. Suppose, a query returns no rows then it evaluates to be FALSE. | |||
BETWEEN ‘x’ and ‘y’ | It returns the rows which fall into a specified range where x is the minimum value and y is the maximum value (Both maximum and minimum values are included). It is equivalent to saying the range is greater than or equal to x and less than or equal to y. | |||
NOT BETWEEN ‘x’ and ‘y’ | It returns the rows which do not fall into a specified range where x is the minimum value and y is the maximum value. It is equivalent to saying the range is not greater than or equal to x and not less than or equal to y. | |||
IN | IN is equivalent to ‘=ANY’. It compares a particular value to another set of values mentioned in list separated by commas or a subquery that returns the set of values. If matches are found, then it returns all such rows. | |||
NOT IN | IN is equivalent to ‘!=ANY’. It compares a particular value to another set of values mentioned in a list separated by commas or a subquery that returns the set of values. If matches are found, then it returns all other rows except the ones for which match has been found. | |||
LIKE | LIKE operator uses the wildcard notations ‘%’ and ‘_’ to search for specific values in the column. ‘%’ represents zero, one or multiple characters and ‘_’ represents a single character. Note that ‘*’ represents zero, one, or multiple characters where as ‘?’ represents a single character in MS Access. | |||
OR | OR permits the occurrence of multiple conditions in a SQL query. All those rows are returned which satisfy any of the conditions mentioned in a SQL statement. | |||
SOME | SOME is used to compare a value with any of the values specified either in a list or in the result of the subquery. ‘SOME’ has to be preceded with any of the comparison operators always. This operator functions similar to ANY. | |||
IS NULL | This operator is used when there is a need to deal with NULL values. We can specify this as a condition in where clause of the SQL query which returns the rows (if present) having column value equal to NULL. | |||
IS NOT NULL | It can be specified in a where clause of a SQL query which then returns the rows that do not have nulls. | |||
EXISTS | It compares a specified column value with that of column values present in another table or a subquery result along with the other mentioned criteria. If matches are found, it returns all such rows. | |||
NOT EXISTS | It compares a specified column value with that of column values present in another table or a subquery result along with the other mentioned criteria. It returns all other rows except the ones that do exist as a result of the subquery. | |||
For a better understanding of the usage of Logical operators in SQL queries, you can check out the examples provided below.
Consider the tables “FACULTY_DETAILS” and “JOBS” below that is being used as a reference for the next examples.
Sample Table 1 – FACULTY_DETAILS
Sample Table 2 – JOBS
- ALL
Example 1:
SELECT * FROM FACULTY_DETAILS WHERE SALARY > ALL (SELECT SALARY FROM FACULTY_DETAILS WHERE FACULTY_ID = 104);
Referring to the above statement, we know that the subquery returns one record with SALARY = 37000. So, the main query returns all such records whose SALARY is greater than 37000.
Output
Example 2:
SELECT * FROM FACULTY_DETAILS WHERE SALARY > ALL (SELECT SALARY FROM FACULTY_DETAILS WHERE FACULTY_ID < 104);
This time the subquery returns four records (with SALARY = 15000, 16000, 18000, 20000). So, the main query returns all such records whose SALARY is greater than all of those returned from the subquery.
Output
Example 3:
SELECT * FROM FACULTY_DETAILS WHERE SALARY < ALL (SELECT SALARY FROM FACULTY_DETAILS WHERE FACULTY_ID > 104);
In this case, the subquery returns three records (with SALARY = 30000, 43000, 60000). So, the main query returns all such records whose SALARY is less than all of those returned from the subquery.
Output
2. AND
SELECT * FROM FACULTY_DETAILS WHERE FACULTY_ID = 104 AND JOB_ID = ‘SF’;
In the above statement, there are two conditions in the where clause. AND Operator makes sure that only those rows are returned that satisfy both the conditions. Likewise, you can specify as many conditions as you want.
Output
3. ANY
Example 1:
Here, the subquery returns three records (with SALARY = 30000, 43000, 60000). So, the main query returns all such records whose SALARY is greater than any one value of those returned from the subquery.
SELECT * FROM FACULTY_DETAILS WHERE SALARY > ANY (SELECT SALARY FROM FACULTY_DETAILS WHERE FACULTY_ID > 104);
Output
Example 2:
SELECT * FROM FACULTY_DETAILS WHERE SALARY = ANY (SELECT SALARY FROM FACULTY_DETAILS WHERE FACULTY_ID > 104);
In this scenario, the subquery returns three records (with SALARY = 30000, 43000, 60000). So, the main query returns all such records which are returned as a result of the subquery.
Output
Example 3:
SELECT * FROM FACULTY_DETAILS WHERE SALARY < ANY (SELECT SALARY FROM FACULTY_DETAILS WHERE FACULTY_ID > 105);
Output
4. BETWEEN X AND Y
SELECT * FROM FACULTY_DETAILS WHERE SALARY BETWEEN 20000 AND 70000;
Output
5. Not Between X AND Y
SELECT * FROM FACULTY_DETAILS WHERE SALARY NOT BETWEEN 21000 AND 70000;
Output
6. IN
SELECT * FROM FACULTY_DETAILS WHERE FACULTY_ID IN (103,105);
Returning the rows with the column values of NUMBER datatype specified in the list.
Output
SELECT * FROM FACULTY_DETAILS WHERE JOB_ID IN (‘SF’,‘JF’)
Returning the rows with the column values of VARCHAR2 datatype specified in the list.
Output
7. NOT IN
SELECT * FROM FACULTY_DETAILS WHERE FACULTY_ID NOT IN (103,105)
Notice that all those rows are returned except the ones that are specified in the argument list.
Output
8. LIKE
Example 1:
SELECT * FROM FACULTY_DETAILS WHERE FIRST_NAME LIKE ‘LU%’;
The above query returns the rows that have FIRST_NAME starting with ‘Lu’.
Output
Example 2:
SELECT * FROM FACULTY_DETAILS WHERE FIRST_NAME LIKE ‘%s’;
This query returns the rows that have FIRST_NAME ending with ‘s’.
Output
Example 3:
SELECT * FROM FACULTY_DETAILS WHERE JOB_ID LIKE ‘S_’;
Here, it uses the underscore wildcard which is a placeholder for a single character. Returns all the rows that whose JOB_ID starts with S and ‘_’ can be replaced with any one character.
Output
9. OR
SELECT * FROM FACULTY_DETAILS WHERE JOB_ID = ‘JF’ OR FACULTY_ID = 104;
It returns all the rows that satisfy at-least one among all the given conditions. In other words, it need not satisfy all conditions. Even if one condition is satisfied it will return such a row.
Output
10. SOME
Example 1:
SELECT * FROM FACULTY_DETAILS WHERE SALARY > SOME (SELECT SALARY FROM FACULTY_DETAILS WHERE FACULTY_ID > 104);
Output
Example 2:
SELECT * FROM FACULTY_DETAILS WHERE SALARY > SOME (SELECT SALARY FROM FACULTY_DETAILS WHERE FACULTY_ID = 104);
Output
11. IS NULL
SELECT * FROM FACULTY_DETAILS WHERE FIRST_NAME IS NULL;
Since there are no records in our reference table that has FIRST_NAME as null, it returns no rows.
Output
No Data Found
12. IS NOT NULL
SELECT * FROM FACULTY_DETAILS WHERE FIRST_NAME IS NOT NULL;
All the records in our table have valid names in the FIRST_NAME column, there is none that has null values. Hence, all the records are returned.
Output
13. EXISTS
SELECT * FROM FACULTY_DETAILS
WHERE EXISTS (SELECT DESIGNATION FROM JOBS WHERE FACULTY_DETAILS.JOB_ID = JOBS.JOB_ID AND SALARY < 20000);
Output
14. NOT EXISTS
SELECT * FROM FACULTY_DETAILS
WHERE NOT EXISTS (SELECT DESIGNATION FROM JOBS WHERE FACULTY_DETAILS.JOB_ID = JOBS.JOB_ID AND SALARY < 20000);
Output
SQL SET Operators
SET operators produce a meaningful result after combining the results of two or more select queries.
OPERATOR | EXPLANATION | |
1 | UNION | This operator returns results consisting of all the distinct (Eliminates duplicates) rows selected by either of the queries. |
2 | UNION ALL | It functions similarly to UNION except that it returns the duplicates as well. |
3 | INTERSECT or INTERSECT ALL | INTERSECT operator returns the result consisting of the rows that are common in two or more select queries. |
4 | MINUS | It eliminates the rows that are common in both the select queries (Result of queries specifically), returns the remaining ones from the first select query which are not present in the second select query. |
For a better understanding of usage of SET operators, you can refer to the examples provided below.
Consider the tables “STUDENTS_TEAM_1” and “STUDENTS_TEAM_2” below that are being used as a reference for the next examples.
Sample Table 1 – STUDENTS_TEAM_1
Sample Table 1 – STUDENTS_TEAM_2
- UNION
SELECT * FROM STUDENTS_TEAM_1
UNION
SELECT * FROM STUDENTS_TEAM_2;
As seen below, the UNION selects the distinct rows from both the tables and there are no duplicates.
Output
2. UNION ALL
SELECT * FROM STUDENTS_TEAM_1
UNION ALL
SELECT * FROM STUDENTS_TEAM_2;
Checking from below, UNION ALL selects all the rows from both the tables including duplicates.
Output
3. INTERSECT OR INTERSECT ALL
SELECT * FROM STUDENTS_TEAM_1
INTERSECT
SELECT * FROM STUDENTS_TEAM_2;
It only returns those rows that were present in both STUDENTS_TEAM_1 and STUDENTS_TEAM_2 tables.
Output
4. MINUS
SELECT * FROM STUDENTS_TEAM_1
MINUS
SELECT * FROM STUDENTS_TEAM_2;
As you refer to both tables, you understand that there were only two rows left in the first table after the common ones were eliminated. Hence, the output appears as below.
Output
SQL OTHER Operators
OPERATOR | EXPLANATION | |
1 | OUTER JOIN (+) | This operator has a greater usage in the oracle database. It suggests that the preceding column is an outer join column (in a join) which means it returns the rows of both the tables included in the select query that satisfies the given join condition along with rows that do not satisfy the condition. |
2 | TERNARY | Basic syntax for the ternary operator is ‘CONDITION? (STATEMENT 1): (STATEMENT 2)’. It is as simple as saying that the condition on being true executes statement 1 or on being false executes statement 2. The keywords CASE, WHEN, THEN, ELSE are used to form a ternary operator which you can refer to the corresponding example in the later section. |
3 | PRIOR | This is a special operator in oracle that estimates immediately followed expression for a parent row of a current row in the hierarchy. When combined with CONNECT BY it helps in defining how the child row is related to the parent row. |
For a better understanding of the usage of above operators, you can refer to the examples provided below.
- OUTER JOIN (+)
Sample Table 1 – FACULTY_DETAILS
Sample Table 2 – JOBS
SELECT FD.FACULTY_ID, FD.FIRST_NAME, FD.EMAIL, JB.JOB_ID, JB.DESIGNATION, JB.WORKING_HOURS
FROM FACULTY_DETAILS FD, JOBS JB
WHERE FD.JOB_ID = JB.JOB_ID (+);
Look at the below output, all the rows in the first table (FACULTY_DETAILS) are included even if there are no corresponding rows in the second table (JOBS) having the same JOB_ID. For such cases, null values are inserted.
Output
2. TERNARY OPERATOR
Sample Table – EMPLOYEES
SELECT EMP_NAME,
(CASE WHEN EMP_GENDER = ‘M’
THEN ‘Male T-Shirt with company LOGO to be provided’
ELSE ‘Female T-Shirts with company LOGO to be provided’
END)
AS TYPE_TSHIRT FROM EMPLOYEES;
Output
3. PRIOR
PRIOR is used along with other keywords or clauses such as START WITH, CONNECT BY. START WITH can contain a value or a subquery that returns a result. CONNECT BY indicates the relationship between child and parent row and connect by condition can by any normal condition.
Use the below queries to create a table and insert the data for this example.
CREATE TABLE FAMILY(
ID INT,
NAME VARCHAR2(100),
PARENT_ID INT
);
INSERT ALL
INTO FAMILY VALUES( 1, ‘GRANDMOTHER’, NULL)
INTO FAMILY VALUES( 2, ‘MOTHER’, 1)
INTO FAMILY VALUES( 3, ‘DAUGHTER’, 2)
INTO FAMILY VALUES( 4, ‘GRANDDAUGHTER’, 3)
SELECT 1234 FROM DUAL;
Sample Table – FAMILY
Example 1:
Here, we start searching for hierarchy in the lower direction starting with ‘GRANDMOTHER’ (highest). Hence, it has returned all the rows in the given family hierarchy.
SELECT LEVEL, F.*
FROM FAMILY F
START WITH NAME = ‘GRANDMOTHER’
CONNECT BY PRIOR ID = PARENT_ID;
Output
Example 2:
This is a case where we start with ‘MOTHER’. Hence, the result contains the rows followed by ‘MOTHER’ in the family hierarchy.
SELECT LEVEL, F.*
FROM FAMILY F
START WITH NAME = ‘MOTHER’
CONNECT BY PRIOR ID = PARENT_ID;
Output
Example 3:
In this example, we start with ‘DAUGH%’. Remember the LIKE operator using wildcard % that enables us to search results where % could be zero or more characters. Hence, the result returned two records satisfying the given condition.
SELECT LEVEL, F.*
FROM FAMILY F
START WITH NAME LIKE ‘DAUGH%’
CONNECT BY PRIOR ID = PARENT_ID;
Output
Leave a Reply