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

OPERATOROPERATIONEXPLANATION
1+UNARY POSITIVETo make the operand or a value positive.
2–UNARY NEGATIVETo negate the operand or make a value negative.

Binary Arithmetic Operators

OPERATOROPERATIONEXPLANATION
3+ADDITIONAdds up the operand values specified on either side of the operator.
4–SUBTRACTIONPerforms subtraction of right hand side value from the left hand side value.
5*MULTIPLICATIONMultiplies the operand values present on either side of operator.
6/DIVISIONPerforms division of left hand side value by right hand side value and returns the quotient.
7%MODULUSPerforms 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).

 OPERATOROPERATIONEXPLANATION
1=Equality testPerforms 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 testPerforms 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 testTests 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 testTests 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 testEvaluates 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 testEvaluates 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                  
 ALLALL 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.
 ANDAND 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.
 ANYANY 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.
 ININ 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 ININ 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.
 OROR 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.
 SOMESOME 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 NULLThis 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 NULLIt can be specified in a where clause of a SQL query which then returns the rows that do not have nulls.
 EXISTSIt 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 EXISTSIt 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

  1. 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.

 OPERATOREXPLANATION
1UNIONThis operator returns results consisting of all the distinct (Eliminates duplicates) rows selected by either of the queries.
2UNION ALLIt functions similarly to UNION except that it returns the duplicates as well.
3INTERSECT or INTERSECT ALLINTERSECT operator returns the result consisting of the rows that are common in two or more select queries.
4MINUSIt 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

  1. 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

 OPERATOREXPLANATION
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.
2TERNARYBasic 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.  
3PRIORThis 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.

  1. 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

SQL Operators | Arithmetic, Comparison, SET & Logical OperatorsSumit ThakurUncategorizedAs 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...Let's Define DBMS