Summary -

In this topic, we described about the AND Condition with detailed example.

AND condition is used to specify multiple conditions in WHERE clause. AND condition return the rows or values that are satisfied all the conditions in the WHERE clause. AND condition is used in SELECT, UPDATE, DELETE statements.

SELECT Syntax –

SELECT column1, column2, …, columnN
FROM table_name
[WHERE condition1 AND condition2 AND … AND conditionN]; 

UPDATE Syntax –

UPDATE table_name
SET column1 = value1, column2 = value2, …, columnN = valueN 
[WHERE condition1 AND condition2 AND … AND conditionN]; 

DELETE Syntax –

DELETE FROM table_name
[WHERE condition1 AND condition2 AND … AND conditionN]; 

  • column1, column2, …, columnN – Specifies the column names.
  • condition1, condition2, …, conditionN – Specifies the conditions that are used to filter the rows.
  • table_name – Specifies the name of the table.

Example -

Let us consider the 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 – Fetch rows using AND condition.

Requirement1 – Fetch all details of employees whose designation is "Analyst" and manager is "Employee3". The query was as follows –

SELECT * FROM employee_details
   WHERE designation = "Analyst"
   AND manager_id = "Employee3"; 

By executing above query, we can get results as shown below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000

Requirement2 – Fetch all details of employees whose designation is not "Analyst" and manager is "Employee3". The query was as follows –

SELECT * FROM employee_details
  WHERE manager_id = "Employee3"
  AND NOT designation = "Analyst"; 

By executing above query, we can get results as shown below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000

Requirement3 – Fetch all details of employees whose dept_id is 1000 and designation is "Director" or "Manager". The query was as follows –

SELECT * FROM employee_details WHERE dept_id = 1000 
AND (designation = "Director" OR designation = "Manager"); 

By executing above query, we can get results as shown below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000

Requirement4 – Fetch employee details from employee_details table whose dept_id is 1000 and manager is "Employee3" order by salary in descending order. The query was as follows –

 SELECT emp_name , salary FROM employee_details WHERE dept_id = 1000
AND manager_id = 'Employee3' ORDER BY salary DESC; 

By executing above query, we can get results as shown below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
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 – Update the rows using AND condition.

Requirement – Update the employee salary whose id is 007 and manager is "Employee3" to 20000. The query was as follows –

UPDATE employee_details SET salary = 20000
WHERE manager_id = "Employe3" AND emp_id = 007; 

By executing above query, we can update the salary to 20000 whose emp_id is 007 and manager is "Employee3". The table row after updation as follows-

emp_id emp_name designation manager_id date_of_hire salary dept_id
007 Employee7 Clerk Employee3 2019-07-11 20000.00 1000

Scenario – Deleting a row using AND condition.

Requirement – Delete employee details whose designation is "Salesman" and manager is "Employee4". The query was as follows –

DELETE FROM employee_details WHERE designation = 'Salesman'
 AND manager_id = "Employee4"; 

By executing above query, we can delete employee details whose designation is "Salesman" and manager is "Employee4". The table after deletion is -

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