SQL Operators

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:

  1. Arithmetic Operators
  2. Assignment Operators
  3. Bitwise Operators
  4. Comparison Operators
  5. Logical Operators
  6. Scope Resolution Operator
  7. String Concatenation Operators
  8. Unary Operators
  9. Set Operators

Let us discuss step-by-step 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 T-SQL and you will get the output: 15 in AssiginedColumn.

The same as in other languages like C, C++, Java, C#, and so on. in T-SQL 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 T-SQL that are as follows,

Operator
Meaning
& (Bitwise AND)
Performs bitwise AND operation
| (Bitwise OR)
Performs bitwise OR operation
^ (Bitwise XOR)
Performs bitwise Exclusive-OR 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 Exclusive-OR 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 T-SQL 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 sub-query 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 -by-step 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:

#tb1
#tb2

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

😊😊😊

Sourabh Somani
He is Microsoft MVP & C# Corner MVP. His core competencies include Mobile and web applications development using .NET, Python, Node.js, C#, JavaScript, jQuery, SQL Server, NoSQL, MongoDB, and Angular.
Chittorgarh, Rajasthan, India