Summary -

In this topic, we described about the WHERE Clause with detailed example.

WHERE Clause is used to specify the condition in SELECT statement. WHERE Clause filters the rows and fetch particular rows in the table whose values satisfied with the specified condition. WHERE Clause can also used in UPDATE and DELETE statement.

Syntax -

SELECT column1, column2,…..
  FROM table_name
  WHERE condition;
  • column1, column2 – represents the columns which we want to fetch from table.
  • table_name – represents the name of the table.
  • condition – represents the required condition to fetch rows based on requirement. It contains column_name, operator, user defined value. Comparison and logical operators are used in condition.

Example -

Consider employee_details table as follows -

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario – Fetching list of rows from table.

Requirement – Fetching all details of employees WHOSE dept_id of 2000. The query was as follows –

SELECT  * FROM employee_details WHERE dept_id = 2000;

By executing above query we can get all details of dept_id is 2000 employees. The output was as follows –

emp_id emp_name designation manager_id date_of_hire salary dept_id
002 Employee2 Director 2019-07-11 40000.00 2000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario – Fetching list of rows within the range from table.

Requirement– Fetching all details of employees whose salary between 27000 and 15000.The query was as follows –

SELECT  * FROM employee_details WHERE salary BETWEEN 15000 AND 27000;

By using above query we can get all details of employees whose salary is in between 15000 and 27000. The output was as follows –

emp_id emp_name designation manager_id date_of_hire salary dept_id
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000

Scenario – Fetching one row from table.

Requirement – Fetching all details of one employee whose Emp_id was 006. The query was as follows –

SELECT  * FROM employee_details WHERE emp_id = 006;

By using above query we can get all details of single employee whose id was 006. The output was as follows –

emp_id emp_name designation manager_id date_of_hire salary dept_id
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000

Scenario – Fetching list of rows with specific columns from table.

Requirement – Fetching emp_name, emp_id, salary details of employees whose salary greater than 35,000. The query was as follows –

SELECT emp_id,emp_name,salary
FROM employee_details WHERE salary > 35000;

By executing above query we can get specified details of employees whose salary greater than 35000. The output was as follows –

emp_id emp_name salary
001 Employee1 45000.00
002 Employee2 40000.00

Scenario – Fetching one row with specified columns from table.

Requirement – Fetching emp_id, emp_name, designation details of one employee whose designation is clerk. The query was as follows –

SELECT emp_id, emp_name, designation FROM employee_details 
WHERE designation = 'clerk';

By using above query we can get specified details of single employee whose designation was clerk. The output was as follows –

emp_id emp_name designation
007 Employee7 Clerk

Scenario – Fetching one value from table.

Requirement – Fetching dept_id of employee whose id was 005. The query was as follows –

SELECT dept_id FROM employee_details WHERE emp_id = 005;   

By executing above query we can get dept_id value of employee whose id is 005. The output was as follows –

dep_id
1000

Scenario – Fetching multiple different rows from table.

Requirement – Fetching salary details of employees whose emp_name are Employee1, Employee3, Employee5. The query was as follows –

SELECT salary FROM employee_details
WHERE emp_name IN ('Employee1','Employee3','Employee5');

By executing above query we can get salary details of employees whose names are Employee1, Employee3, Employee5. The output was as follows –

salary
45000
27000
20000

Scenario – Updating the value in the row from table.

Requirement – Updating the salary of employee whose emp_id is 004. The query was as follows –

UPDATE employee_details SET salary = 30000 WHERE emp_id = 004;

By executing above query we can update the salary of employee whose id is 004. The output was as follows –

emp_id emp_name designation manager_id date_of_hire salary dept_id
004 Employee4 Manager Employee2 2019-10-08 30000.00 2000

Scenario – Deleting the row from table.

Requirement – Deleting the row in the employee_details table whose name is Employee3. The query was as follows –

DELETE  FROM employee_details WHERE emp_name = 'Employee3';

By executing above query we can delete all the details of employee whose name is Employee3. The output was as follows -

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
004 Employee2 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000