SQL Operators - Where, IN, AND, OR and Between

    4 Votes


We have learned basic DML operations in the last article. Now it's time to learn filter commands like Where, IN, AND, OR, Between etc. which can be used to filter data retreived from a table.

SQL Where Clause

SQL “where” clause is used to retrieve a subset of table's data based on the filter condition specified. SQL “Where” clause is specified after table name. For example, To retrieve a row from the table specified below, following query is used.
Table Name: Employee
Employee_IDEmployee_NameAge
1 John 28
2 Alex 24
3 James 35
4 Roy 22
5 Kay 44

Select * from Employee where employee_id = 2 
Employee_IDEmployee_NameAge
2 Alex 24

If we want to retrieve records which is greater than a specified value, greater than operator is used. 

Select * from Employee where employee_id > 2
Employee_IDEmployee_NameAge
3 James 35
4 Roy 22
5 Kay 44

To retrieve records which is greater than or equal to specified value, “>=” operator is used.

Select * from Employee where employee_id >= 2
Employee_IDEmployee_NameAge
2 Alex 24
3 James 35
4 Roy 22
5 Kay 44

To retrieve records which is less than a specified value, "<" operator is used.

Select * from Employee where employee_id < 2
Employee_IDEmployee_NameAge
1 John 28

To retrieve records which less than or equal to a specified value, operator used is "<=".

Select * from Employee where employee_id <= 2
Employee_IDEmployee_NameAge
1 John 28
2 Alex 24

To retrieve records from a table which is not matching the specified filter condition, “< > or !=” not equal operator is used.

Select * from Employee where employee_id < > 2 
or
Select * from Employee where employee_id != 2
Employee_IDEmployee_NameAge
1 John 28
3 James 35
4 Roy 22
5 Kay 44

SQL Between

“Between” operator is used to find matching records between two values.

Select * from Employee where employee_id between 2 and 4 
Employee_IDEmployee_NameAge
2 Alex 24
3 James 35
4 Roy 22

SQL Null

Null” keyword is used to fetch data from a table where a columns value in NULL.

select * from employee where age is null

Advanced Filtering ( And & OR ) “AND” Keyword

To filter by more than one column, use AND operator to append conditions to WHERE clause

select * from employee where employee_name = 'Alex' and age > 20
Employee_IDEmployee_NameAge
2 Alex 24

“OR” Keyword

The OR operator instructs the database management system to retrieve rows that match any one condition.

select * from employee where employee_name ='Alex' or age > 40
Employee_IDEmployee_NameAge
2 Alex 24
5 Kay 44

Using “And ” and “OR” Keyword with parenthesis

select * from employee where (employee_name ='Alex' or employee_name ='Roy' ) and age > 20
Employee_IDEmployee_NameAge
2 Alex 24
4 Roy 22

SQL IN Operator

The IN operator is used to specify a range of conditions, any of which can be matched.

select * from employee where employee_name in ('Alex','Roy' )
Employee_IDEmployee_NameAge
2 Alex 24
4 Roy 22

SQL NOT

The NOT operator is used to fetch data from table where condition is not matched

select * from employee where employee_name not in ('Alex','Roy' )