# SQL Operators

## Overview

To retrieve the data stored in the databases by performing specific logical or mathematical computations, we use SQL Operators. In SQL, an operator is a reserved keyword or special symbol which can be used to perform some specific logical and mathematical computation on operands.

## What is SQL Operator?

To retrieve the data stored in the databases by performing specific logical or mathematical computations, we use SQL Operators. Let us learn about SQL operators in detail.

**Note:**
Suppose we have a statement: answer = x or y.
Here, x and y are operands.
or is an operator that operates on the operands.

In SQL, an operator is a reserved keyword or special symbol which can be used to perform some specific logical and mathematical computation on operands. We use the SQL operators with the SQL WHERE clause for retrieving results based on some specific logical or mathematical computation.

## Types of SQL Operators

We can categorize the SQL operators into six categories:

- SQL Arithmetic Operators.
- SQL Comparison Operators.
- SQL Logical Operators.
- SQL Set Operators.
- SQL Unary Operators.
- SQL Bitwise Operators.

Let us learn about each one of the above-stated operators in detail.

### SQL Arithmetic Operators

The SQL Arithmetic operator is used to perform arithmetic computations and operations on the two operands or the numerical data present in the tables of the database. The Arithmetic operators are capable of performing all arithmetic operations like addition, subtraction, multiplication, division, and modulus on the operands of the operator.

The SQL Comparison Operators are used with the WHERE clause.

Following is the list of arithmetic operators present in SQL:

Operator | Description |
---|---|

+ | The Addition operator is used to perform addition operations on the operands. |

- | The Subtraction operator is used to perform subtraction operations on the operands (to get the difference between operands). |

* | The Multiplication operator is used to perform multiplication operations on the operands. |

/ | The Division operator is used to perform division operations on the operands. |

% | The Modulus operator is used to perform modulus operation on the operands (to get the remainder when one operand is divided by the other). |

Let us take an example to understand the Arithmetic operators in a better way.

Suppose we have a database of students. The student table has 3 columns namely student ID, Physics (marks in Physics), and Chemistry (marks in Chemistry).

Overview of the student table:

ID | Physics | Chemistry |
---|---|---|

1 | 86 | 92 |

2 | 85 | 91 |

3 | 73 | 98 |

Now, let us perform some arithmetic operations using arithmetic operators.

**Output:**

In this query, we have added the Physics and Chemistry marks as the Total Marks of the above table.

Similarly, we can use various other arithmetic operators.

### SQL Comparison Operators

The SQL Comparison operator is used to compare the two operands or the two data values present in the database tables. The comparison operators are also capable of comparing one expression to the other expression.

The SQL Comparison Operators are used with the WHERE clause.

Following are the list of comparison operators present in SQL:

Operator | Description |
---|---|

= | The Equal operator is used to show data that matches with the provided value in the query. |

> | The Greater Than operator is used to show data that are greater than the provided value in the query. |

< | The Lesser Than operator is used to show data that are lesser than the provided value in the query. |

>= | The Greater Than Equals To operator is used to show data that are greater than and equal to the provided value in the query. |

<= | The Lesser Than Equals To operator is used to show data that are lesser than and equal to the provided value in the query. |

<> or!= | The Equal Not operator is used to show data that do not match with the provided value in the query. |

Let us take an example to understand the comparison operators in a better way.

Suppose we have a database of students. The student table has $4$ columns namely student ID, Name, Physics (marks in Physics), and Chemistry (marks in Chemistry).

Overview of the student table:

ID | Name | Physics | Chemistry |
---|---|---|---|

1 | Aman | 86 | 92 |

2 | Sushant | 91 | 91 |

3 | Saumya | 98 | 98 |

Let us perform some comparison operations using comparison operators.

In this query, we have selected the details of the student(s) having 88 marks in Physics.

We have selected the details of the student(s) having marks greater than or equal to 91 in Chemistry.

Similarly, we can use various other comparison operators.

### SQL Logical Operators

The SQL Logical operator is used to perform boolean (TRUE or FALSE) operations on the operands or the two data values present in the database tables. The Logical operators return True if both the operands follow the logical condition.

