SQL Operators: Types, Syntax and Examples

• Introduction to SQL Operators
  • Types of SQL Operators
  • SQL Arithmetic Operators 
  • SQL Logical Operators
  • SQL Compound Operators 
  • SQL Comparison Operators
• Frequently Asked Questions (FAQs)

Introduction to SQL Operators

SQL queries are used by every database administrator and user to manipulate and access the data of database tables and views. Reserved words and characters, which are utilized to carry out arithmetic operations, logical operations, comparison operations, compound operations, etc., are used to manipulate and retrieve the data. 

SQL operators are what make the SQL queries more useful in data retrieval in terms of data filtration. Here, in this blog, we are going to learn about various types of SQL operators and how they work. 

When a WHERE clause is used in a SQL query, the reserved words and characters are known as operators. An operator in SQL can either be a binary operator or a unary operator. When performing a unary operation, the unary operator only needs one operand, whereas when doing a binary operation, the binary operator needs two operands. 

An essential part of defining a condition in a SQL query is connecting two or more conditions, which is done with the help of operators. The role of operators in SQL is to manipulate the data and generate a result set on the basis of the used SQL operator’s functionality. There are different types of SQL operators depending upon the operation they perform. 

Types of SQL Operators

Operators in SQL are categorized on the basis of the operation they are responsible to perform. Different types of SQL operators have been listed below:

1. SQL Arithmetic Operators
2. SQL Logical Operators
3. SQL Compound Operators
4. SQL Comparison Operators
5. SQL bit-wise operators  

We will now learn about each of these SQL operators one by one in detail and understand how these operators work in a query with the help of examples.

SQL Arithmetic Operators 

Like any other arithmetic operator, they also perform simple arithmetic operations such as addition, subtraction, multiplication, division, and modulus.  The operands on which these operators operate are numeric in nature.  Here is a table describing all the SQL Arithmetic Operators along with their functionalities. 

Examples of SQL Arithmetic Operators
To show the ID and MARKS of all the students from the STUDENTS table along with two more columns displaying the value of MARKS+10 and MARKS-10.

--ADDITION AND SUBTRACTIONSELECT ID, MARKS, MARKS+10, MARKS-10  FROM STUDENTS;

Output: 

Output:

To show the ID and MARKS of all the students from the STUDENTS table along with two more columns displaying the value of MARKS*10 and MARKS/10.

--MULTIPLICATION AND DIVISIONSELECT ID, MARKS, MARKS*3, MARKS/10 FROM STUDENTS;

To display all the records from the STUDENTS table showing the values in the ID, GROUP_ID and MARKS columns. GROUP_ID is formed, depending upon the remainder when ID is divided by 10.

--MODULUSSELECT ID, ID%3 AS GROUP_ID, MARKS FROM STUDENTS;

Output:

SQL Logical Operators

The terms “logical operators” refer to those operators that accept two expressions as operands and give an output in the form of either TRUE or FALSE. Logical operators, which function similar to the logic gates, come extremely handy when dealing with complex SQL queries and expressions. 

 The table below lists the various logical operations that are available in SQL. 

Output:

Examples of SQL Logical Operators
To display the names of the students starting with A, who has either scored above 90 or have GROUP_ID(from one of the previous examples) = 0

--OR & LIKESELECT NAME FROM STUDENTS WHERE NAME LIKE "A%" AND (MARKS > 90 OR ID%10==0);

To display the names of the students whose names start with A, end with H or start with H and having Y as the third letter 

--IN and BETWEENSELECT NAME FROM STUDENTS WHERE NAME IN ["A%", "%H, "P_Y%"]OR MARKS BETWEEN 85 AND 90;

Output:

To display the name and marks of students who scored 95 using ALL operator

--ALL (Similarly we can also write queries for ANY and SOME)SELECT NAME, MARKS FROM STUDENTS WHERE ID = ALL(SELECT ID FROM STUDENTS WHERE MARKS = 95);

Output:

To display records with a null value in the MARKS column

-- IS NULLSELECT ID, NAME FROM STUDENTSWHERE MARKS IS NULL;

Output:

This query when executed produces a blank result set since there is no record with a NULL value in the MARKS column in the table. Thus, an empty set is generated by this IS NULL operator. 

To display the records from the STUDENTS table whose records exist in the PROGRAMS table as well

--EXISTSSELECT * FROM STUDENTSWHERE EXISTS (SELECT * FROM PROGRAMWHERE STUDENTS.ID = PROGRAMS.SID);

Output:

Here, PROGRAM is another table which records the details of the students who participated in the Annual Day Program of the school.

SQL Compound Operators 

We can operate on variables more rapidly and effortlessly with compound operators. For instance, suppose @a is a variable whose value we want to raise by 2. By using the expression “@ a = @ a + 5,” we can normally increase the value of @a by 5. We may execute the same task more quickly and simply with @ a + = 5 by using the compound operator “+ =”. Compound operators are original and can be seen as short-hand derived versions of arithmetic and bitwise operators. 

Here is the list of all the compound operators present in SQL. 

Examples of SQL Compound Operators
To update the marks of students having less than 80 marks using a compound operator

--QUERY - 1UPDATE STUDENTS SET MARKS+=10WHERE MARKS<85;
--QUERY – 2SELECT * FROM STUDENTS;

Output:

To display the records of students using a compound operator in the SELECT clause

--%=SELECT ID, NAME, MARKS%=5 FROM STUDENTSWHERE MARKS>90;

Output:

SQL Comparison Operators

Another important and highly useful type of operator is Comparison Operators. In SQL, the equality of two expressions is verified using comparison operators. It determines whether two expressions are equivalent to one another. In the WHERE clause of a SQL query, comparison operators are typically made use of. A comparison operation can produce the results TRUE, FALSE, or UNKNOWN. If any one of the expressions or both of them are NULL, the operation returns UNKNOWN. All expression kinds, with the exception of those containing text, ntext, or images, could employ these operators.

Look at the table below to know what all comparison operators are allowed in SQL. 

Examples of SQL Comparison Operators
To display the ID, NAME, MARKS, and values of MARKS+5 for the students having more than or equal to 90 marks

--<=SELECT ID, NAME, MARKS, MARKS+5 FROM STUDENTSWHERE MARKS<=90;

Output:

To display the records of the students who have not scored 90

SELECT * FROM STUDENTSWHERE MARKS != 90;

Output:

Conclusion

In most of the examples given in this blog, more than one operator has been used. We can combine multiple SQL operators together in a single SQL query to make the retrieval of data more specific, but in such cases, it is significant to put the operators in the query while keeping the precedence of operators in mind. Precedence of operators tells the sequence in which different operators will operate in a query in accordance with their priority level in SQL. The arithmetic and compound operators are always given the highest precedence, followed by comparison and logical operators. 

The precedence for some of the commonly used SQL operators has been given below:

= 🡺 < 🡺 > 🡺 <= 🡺 >= 🡺 <> 🡺!= 🡺 ~= 🡺 ^= 🡺 IS NULL 🡺 LIKE 🡺 BETWEEN 🡺 IN

As you might have observed from the examples cited above, SQL operators make it convenient and time-saving for us to filter out data based on certain conditions. Now that you know how each type of operator works in SQL, you’ll be able to identify which operator meets the demand of the query perfectly in a particular situation. You will also be capable of putting using the operators in the right sequence on the basis of their precedence order. So, go ahead and try your hands on all sorts of queries in SQL and polish your knowledge of SQL operators you have gained so far.  

Frequently Asked Questions (FAQs)