SQL Operators

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.

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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.