The SQL Logical Operators are used with the WHERE clause.

Following is the list of the common logical operators present in SQL:

Operator | Description |
---|---|

AND | The Logical AND operator is used to compare two operands, it returns TRUE when both the operands follow the logical condition provided in the SQL query. |

OR | The Logical OR operator is used to compare two operands, it returns TRUE when any of the two operands follow the logical condition provided in the SQL query. |

NOT | The Logical NOT operator has been used to change the value operand. If the value of the operand is True, it changes the value to False. If the value of the operand is False, it changes the value to True. |

Apart from the AND, OR, and NOT logical operators, we have some special logical operators. The special logical operators are used to select records from the table. The special logical operators are commonly used inside the WHERE clause or with the HAVING statement.

Following are the list of the special logical operators present in SQL:

Operator | Description |
---|---|

ALL | The ALL operator compares the provided value to all the values of a column returned from the sub-query. It selects all the records of an inner SELECT statement. |

ANY | The ANY operator returns records, when any one of the values returned from the sub-query, satisfies the provided condition. It must match with at least one record of the inner query. |

BETWEEN | The BETWEEN operator is used to retrieve records within the provided range. BETWEEN operator can work with numbers, characters, dates, and times. The range is specified using another logical operator AND. |

IN | The IN operator is used to retrieve records that match the set of values separated by commas. We can use multiple OR statements in the place of IN operator. |

EXISTS | The EXISTS operator is used to check if the specified value exists in the result of the inner sub-query or not. It returns True or False based on the results obtained. |

LIKE | The LIKE operator is used to retrieve those records from a table that matches the provided pattern. There are two wild cards % (percentage) and _ (underscore) that are often used with the LIKE operator. The % sign represents 0 or more characters, _ sign represents a single character. |

Let us take some examples to understand the different logical operators in a better way.

By taking the same students' table as above, let us perform some logical operations using logical operators.

We have selected the details of the student(s) having Chemistry marks greater than equal to 75, or the student(s) having Physics marks greater than equal to 85.

The details of the student(s) whose name starts with S will be shown in the output.

Similarly, we can use various other logical operators.

### SQL Set Operators

The SQL Set operators are used to combine two or more similar data present in two or more SQL databases. The SQL set operators merge the result retrieved from two or more queries into a single query result.

The SQL Logical Operators are used with the SELECT statements.

Following is the list of the common set operators present in SQL:

Operator | Description |
---|---|

UNION | The UNION operator merges the result retrieved from two or more SELECT statements into a single query result. The data type and the number of columns that are used with the UNION operator must be the same for each SELECT statement. |

UNION ALL | The UNION ALL operator merges the result retrieved from two or more SELECT statements into a single query result. The UNION ALL operator also shows duplicate or repeated values from both of the queries. |

INTERSECT | The INTERSECT operator is used to fetch the common records from two or more SELECT statements. The data type and the number of columns that are used with the INTERSECT operator must be the same for each SELECT statement. |

MINUS | The MINUS operator combines the result retrieved from two or more SELECT statements but only shows the results from the first data set. |

Let us take some examples to understand the different SQL set operators in a better way.

Suppose we have a database of students. The student table has 4 columns namely student ID, Name, Physics (marks in Physics), and Chemistry (marks in Chemistry).

Suppose we have a database of studentsâ€™ library details as well. The library table has 3 columns namely student ID, Name, and Books. The student ID as the name suggests stored unique student IDs. The Name column contains the names of the students. The Books column contains the number of books acquired by the student.

Overview of the library table:

ID | Name | Books |
---|---|---|

1 | Aman | 7 |

2 | Sushant | 8 |

3 | Saumya | 8 |

4 | Kausiki | 3 |

5 | Aditya | 6 |

Now, let us perform some set operations using set operators.

In this query, we have the output of both the queries merged as a single output Hence, we will get the details of the student(s) whose name starts with 'S' as well as the library details of the students who have acquired more than 7 books.

Similarly, we can use various other set operators.

### SQL Unary Operators

The SQL Unary operators are used to perform the unary operations on an operand or a single data of the database table.

Following are the list of the common unary operators present in SQL:

Operator | Description |
---|---|

