Introduction
In this SQL Operators tutorial, I'll explain SQL operators in detail starting with SQL Operator types, their usages, and examples in SQL.
SQL Operators
A SQL operator is a sign or symbol that can perform an operation between operands or between two or more than two expressions. SQL operators can be broken down into the following categories:
 Arithmetic Operators
 Assignment Operators
 Bitwise Operators
 Comparison Operators
 Logical Operators
 Scope Resolution Operator
 String Concatenation Operators
 Unary Operators
 Set Operators
Let us discuss stepbystep each of the operators listed above.
SQL Arithmetic Operators
To perform a mathematical operation on one or more expressions of a numeric data type, like addition, subtraction, multiplication, division and modulo division then we use an arithmetic operator. The list of arithmetic operators are given below,
Operator

Meaning

Example

+

Addition

15 + 2 = 17



Subtraction

15  2 = 13

*

Multiplication

15 * 2 = 30

/

Division

15 / 2 = 7

%

Modulo

15 % 2 = 1 (Reminder)

Example 1.1
declare @var1 int,
@var2 int,
@add int,
@sub int,
@mul int,
@div int,
@rem int;  declaration of var1, var2, add,sub,mul,div
set @var1=12;assigning var1=12
set @var2=5;assigning var1=12
set @add = @var1 + @var2;Addtion
set @sub = @var1  @var2;Subtraction
set @mul = @var1 * @var2;Multiplication
set @div = @var1 / @var2;Division
select @var1 as Variable1,
@var2 as Variable2,
@add as Addition,
@sub as Subtraction,
@mul as Multipication,
@div as division; getting the result by using select
By using the code above you will get the following output,
Example 1.2
Suppose I have a table named TempTable in which I have 2 columns like a and b , and I have inserted some values of a and b as in the following,
Creating Table
CREATE TABLE #TempTable(
a int,
b int
)
Inseerting Values
INSERT INTO #TempTable VALUES(1,2),(12,8),(8,10),(17,3),(11,4),(15,8)
By using the query above you will get a new #TempTable such as the following,
Now you can also use arithmetic operators with your query like,
SELECT
a as value1,
b as value2,
(a + b) as ADDITION,
(a  b) as SUBTRACTION,
(a * b) as MULTIPLICATION,
(a / b) as DIVISION,
(a % b) as REMINDER
FROM #TempTable
The output of the above Query is,
SQL Assignment Operator
The SQL assignment operator is used to assign the value to a variable. We usually see an assignment operator '=' .
Example 2.1
In the following example, I am simply taking a variable and assigning a value.
Declaring variable
declare @h int;
Assigning Value
set @h=15;
Getting the output with column name Assigned
select @h as Assigned
So simply run the code above in TSQL and you will get the output: 15 in AssiginedColumn.
The same as in other languages like C, C++, Java, C#, and so on. in TSQL we have Shorthand Assignment Operator. The shorthand assignment operators in SQL are +=, =, *=, /=, %=. shorthand assignment operators are illustrated in the following table.
Statement With Simple Assignment Operator

Statement With Shorthand Assignment Operator

a = a + b

a+=b

a = a – b

a=b

a = a * b

a*=b

a = a / b

a/=b

a = a % b

a%=b

