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. 

Operator	Operation	Explanation
+	Addition or sum	This operator is used for adding the numeric operands on either side of the operator
–	Subtraction or difference	This operator is used for subtracting the right numeric operand from the left numeric operand.
*	Multiplication	This operator is used for multiplying a given numeric value to the operand given on the other side of the operator or multiplying the numeric columns provided on either side of the operator.
/	Division	This operator is used for returning a quotient by dividing the first operand by the second operand or one numeric column by another.
%	Modulus	This operator is used for returning the remainder by dividing the first operand by the second operand or one numeric column by another.

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. 

Operator	Explanation
ALL	This SQL operator is used for performing a comparison on all other values of the present set. A logical ALL operator is used with one of the following three statements in SQL – SELECT, WHERE, HAVING.
ANY	This SQL operator can be used for generating a result set depending upon whether a given value matches any other value in the available set.
OR	This logical OR returns TRUE if at least one of the given conditions separated by OR is fulfilled, otherwise returns FALSE.
AND	This logical operator returns TRUE if all the mentioned conditions separated by AND are met, otherwise returns FALSE.
SOME	Similar to ANY operator, this logical operator is also used to make a comparison with each value of the data set. It checks whether a particular value matches some of the values in the given data set.
IN	This logical operator is a short-hand for multiple OR operations where you can compare the values of selected columns to the values in a given list. It returns TRUE if at least one match from the list is found, else returns FALSE.
BETWEEN	This logical operator is used for specifying a range to check values. It can generate a result set containing the rows that fall in the specified range. It returns NULL if there is no value falling in the specified range.
IS NULL	This operator is useful in fetching the rows for which the value for a certain field is NULL.
EXISTS	This logical operator is used for finding out if a certain row exists in a table of the database.
NOT	Logical NOT operator has been used for the negation of the output of any logical operator. It checks for the reverse of a given logical expression.
LIKE	When we want to match a particular pattern with the values of a selected field, we use this LIKE operator.

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. 

Operators	Explanation
+=	This operator can be used for incrementing the value of the variable on the left side of the operator by the operand or value specified on the right side of the operator.
-=	This operator can be used for decrementing the value of the variable on the left side of the operator by the operand or the value specified on the right side of the operator.
*=	This operator can be used for multiplying the variable on the left side of the operator with the operand or value on the right side of the operator and storing the updated value in the left-hand side variable.
/=	This operator can be used for updating the value of a variable by dividing the variable by the value specified on the right side of the operator.
%=	This operator stores the remainder when the variable is divided by a given value in the variable itself.
&=	This operator can be used for updating the value of a variable after performing a bitwise AND operation on the given operands, one operand is the variable itself.
|=	This operator can be used for updating a variable with the output of the bitwise OR operation performed on the given operands, one operand being the variable itself.
^=	This operator can be used for updating a variable with the output of the bitwise exclusive OR operation performed on the given operands, one operand being the variable itself.

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. 

Operator	Operation	Explanation
>	Greater than	This operator checks if the left operand is greater than the right operand.
<	Less than	This operator checks if the left operand is less than the right operand.
=	Equal to	This operator checks if the values of the operands on both sides of the operator are the same.
>=	Greater than or equal to	It checks for the value of the left operand to be greater than or equal to the value of the right-hand operand.
<=	Less than or equal to	It checks for the value of the left-hand operand to be less than or equal to the value of the right-hand operand.
< > or!=	Not equal to	This operator checks for inequality and returns TRUE if the left and right operands are not equal to each other.
!>	Not greater than	This operator returns TRUE if the left-hand operand is not greater than the right-hand operand. It is equivalent to less than or equal to the operator in SQL.
!<	Not less than	This operator returns TRUE if the left-hand operand is not equal to the right-hand operand. It is equivalent to the greater than or equal to the operator 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)