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

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_ID	Employee_Name	Age
1	John	28
2	Alex	24
3	James	35
4	Roy	22
5	Kay	44

Select * from Employee where employee_id = 2 

Employee_ID	Employee_Name	Age
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_ID	Employee_Name	Age
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_ID	Employee_Name	Age
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_ID	Employee_Name	Age
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_ID	Employee_Name	Age
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_ID	Employee_Name	Age
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_ID	Employee_Name	Age
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_ID	Employee_Name	Age
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_ID	Employee_Name	Age
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_ID	Employee_Name	Age
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_ID	Employee_Name	Age
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' )