The following are some examples of shorthand assignment operators.
Example 2.2
declare @i int;
set @i=15;
set @i+=10;
select @i as Addition
The output of the code above is 25 with column name Addition.
Example 2.3
declare @j int;
set @j=15;
set @j=10;
select @j as Subtraction
The output of the code above is 5 with column name Subtraction.
Example 2.4
declare @k int;
set @k=15;
set @k*=10;
select @k as Multiplication
The output of the code above is 150 with column name Multiplication.
Example 2.5
declare @L int;
set @L=15;
set @L/=10;
select @L as Divide
The output of the code above is 1 with column name Divide.
Example 2.6
declare @m int;
set @m=15;
set @m%=10;
select @m as Reminder
The output of the code above is 5 with column name Reminder.
Example 2.7
Suppose you have a table of Employee_Info in which you have the 3 fields id, Name, Salary and inside it some data is available like,
Now if you fire a query like,
select id, Name, Salary=10000 from Employee_Info assigning every value as 10000 Salary
Then every value of the salary will be set to 10000 and the output of the code above will be,
SQL Bitwise Operators
As in other languages (like C, C++, Java, C#, and so on) SQL also supports special operators that are the bitwise operators. Bitwise operators manipulate the data at the bit level. The operations are used to test and/or the bits. Bitwise operators are only applied for integer values they cannot be applied for float or doubled value. There are four Bitwise operators available in TSQL that are as follows,
Operator

Meaning

& (Bitwise AND)

Performs bitwise AND operation

 (Bitwise OR)

Performs bitwise OR operation

^ (Bitwise XOR)

Performs bitwise ExclusiveOR operation

~ (Bitwise NOT)

Performs bitwise Not operation

Bitwise AND (&)
The Bitwise logical AND operation is performed between 2 integer values.
Example 3.1
Suppose there are the two integer numbers 2 and 3 and we want to perform 2 & 3 so first we convert 2 and 3 into the binary format. Then after the AND operation is performed on the bits then the bits will again be converted into the decimal format and that will be our output. The complete illustration is given below in the following figure,
Bitwise OR ()
Bitwise logically OR operation is performed between 2 integer values.
Example 3.2
Suppose there are the two integer numbers 2 and 3 and we want to perform 2  3 so we first convert 2 and 3 into binary format. Then after the OR operation is performed on the bits then the bits will again be converted into the decimal format and that will be our output. The complete illustration is given below in the figure,
Bitwise XOR (^)
The Bitwise logical ExclusiveOR operation is performed between 2 integer values.
Example 3.3
Suppose there are the two integer numbers 2 and 3 and we want to perform 2 ^ 3 so we first convert 2 and 3 into the binary format. Then after the XOR operation is performed on the bits then the bits will again be converted into the decimal format and that will be our output. The complete illustration is given below in the figure,
Bitwise Not ( ~ )
The Bitwise logical NOT operation is performed on only one integer value.
Example 3.4
Suppose we have the number 2 and we want to perform the operation ~2 then first 2 will be converted into binary format then after the not operation is performed on the bits then the bits will again be converted it into decimal format.
Example 3.5
In the following example, I am writing a SQL query for examples 3.1, 3.2, 3.3, and 3.4 explained above.
declare @a int;
declare @b int;
set @a=2;
set @b=3;
select @a & @b as And_Operation,
@a  @b as Or_Operation,
@a ^ @b as Exclusive_Operation,
~@a as Bitwise_NOT_A,
~@b as Bitwise_NOT_B ;
The output of the preceding query will be,
Example 3.6
In this example, I am showing how to perform bitwise operations on a table. Suppose you have a temporary table in which you have the two columns val1 and val2 , in which some data is present like,
Create table #MyTempTable(
val1 int,val2 int
)
insert into #MyTempTable values(2,3),(5,3),(8,5),(9,4)
You can perform bitwise operations with your query like,
select val1 as Value1,
val2 as Value2,
val1 & val2 as Bitwise_And_Operation,
val1  val2 as Bitwise_OR_Operation,
val1 ^ val2 as Bitwise_XOR_Operation,
~val1 as Bitwise_Not_Value1,
~val2 as Bitwise_Not_Value2
from #MyTempTable
The output of the code above will be,
SQL Comparison Operators
The comparison operator is also called a Relational Operator. We often compare two quantities and depending on there relations, make a certain decision. For example, we may compare the age of two people, or the price of two items, or salary of two people, and so on. These types of comparisons are done by relational or comparison Operators. The list of Comparison operators available in TSQL is given in the following table,
Operator

Meaning

=

Equal to

<

Less than

>

Greater than

<=

Less than equal to

>=

Greater than equal to

<>

Not equal to

!=

Not equal to(Not ISO Standard)

!<

Not less than(Not ISO Standard)

!>

Not greater than(Not ISO Standard)

