MySQL AND Condition

Last Updated: 25 July, 2023

In MySQL, the AND condition is used in the WHERE clause of a query to combine multiple conditions together. It is used to filter rows based on multiple criteria, and all the conditions connected by AND must be true for a row to be included in the query result.

The basic syntax of a MySQL query with AND condition is as follows:

  SELECT column1, column2, ...
  FROM table_name
  WHERE condition1 AND condition2 AND ...;

Here, condition1, condition2, etc., represent the individual conditions that we want to combine using the AND operator.

The AND condition used in a SELECT, INSERT, UPDATE, or DELETE statement in MySQL.

'AND' Condition in SELECT Statement

If we need to fetch some specific types or conditional records from one table or more tables, we use the AND condition in the SELECT statement with the WHERE clause.

Let's see an example of AND condition in SELECT statement. Suppose we have a table called employees with the following structure:

Table: employees

  +----+-----------+------------+--------+------------+---------------+
  | ID | Name      | Department |   Age  |   Salary   |  JoiningDate  | 
  +----+-----------+------------+--------+------------+---------------+
  | 1  | Rajesh    | Sales      |   43   |    54,000  |   2023-06-11  |
  | 2  | Kamal     | HR         |   35   |    38,000  |   2021-01-23  |
  | 3  | Vimal     | Sales      |   52   |    68,000  |   2022-04-08  |
  | 4  | Hina      | Marketing  |   38   |    72,000  |   2018-08-16  |
  | 5  | Jyanti    | Finance    |   29   |    60,000  |   2010-02-24  |
  | 6  | Sanjay    | HR         |   31   |    55,000  |   2023-01-29  |
  | 7  | Amar      | Finance    |   27   |    40,000  |   2023-07-19  | 
  +----+-----------+------------+--------+------------+---------------+

If we want to retrieve those employee records whose age is greater than 30 and salary is more than 50,000.

 SELECT * FROM employees
 WHERE Age > 30 AND Salary > 50000;

This query will return the following result:

  +----+-----------+------------+--------+------------+---------------+
  | ID | Name      | Department |   Age  |   Salary   |  JoiningDate  | 
  +----+-----------+------------+--------+------------+---------------+
  | 1  | Rajesh    | Sales      |   43   |    54,000  |   2023-06-11  |
  | 3  | Vimal     | Sales      |   52   |    68,000  |   2022-04-08  |
  | 4  | Hina      | Marketing  |   38   |    72,000  |   2018-08-16  |
  | 6  | Sanjay    | HR         |   31   |    55,000  |   2023-01-29  |
  +----+-----------+------------+--------+------------+---------------+

Multiple AND conditions

In the MySQL query, we can add one or more AND conditions based on the requirement. Let's see an example of two AND conditions in the MySQL query.

  SELECT * FROM employees
  WHERE Age > 30 AND Salary > 50000 AND Department = 'Sales';

In the query, we have added three conditions using two AND operators. Fetching those employee records whose Age is greater than 30, and salary is more than 50,000, and department is sales.

This query will return the following result:

   +----+-----------+------------+--------+------------+---------------+
   | ID | Name      | Department |   Age  |   Salary   |  JoiningDate  | 
   +----+-----------+------------+--------+------------+---------------+
   | 1  | Rajesh    | Sales      |   43   |    54,000  |   2023-06-11  |
   | 3  | Vimal     | Sales      |   52   |    68,000  |   2022-04-08  |
   +----+-----------+------------+--------+------------+---------------+

'AND' Condition in INSERT Statement

We use AND in the INSERT statement if we are fetching the records from another table based on some conditions where all the conditionals are true, then records will be fetched and inserted into the table.

Let's see an example of AND condition in INSERT statement.

  INSERT INTO all_students
  (student_id, student_name, student_roll)
  SELECT stu_id, stu_name, stu_roll
  FROM kg_students
  WHERE stu_name = 'Rajesh Raj'
  AND stu_id = 14;

'AND' Condition in UPDATE Statement

We use AND in the UPDATE statement if we need to update a conditional record(s) where the condition is more than one and all the conditions are true, then records will be updated in the table.

Let's see an example of AND condition in UPDATE statement.

  UPDATE students
  SET status = 'Present'
  WHERE student_name = 'Deepak Kumar'
  AND roll = 10;

'AND' Condition in DELETE Statement

We use the AND in DELETE statement if we need to delete a conditional record(s) where the condition is more than one and all the conditions are true, then records will be deleted otherwise records will not be deleted from the table.

Let's see an example of AND condition in DELETE statement.

  DELETE FROM students
  WHERE student_name = 'Taniya Singh'
  AND roll = 27;