Unary Positive | The Unary Positive + operator is used to make the numeric values of a SQL table positive. |

Unary Negative | The Unary Negative - operator is used to make the numeric values of a SQL table negative. |

Unary Bitwise NOT | The Unary Bitwise NOT ~ operator is used to get one's component (inversion of the given number, for example, 10110 to 01001) of a numeric operand. The Unary Bitwise Not operator shifts 0 bit of an operand to 1 bit and vice versa. |

Let us take some examples to understand the different SQL unary operators in a better way.

Suppose we have a database of students. The student table has 3 columns namely student ID, Name, Dues (due amount of the student).

Overview of the student table:

ID | Name | Dues |
---|---|---|

1 | Aman | 186 |

2 | Sushant | 291 |

3 | Saumya | 198 |

4 | Kausiki | 276 |

5 | Aditya | 767 |

Now, let us perform some unary operations using unary operators.

In this query, we have selected the Dues column and we have printed it such that the dues amount is shown as a negative value. (For example, -186, -767 etc.)

Similarly, we can use various other unary operators.

### SQL Bitwise Operators

The SQL Bitwise operators are used to perform the bit operations on the integer values operands of the database table.

Following is the list of the common set operators present in SQL:

Operator | Description |
---|---|

Bitwise AND | The bitwise AND & operator is used to perform the logical AND operation of the provided integer-valued operands. The bitwise AND operator checks every bit of an operand with the other operand. |

Bitwise OR | The bitwise OR l operator is used to perform the logical OR operation of the provided integer-valued operands. The bitwise OR operator checks every bit of an operand with the other operand. |

Let us take some examples to understand the different SQL bitwise operators in a better way.

Suppose we have a database of students. The student table has 4 columns namely student ID, Name, Physics (marks in Physics), and Chemistry (marks in Chemistry).

Overview of the student table:

ID | Name | Physics | Chemistry |
---|---|---|---|

1 | Aman | 86 | 92 |

2 | Sushant | 91 | 91 |

3 | Saumya | 98 | 98 |

4 | Kausiki | 76 | 76 |

5 | Aditya | 67 | 67 |

Now, let us perform some bitwise operations using bitwise operators.

In this query, we have performed the bitwise AND between the student's Physics and Chemistry marks.

In this query, we have performed the bitwise OR between the student's Physics and Chemistry marks.

## Using Parentheses between Operators

Let us take a few more examples to understand how to use parenthesis to bind different SQL operators.

Parenthesis can be used to combine different WHERE clauses to fetch desired query results.

Suppose we have a database of students. The student table has 4 columns namely student ID, Name, Physics (marks in Physics), and Chemistry (marks in Chemistry).

Overview of the student table:

ID | Name | Physics | Chemistry |
---|---|---|---|

1 | Aman | 86 | 92 |

2 | Sushant | 91 | 91 |

3 | Saumya | 98 | 98 |

4 | Kausiki | 76 | 76 |

5 | Aditya | 67 | 67 |

### Simple Query with Parenthesis

We want to fetch the details of the student(s) whose marks in Physics id 67.

So, the SQL query can be:

### Complex SQL Query with Parenthesis

We want to fetch the details of the student(s) whose name starts with 'A' as well as he/she must have scores greater than 85 in Physics or Chemistry.

So, the SQL query can be:

So, to combine various conditions of this complex SQL query, we have used parenthesis.

## Conclusion

- An SQL operator is a reserved keyword or special symbol which can be used to perform some specific logical and mathematical computation on operands.
- The SQL Arithmetic operator is used to perform arithmetic computations and operations on the two operands or the numerical data present in the tables of the database.
- The SQL Comparison operator is used to perform a comparison of the two operands or the two data values present in the database tables.
- The SQL Logical operator is used to perform boolean (TRUE or FALSE) operations on the operands or the two data values present in the database tables.
- The SQL Set operators are used to combine two or more similar data present in two or more SQL databases. It merges the result retrieved from two or more queries into a single query result.
- The SQL Unary operators are used to perform the unary operations on an operand or a single data of the database table.
- The SQL Bitwise operators are used to perform the bit operations on the integer values operands of the database table.