Example 4.1
Suppose you have a table #TempTable (temporary table) in which you have two columns val1 and val2. Some values are also inserted into it that are like,
Creating a temporary table
create table #TempTable(
val1 int,
val2 int
)
Inserting Data
insert into #TempTable values(8,10),(17,3),(11,4),(15,8),(8,9),(8,17),(8,8)
Now I am showing you how can you fire a query for all comparison operators.
For Equals (=)
select val1 as Value1,
val2 as Value2
from #TempTable
where val1=val2
Output
Where val1 and val2 will be the same that rows will be selected.
Less than (<)
select val1 as Value1,
val2 as Value2
from #TempTable
where val1<val2
Output
Where val1 will be less than the from val2, those rows will be selected.
Greater than (>)
select val1 as Value1,
val2 as Value2
from #TempTable
where val1>val2
Output
Where val1 will be greater than from val2, those rows will be selected.
Less than or equals to (<=)
select val1 as Value1,
val2 as Value2
from #TempTable
where val1<=val2
Output
Where val1 will be less than or equal to from val2, those rows will be selected.
Greater than equals to (>=)
select val1 as Value1,
val2 as Value2
from #TempTable
where val1>=val2
Output
Where val1 will be greater than or equal to from val2, those rows will be selected.
Not equals to (!=)
select val1 as Value1,
val2 as Value2
from #TempTable
where val1!=val2
Output
Where val1 will not be equal to from val2, those rows will be selected.
Not equals to (<>)
select val1 as Value1,
val2 as Value2
from #TempTable
where val1<>val2
Output
Where val1 will not be equal to from val2, those rows will be selected. The same output will be as for the Not equals to (!=)
Not less than (!<)
select val1 as Value1,
val2 as Value2
from #TempTable
where val1!<val2
Output
Where val1 will not be less than from val2, those rows will be selected.
Not greater than (!>)
select val1 as Value1,
val2 as Value2
from #TempTable
where val1!>val2
Output
Where val1 will not be greater than from val2, those rows will be selected.
SQL Logical Operators
Logical operators in SQL are used to check some condition and returns a boolean value that may be true or false.
The list of logical operators of SQL is listed below in the table.
Operator

Description

All

Check for all the set of comparisons.

AND

Check that two values are both true.

ANY

Check for anyone's set of comparisons.

BETWEEN

Check between the range.

EXISTS

Check whether or not a subquery has a row.

IN

Check a list of expressions.

LIKE

Match the patterns.

NOT

Reverse the value of a Boolean value.

OR

Check between two values and anyone should be true.

SOME

Check that some set of comparisons are true or not

Now let's see a bystep explanation of all the logical operators:
All
This operator returns true if and only if when all the set of comparisons are true. It compares the scalar value with a single column set of values.
Example 5.1
Suppose you have the two tables #tb1, #tb2 and you have inserted a Name and age in both tables like,
create table #tb1(
Name varchar(20),
age int
)
create table #tb2(
Name varchar(20),
age int
)
Both tables will look such as in the following:
Now to understand the ALL operator use the following query.
SELECT * FROM #tb1
WHERE age > All (SELECT age FROM #tb2)
So the output of the preceding query will be,
Because all the sets of comparisons are true with respect to #tb2. But take a scenario where one age is greater in #tb2 with respect to all the the ages of #tb1. Suppose I insert some data like this,
insert into #tb2 values('Monika Dashora',25)
After inserting the row #tb2 will become,
Now again you fire the same query like,
SELECT * FROM #tb1
WHERE age > All (SELECT age FROM #tb2)
Then by using the query above you will get #tb1 as null. Because in the #tb2 we have the age of "Monika Dashora" as "25" and if it will be checked against #tb1 then the age will always be less then 25.
ANY
Any operator returns true if one of the set comparisons are true. This operator also compares a scalar value with a single column set of values.
Example 5.2
Suppose you have the same table that we had in Example 5.1 which is #tb1 and #tb2 and you fire the following query,
SELECT * FROM #tb1
WHERE age > ANY (SELECT age FROM #tb2)
Then you will get the output as:
Because the ANY operator compares a set of values not for all.
Note
The difference between ALL and ANY is, ALL works like an AND so if all the comparisons are true then true is returned else it returns false, ANY works like an OR so if anyone comparison is true then it returns true.
SOME
SOME and ANY are equivalent, both compare a single column's set of values.
To read more about SQL Operator My complete article is written at C# Corner link is as follows
Complete SQL Article Link
😊😊😊