MySQL AND Operator

Introduction

In this chapter, we will learn about the AND operator in MySQL. The AND operator is used to combine multiple conditions in a SQL statement. It ensures that all the specified conditions are true for a row to be included in the result set. The AND operator is commonly used in SELECT, UPDATE, DELETE, and other SQL statements to filter data based on multiple criteria. We will cover the syntax, examples, and important considerations for using the AND operator.

Syntax

The basic syntax for the AND operator is:

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND ...;
  • condition1, condition2, ...: The conditions that must be true for a row to be included in the result set.

Using AND Operator

Example with SELECT

SELECT first_name, last_name, email
FROM students
WHERE last_name = 'Sharma' AND enrollment_date > '2023-01-01';

This example retrieves the first_name, last_name, and email columns from the students table where the last_name is 'Sharma' and the enrollment_date is after January 1, 2023.

Example with UPDATE

UPDATE students
SET email = 'rahul.sharma@newemail.com'
WHERE first_name = 'Rahul' AND last_name = 'Sharma';

This example updates the email column for the row where the first_name is 'Rahul' and the last_name is 'Sharma'.

Example with DELETE

DELETE FROM students
WHERE first_name = 'Rahul' AND last_name = 'Sharma';

This example deletes the row where the first_name is 'Rahul' and the last_name is 'Sharma'.

Combining AND with Other Operators

Example with OR

SELECT first_name, last_name, email
FROM students
WHERE (last_name = 'Sharma' AND enrollment_date > '2023-01-01') OR email LIKE '%@example.com';

This example retrieves the first_name, last_name, and email columns from the students table where the last_name is 'Sharma' and the enrollment_date is after January 1, 2023, or the email ends with '@example.com'.

Example with NOT

SELECT first_name, last_name, email
FROM students
WHERE last_name = 'Sharma' AND NOT email LIKE '%@oldemail.com';

This example retrieves the first_name, last_name, and email columns from the students table where the last_name is 'Sharma' and the email does not end with '@oldemail.com'.

Full Example

Let's go through a full example where we create a table, insert data into it, and use the AND operator to filter records.

  1. Create a Database:
CREATE DATABASE school;
  1. Select the Database:
USE school;
  1. Create a Table:
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    enrollment_date DATE
);
  1. Insert Data into the Table:
INSERT INTO students (first_name, last_name, email, enrollment_date) VALUES
('Rahul', 'Sharma', 'rahul.sharma@example.com', '2023-07-01'),
('Priya', 'Singh', 'priya.singh@example.com', '2023-07-02'),
('Amit', 'Kumar', 'amit.kumar@example.com', '2023-07-03'),
('Neha', 'Verma', 'neha.verma@example.com', '2023-07-04'),
('Sahil', 'Mehta', 'sahil.mehta@example.com', '2023-07-05');
  1. Use the AND Operator with SELECT:
SELECT first_name, last_name, email
FROM students
WHERE last_name = 'Sharma' AND enrollment_date > '2023-01-01';

Output

first_name last_name email
Rahul Sharma rahul.sharma@example.com
  1. Use the AND Operator with UPDATE:
UPDATE students
SET email = 'rahul.sharma@newemail.com'
WHERE first_name = 'Rahul' AND last_name = 'Sharma';
  1. Verify the Update:
SELECT first_name, last_name, email
FROM students
WHERE first_name = 'Rahul' AND last_name = 'Sharma';

Output

first_name last_name email
Rahul Sharma rahul.sharma@newemail.com
  1. Use the AND Operator with DELETE:
DELETE FROM students
WHERE first_name = 'Rahul' AND last_name = 'Sharma';
  1. Verify the Deletion:
SELECT first_name, last_name, email
FROM students
WHERE first_name = 'Rahul' AND last_name = 'Sharma';

Output

(empty result set)

Important Considerations

  • Order of Conditions: Ensure that conditions combined with the AND operator are logically ordered and do not conflict with each other.
  • Performance: Using multiple conditions with the AND operator can impact performance, especially on large datasets. Ensure appropriate indexing to optimize query performance.

Conclusion

The AND operator is used for combining multiple conditions in MySQL queries. This chapter covered how to use the AND operator with SELECT, UPDATE, and DELETE statements, provided examples, and discussed important considerations. In the next chapter, we will learn how to use the OR operator to filter data based on multiple conditions.

Comments