Free Courses

SQL Operators

Learn about SQL operators in detail.

Updated - 17 May 202213 mins readPublished : 21 Mar 2022
Published : 21 Mar 2022
quiz
Challenge Inside! : Find out where you stand! Try quiz, solve problems & win rewards!

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.

Scope

The article contains topics such as

  • SQL Operators, SQL Arithmetic Operators, SQL Comparison Operators, SQL Logical Operators, SQL Set Operators, SQL Unary Operators, SQL Bitwise Operators.
  • Using Parentheses between operators, Simple Query with parenthesis, Complex SQL Query with parenthesis.

Each of the topics is explained clearly with diagrams and examples wherever necessary.

Introduction

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 are the list of arithmetic operators present in SQL:

OperatorDescription
+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:

IDPhysicsChemistry
18692
28591
37398

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

SELECT Physics + Chemistry as Total_Marks FROM student;

Output:

Total_Marks
178
176
171

In this query, we have added the Physics and Chemistry marks as 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:

OperatorDescription
=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 44 columns namely student ID, Name, Physics (marks in Physics), and Chemistry (marks in Chemistry).

Overview of the student table:

IDNamePhysicsChemistry
1Aman8692
2Sushant9191
3Saumya9898

Let us perform some comparison operations using comparison operators.

SELECT * FROM student WHERE Physics = 86;

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

SELECT * FROM student WHERE Chemistry >= 92;

We have selected the details of the student(s) having marks greater than 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 are the list of the common logical operators present in SQL:

OperatorDescription
ANDThe 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.
ORThe 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.
NOTThe 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:

OperatorDescription
ALLThe 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.
ANYThe 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.
BETWEENThe 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.
INThe 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.
EXISTSThe 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.
LIKEThe 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.

SELECT * FROM student WHERE Physics >= 85 OR Chemistry >= 75;

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.

SELECT * FROM student WHERE Name LIKE 'S%';

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 are the list of the common set operators present in SQL:

OperatorDescription
UNIONThe 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 ALLThe 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.
INTERSECTThe 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.
MINUSThe 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, Books. The student ID as the name suggests stored the 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:

IDNameBooks
1Aman7
2Sushant8
3Saumya8
4Kausiki3
5Aditya6

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

SELECT * FROM student WHERE Name LIKE 'S%'
UNION
SELECT * FROM library WHERE Books >= 7

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 the 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:

OperatorDescription
Unary PositiveThe Unary Positive + operator is used to make the numeric values of a SQL table positive.
Unary NegativeThe Unary Negative - operator is used to make the numeric values of a SQL table negative.
Unary Bitwise NOTThe 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:

IDNameDues
1Aman186
2Sushant291
3Saumya198
4Kausiki276
5Aditya767

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

SELECT -Dues FROM student;

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 the 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 are the list of the common set operators present in SQL:

OperatorDescription
Bitwise ANDThe 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 ORThe 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:

IDNamePhysicsChemistry
1Aman8692
2Sushant9191
3Saumya9898
4Kausiki7676
5Aditya6767

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

SELECT Physics & Chemistry FROM student;

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

SELECT Physics | Chemistry FROM student;

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

Using Parentheses Between Operator

Let us take a few more examples to understand how to use parenthesis to bind different 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:

IDNamePhysicsChemistry
1Aman8692
2Sushant9191
3Saumya9898
4Kausiki7676
5Aditya6767

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:

SELECT *
FROM student
WHERE (Physics = 67)

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:

SELECT *
FROM student
WHERE (Physics > 85 OR Chemistry > 85)
AND (Name LIKE 'S%')

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

Conclusion

  • 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.
  • 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.
Challenge Time!
quiz
quiz
Time to test your skills and win rewards! Note: Rewards will be credited after the next product update.
Free Courses by top Scaler instructors