SQL Operators

Earlier Post, We have discussed about Where and Order by Clauses and continuation for that we ill learn operators in SQL.

The following are the different types of operators used in where clause.

First Let's Understand "What is operator in SQL ?".

In SQL, operator is a Keyword and which we use in sql query where clause.

1) Arithmetic operators
2) Comparison operators
3) Logical operators

1) Arithmetic operators :

+, -, *, / are arithmetic operators.


We all know mathematics so we no need learn again what is Plus(+),Minus(-) etc.

2) Comparison operators:-

a) =, !=, >, <, >=, <=, <>
b) between, not between
c) in, not in
d) null, not null
e) like
3) Logical operators:-
a) And
b) Or

a) USING =, >, <, >=, <=, !=, <> operators:-

Ex: SQL> select * from student where no = 2;
Above select query will return wherever no column has value equal to “2”.

NO NAME MARKS
— ——- ———
2 Ram 200
2 Chinna 400

SQL> select * from student where no < 2;
Above select query will return wherever no column has value less than “2”.
NO NAME MARKS
— ——- ———-
1 Sree 100
1 Ramana 300

SQL> select * from student where no > 2;
Above select query will return wherever no column has value Greater than “2”.
NO NAME MARKS
— ——- ———-
3 Sreeram
4 Nithin
5 Ravi
6 Mahi

SQL> select * from student where no <= 2;
Above select query will return wherever no column has value less than or equal to “2”.
NO NAME MARKS
— ——- ———-
1 Sree 100
2 Ram 200
1 Ramana 300
2 Chinna 400
SQL> select * from student where no >= 2;
Above select query will return wherever no column has value Greater than or equal to “2”.
NO NAME MARKS
— ——- ———
2 Ram 200
2 Chinna 400
3 Sreeram
4 Nithin
5 Ravi
6 Mahi

SQL> select * from student where no != 2;
Above select query will return wherever no column is not equal to “2”.
NO NAME MARKS
— ——- ———-
1 Sree 100
1 Ramana 300
3 Sreeram
4 Nithin
5 Ravi

SQL> select * from student where no <> 2;
This query also work as same as above query,it means
Above select query also will return wherever no column is not equal to “2”
NO NAME MARKS
— ——- ———-
1 Sree 100
1 Ramana 300
3 Sreeram
4 Nithin
5 Ravi



b) BETWEEN

This will gives the output based on the column and its lower bound, upper bound.

Syntax:
select * from <table_name> where <col> between <lower bound> and <upper
bound>;
Ex:
SQL> select * from student where marks between 200 and 400;

Above query will return values which are having Marks column should have val between 200 to 400.

NO NAME MARKS
— ——- ———
2 Ram 200
1 Ramana 300
2 Chinna 400

c) NOT BETWEEN
This will gives the output based on the column which values are not in its lower bound, upper bound.
Syntax: select * from <table_name> where <col> not between <lower bound> and <upper bound>;
Ex:
SQL> select * from student where marks not between 200 and 400;

Its opposite to previous query.
Above query will return values which are not having Marks column should have val between 200 to 400.

NO NAME MARKS
— ——- ———
1 Sree 100
d) IN

This will gives the output based on the column and its list of values specified.

Syntax:
select * from <table_name> where <col> in ( value 1, value 2, value 3 … value n);

Ex:
SQL> select * from student where no in (1, 2, 3);

Above query will return values which are having no column values as 1, 2, 3.

NO NAME MARKS
— ——- ———
1 Sree 100
2 Ram 200
1 Ramana 300
2 Chinna 400
3 Sreeram

e) NOT IN

This will gives the output based on the column which values are not in the list of values specified.

Syntax:
select * from <table_name> where <col> not in ( value 1, value 2, value 3 … value n);

Ex: SQL> select * from student where no not in (1, 2, 3);

Its opposite to previous query.
Above query will return values which are not having no column values as 1, 2, 3.
NO NAME MARKS
— ——- ———
4 Nithin
5 Ravi
6 Mahi

f) NULL

This will gives the output based on the null values in the specified column.

Syntax:
select * from <table_name> where <col> is null;

Ex:
SQL> select * from student where marks is null;

Above query will return values wherever marks column are having as null value.

NO NAME MARKS
— ——- ———
3 Sreeram
4 Nithin
5 Ravi
6 Mahi

g) NOT NULL

This will gives the output based on the not null values in the specified column.

Syntax:
select * from <table_name> where <col> is not null;
Its opposite to previous query.
Above query will return values wherever marks column are having as not null value.
Not Null means something data is present in that column.

Ex:
SQL> select * from student where marks is not null;
NO NAME MARKS
— ——- ———
1 Sree 100
2 Ram 200
1 Ramana 300
2 Chinna 400

h) LIKE

This will be used to search through the rows of database column based on the pattern
you specify.

Syntax:
select * from <table_name> where <col> like <pattern>;

Ex:
i) This will give the rows whose marks are 100.

SQL> select * from student where marks like 100;

NO NAME MARKS
— ——- ———
1 Sree 100
ii) This will give the rows whose name start with ‘S’.

SQL> select * from student where name like ‘S%’;

NO NAME MARKS
— ——- ———
1 Sree 100
2 Ram 200

iii) This will give the rows whose name ends with ‘h’.

SQL> select * from student where name like ‘%h’;

NO NAME MARKS
— ——- ———
2 Mahesh 200
3 Ramesh

iV) This will give the rows whose name’s second letter start with ‘a’.

SQL> select * from student where name like ‘_a%’;

NO NAME MARKS
— ——- ——–
2 Ram 200
1 Ramana 300
2 Chinna 400
3 Sreeram
4 Nithin
6 Mahi

V) This will give the rows whose name’s third letter start with ‘d’.

SQL> select * from student where name like ‘__d%’;

NO NAME MARKS
— ——- ———
1 Sree 100
4 Nithin

Vi) This will give the rows whose name’s second letter start with ‘t’ from ending.

SQL> select * from student where name like ‘%t_’;
NO NAME MARKS
— ——- ———
2 Ram 200
6 Mahi

Vii) This will give the rows whose name’s third letter start with ‘e’ from ending.

SQL> select * from student where name like ‘%e__’;

NO NAME MARKS
— ——- ———
2 Sree 200
3 Sreeram

Viii) This will give the rows whose name contains 2 a’s.

SQL> select * from student where name like ‘%a%a%’;

NO NAME MARKS
— ——- ———-
1 Ramana 300

i) NOT LIKE: 

Not like operator is opposite to like operator so you can execute above queries by adding not like instead of like .

Note: Like operator can be used like this way for any requirement and remember to use “%”,”_” with like operator.
We call “%”(Percentage) and “_”(Underscore) as WILD CARD CHARACTERS.

Logical Operators:

a) AND

This will give the output when all the conditions become true.

Syntax:
select * from <table_name> where <condition 1> and <condition 2> and ..
<condition n>;
Ex:
SQL> select * from student where no = 2 and marks >= 200;

In the above query both condition should satisfy then only it ill return values it means no column value should be equal to “2” and also marks should be greater than or equal to 200.

NO NAME MARKS
— ——- ——–
2 Ram 200
2 Chinna 400

b) OR

This will gives the output when either of the conditions become true.

Syntax:select * from <table_name> where <condition 1> and <condition 2> or.. <condition n>;
Ex:
SQL> select * from student where no = 2 or marks >= 200;

In the above query either of one condition should satisfy then only it ill return values it means no column value should be equal to “2” (OR) marks should be greater than or equal to 200.
NO NAME MARKS
— ——- ———
2 Ram 200
1 Ramana 300
2 Chinna 400