SQL Operators | Different Operators Available in SQL

In SQL Operators article, we learn such as SQL Arithmetic operators, SQL Comparison Operators, SQL Logical Operator, SQL Compound operators,
Also, we discuss in details on:

• HOW to Impose Condition using SQL Operators?
• And operator in SQL and its syntax with example
• NOT operator in SQL and its syntax with example
• SQL OR operator and its syntax with example

What are SQL Operators And How Do They Work?

SQL operators are reserved keywords, which are used in the WHERE clause of a SQL statement to perform arithmetic, comparison, and logical operations. Therefore, Operators act as conjunctions in SQL statements to fulfill multiple conditions in a statement.
There are different types of SQL operator, let us understand the operators in SQL.

Types of SQL Operators

Types Of Operator	Description
Arithmetic Operators:	These operators are used to perform operations such as addition, subtraction, multiplication, etc.
Comparison Operators:	These operators are used to perform operations such as greater than, less than and equal to, etc.
Logical Operators:	These operators are used to perform operations such as ANY, ALL, BETWEEN, NOT, etc.
Compound Operators:	These Operators are used to perform operations such as +=, Add equals. -=, Subtract equals. *=, Multiply equals. /=, Divide equals, etc.
SQL Bitwise operators	These SQL Bitwise operators are the operators which are used on a bit of data

SQL Arithmetic Operators:

Assume ‘variable a’ holds 100 and ‘variable b’ holds 200, then −

Operator	Operation	Description	Example
+	Addition	It is used to add containing values on either side of the operator	a+b will give 300
–	Subtraction	It is used to subtract the right-hand operand value from the left-hand operand value	a-b will give -100
*	Multiplication	It multiplies the values present on either side of the operator	a*b will give 20000
/	Division	It divides the left-hand operand side value by the right-hand operand side value	b/a will give 2
%	Modulus	It divides the left-hand operand side value by the right-hand operand side value and returns the remainder	b%a will give 0

SQL Comparison Operators:

Assume ‘variable a’ holds 100 and ‘variable b’ holds 200, then −

Operator	Operation	Description	Example
=	Equal to	Examine both operands values that are equal or not, if yes then condition becomes true.	(a=b) is not true
!=	Equal or not	This is used to check if the values of two operands are equal or not, if values are not equal then condition becomes true	(a!=b) is true
< >	Not equal to	Used to check if the operand’s value equal or not, if values are not equal then the condition becomes true.	(a< >b) is true
>	Greater than	Examine the left operand value is greater than the value of right operand, if yes then condition becomes true	(a>b) is not true
>	Less than	Examines the left operand value is less than the value of right operand if yes then condition becomes true	(a > b) is true.
>=	Greater than or equal to	Checks if the value of the left operand is greater than or equal to the value of right operand or not, if yes then condition becomes true	(a >= b) is not true.
<=	Less than or equal to	Checks if the value of the left operand is less than or equal to the value of right operand or not, if yes then condition becomes true	(a<=b) is true
!<	Not less than	Examines that if the left operand value is not less than the right operand value, if yes then condition becomes true.	(a !< b) is false
!>	Not greater than	Examines that if the value of the left operand is not greater than the value of right operand, if yes then returns TRUE	(a !> b) is true

SQL Logical Operators

Here is the list of all the SQL logical operators available.

Operator	Description
ALL	The ALL operator is used to compare a specific value to all other values in a set.
AND	The AND operator allows the user to multiple conditions in an SQL statement’s WHERE clause.
ANY	This operator is used to compare the value to any applicable value in the list as per the condition.
BETWEEN	The BETWEEN operator is used to Searches for values that are within the range mentioned, given the minimum value and the maximum value.
IN	This operator is used to compare a specific value to the literal values mentioned.
NOT	The NOT operator used to reverse the output of the logical operator. Eg: NOT EXISTS, NOT BETWEEN, NOT IN, etc. This is a negate operator.
OR	this operator is used to combine multiple conditions in an SQL statement’s WHERE clause.
EXISTS	This operator is used to search for the presence of a row in a specified table that meets a certain criterion.
IS NULL	This operator is used to compare the value with the NULL value.
UNIQUE	The UNIQUE operator searches every row of a specified table for uniqueness means that no duplicates.

SQL Compound Operators

Here is a list of all the SQL Compound Operators available.

Operator	Description
+=	Add equals
-=	Subtract equals
*=	Multiply equals
/=	Divide equals
%=	Modulo equals
&=	Bitwise AND equals
^-=	Bitwise exclusive equals
|*=	Bitwise OR equals

SQL Bitwise operators

Here is a list of all the SQL Bitwise operators available.

Operator	Description
&	Bitwise AND operator
|	Bitwise OR operator
^	Bitwise Exclusive OR operator
<<	Left Shift operator
>>	Right Shift operator

HOW to Impose Condition using SQL Operators?

As in the earlier article, I have discussed SQL WHERE Clause, we saw how one condition is passed, but When there are multiple conditions applied to a table to filter the data, then the operators are used. therefore, let’s see, there are two conditions: first, the age should be greater than 36, and second, the occupation must be a manager. furthermore, when these conditions are met, the AND operator displays values.
There are mostly three operators used in SQL:

• AND operator in SQL
• OR operator in SQL
• NOT operator in SQL

Sample Table: Employee

And operator in SQL and Its Syntax With Example

The AND operator displays only those records where all conditions meet, which means all conditions are evaluated to true. let,s see the example, if you want to find out in the employee table, the all management employees aged greater than 35 years, then the syntax would be as follows:

Syntax:

where, SELECT, FROM, WHERE, and AND are the SQL Keywords, furthermore, Column1 to ColumnN is a set of columns and tablename is the name of the table, therefore, condition1 to conditionN is a set of conditions followed by a semicolon.

SQL Operator Example:

Let’s us display all employees whose age is below 35 and who belong to the operation management from the employee table.

Output:

NOT operator in SQL and Its Syntax With Example

Therefore, the NOT operator displays a record, when if the condition is not true. furthermore, let’s see the example, if we have to extract all records where the occupation of an employee is not a Finance Controller, then the Not operator is used.

Syntax:

where SELECT, FROM, WHERE, and NOT are the SQL keywords; column1 to columnN are a set of columns; furthermore, tablename is the name of the table, and there is a condition followed by a semicolon.

SQL Operator Example:

Let’s us display the, all employees whose Employee Occupation is not equal to Doctor from the employee table:

Output:

SQL OR Operator And Its Syntax With Example

Therefore, the OR operator displays the records for any condition separated by OR which is evaluated to true. let’s us see the example, therefore, if we have to filter out Engineer or a Driver, then i that situation, we use the OR operator.

Syntax:

where SELECT, FROM, WHERE, and OR are the SQL keywords; column1 to columnN are a set of columns; furthermore, tablename is the name of the table, and condition1 to conditionN is a set of conditions these are followed by a semicolon.

SQL Operator Example:

Let’s us display the all employees who are from either the occupation is engineer or the occupation is driver from the employee table.

Output:

Conclusion:

Now you should have a good understanding SQL operators, and their operation, and you will learn the Different Operators Available in SQL. I hope you will be enjoy it! if have any query please contact me, I will try to resolve your query. Thanks!