SQL Select Where Clause

 SQL Select Where Clause

The WHERE clause is optional. When specified, it always follows the FROM clause. The WHERE clause filters row from the FROM clause tables and omitting the WHEREclause specifies that all rows are used.

The WHERE clause is used to get the data according to the specific criteria or specific conditions.

The WHERE keyword is also used to filter the data of matching samples.

The WHERE clause is used with the combination of AND, OR NOT operators.

 

Syntax:

The syntax for the WHERE clause is given as:

Select * from table-name where conditions;

Or

Select column1, column2,….from table-name where conditions;

 

We also have syntax for AND, OR, and NOT operators:

·        AND syntax:

Select * from table-name where condition1 AND condition2 AND condition3………;

Or

Select column1, column2,….from table-name where condition1 AND condition2 AND condition3………;

·        OR syntax:

Select * from table-name where condition1 OR condition2 OR Condition3…..;

or

Select column1, column2,….from table-name where condition1 OR condition2 OR Condition3…..;

·        NOT syntax:

Select * from table-name where NOT condition;

Or

Select column1, column2,…. from table-name where NOT condition;

 

Operators

Following is the list of operators used with the WHERE clause:

 

operator	Description
=	Equal to
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
<>or !=	Not equal
BETWEEN	Between certain range
LIKE	Search for pattern
IN	To specify multiple possible values

 

Example:

Let’s take an employee table

ID	NAME	AGE	SALARY
11	Jack	30	10000
12	John	32	20000
13	Jill	35	30000
14	Rose	37	40000
15	James	40	50000
13	Jill	33	40000
16	Alex	33	70000

7 rows selected

1.     Select*from employee where id=13;

ID	NAME	AGE	SALARY
13	Jill	35	30000
13	Jill	33	40000

 

2.     Select * from employee where name='jill';

 

ID	NAME	AGE	SALARY
13	Jill	35	30000
13	Jill	33	40000

 

3.     Select * from employee where age=33;

ID	NAME	AGE	SALARY
13	Jill	33	30000
16	Alex	33	70000

 

4.     Select * from employee where salary=40000;

ID	NAME	AGE	SALARY
14	Rose	37	40000
13	Jill	33	40000

 

Using AND operator

1.     Select * from employee where name=’Jill’ and age=33;

ID	NAME	AGE	SALARY
13	Jill	33	40000

 

2.     Select * from employee1 where name='rose' and age=33;

No rows selected

3.      Select * from employee1 where name='rose' and id=13;

No rows selected

4.     Select * from employee where name=’Jill’ and age=33 and id=13;

ID	NAME	AGE	SALARY
13	Jill	33	40000

 

Using OR operator

1.     Select * from employee where name=’jill’ or age=13;

ID	NAME	AGE	SALARY
13	Jill	35	30000
13	Jill	33	40000

 

2.     Select * from employee where name=’jill’ or id=13;

ID	NAME	AGE	SALARY
13	Jill	35	30000
13	Jill	33	40000

 

3.     Select * from employee where age=35 or salary=20000;

ID	NAME	AGE	SALARY
12	John	32	20000
13	Jill	33	30000

 

4.     Select * from employee1 where name='jill' or age=13 or id=14;

ID	NAME	AGE	SALARY
13	Jill	35	30000
14	Rose	37	40000
13	Jill	33	40000

 

5.     Select * from employee where name=’Jill’ or id=13 or salary=30000;

ID	NAME	AGE	SALARY
13	Jill	35	30000
13	Jill	33	40000

 

Using NOT operator

1.     Select * from employee where not id=30;

ID	NAME	AGE	SALARY
11	Jack	30	10000
12	John	32	20000
13	Jill	35	30000
14	Rose	37	40000
15	James	40	50000
13	Jill	33	40000
16	Alex	33	70000

 

2.     Select * from employee where not id=13 ;

ID	NAME	AGE	SALARY
11	Jack	30	10000
12	John	32	20000
14	Rose	37	40000
15	James	40	50000
16	Alex	33	70000

 

3.     Select * from employee where not id=13 and salary=20000;

ID	NAME	AGE	SALARY
12	John	32	20000

 

4.     Select * from employee1 where not id=13 and name='Alex';

No rows selected

5.     Select * from employee where not id=13 and  not name='Alex' or not age=33;

ID	NAME	AGE	SALARY
11	Jack	30	10000
12	John	32	20000
13	Jill	35	30000
14	Rose	37	40000
15	James	40	50000
16	Alex	33	70000

 

Using other operators

1.     Select * from employee where salary>30000;

ID	NAME	AGE	SALARY
14	Rose	37	40000
15	James	40	50000
13	Jill	33	40000
16	Alex	33	70000

 

2.     Select * from employee where id>=13;

ID	NAME	AGE	SALARY
13	Jill	35	30000
14	Rose	37	40000
15	James	40	50000
13	Jill	33	40000
16	Alex	33	70000

 

3.     Select * from employee where id<15;

ID	NAME	AGE	SALARY
11	Jack	30	10000
12	John	32	20000
13	Jill	35	30000
14	Rose	37	40000
13	Jill	33	40000

 

4.     Select * from employee where id<=15;

ID	NAME	AGE	SALARY
11	Jack	30	10000
12	John	32	20000
13	Jill	35	30000
14	Rose	37	40000
15	James	40	50000
13	Jill	33	40000

 

5.     Select * from employee where id=13;

ID	NAME	AGE	SALARY
13	Jill	35	30000
13	Jill	33	40000

 

6.     Select * from employee where age<>33;

ID	NAME	AGE	SALARY
11	Jack	30	10000
12	John	32	20000
13	Jill	35	30000
14	Rose	37	40000
15	James	40	50000

 

7.     Select * from employee where age between 33 and 40;

ID	NAME	AGE	SALARY
13	Jill	35	30000
14	Rose	37	40000
15	James	40	50000
13	Jill	33	40000
16	Alex	33	70000

 

8.     Select * from employee where name like 'J%’;

ID	NAME	AGE	SALARY
11	Jack	30	10000
12	John	32	20000
13	Jill	35	30000
15	James	40	50000
13	Jill	33	40000

 

9.     Select * from employee where age is 33;

 

ID	NAME	AGE	SALARY
13	Jill	33	40000
16	Alex	33	70000