MySQL AND Operator

MySQL AND Operator

The MySQL AND operator is used to combine multiple conditions in a SQL query. This operator is essential for filtering records based on more than one condition.

Syntax

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

The AND operator has the following components:

  • column1, column2, ...: The columns to be retrieved.
  • table_name: The name of the table from which to retrieve the data.
  • condition1: The first condition to filter the records.
  • condition2: The second condition to filter the records.

Example MySQL AND Operator

Let's look at some examples of the MySQL AND operator:

Step 1: Using the Database

USE mydatabase;

This query sets the context to the database named mydatabase.

MySQL USE DATABASE

Step 2: Creating a Table

Create a table to work with:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

This query creates a table named employees with columns for id, first_name, last_name, department, and salary.

MySQL CREATE TABLE

Step 3: Inserting Initial Rows

Insert some initial rows into the table:

INSERT INTO employees (first_name, last_name, department, salary)
VALUES ('John', 'Doe', 'HR', 50000.00),
       ('Jane', 'Smith', 'IT', 60000.00),
       ('Jim', 'Brown', 'IT', 55000.00),
       ('Jake', 'White', 'HR', 52000.00),
       ('Jill', 'Green', 'Marketing', 45000.00);

This query inserts five rows into the employees table.

MySQL INSERT INTO TABLE

Step 4: Using AND Operator with WHERE Clause

Use the AND operator to filter records based on multiple conditions:

SELECT * 
FROM employees 
WHERE department = 'IT' AND salary > 55000;

This query retrieves all columns from the employees table where the department is 'IT' and the salary is greater than 55000.

MySQL AND OPERATOR WITH WHERE CLAUSE

Step 5: Combining Multiple Conditions

Use the AND operator to combine multiple conditions:

SELECT * 
FROM employees 
WHERE department = 'HR' AND salary BETWEEN 50000 AND 55000;

This query retrieves all columns from the employees table where the department is 'HR' and the salary is between 50000 and 55000.

MySQL AND OPERATOR COMBINING MULTIPLE CONDITIONS

Step 6: Using AND Operator with Multiple Columns

Use the AND operator with multiple columns:

SELECT first_name, last_name 
FROM employees 
WHERE department = 'Marketing' AND salary < 50000;

This query retrieves the first_name and last_name columns from the employees table where the department is 'Marketing' and the salary is less than 50000.

MySQL AND OPERATOR WITH MULTIPLE COLUMNS

Conclusion

The MySQL AND operator is a powerful tool for filtering records based on multiple conditions. Understanding how to use the AND operator is essential for effective data querying and analysis in MySQL.