SQL AND Operator

SQL AND Operator

The SQL AND operator is used to combine multiple conditions in a SQL query's WHERE clause. This command is essential for retrieving rows that meet all specified criteria, allowing for precise and complex data filtering.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND ...;
  • SELECT: This is the SQL keyword used to specify the columns to retrieve.
  • column1, column2, ...: These specify the names of the columns to retrieve.
  • FROM: This is the SQL keyword used to specify the table.
  • table_name: This specifies the name of the table from which to retrieve the rows.
  • WHERE: This is the SQL keyword used to specify the conditions for filtering the rows.
  • condition1, condition2, ...: These specify the conditions that the rows must meet to be retrieved.

Example

Let's go through a complete example that includes creating a database, creating a table, inserting data into the table, and then using the AND operator to filter specific rows based on multiple conditions.

Step 1: Creating a Database

This step involves creating a new database named example_db.

CREATE DATABASE example_db;

In this example, we create a database named example_db.

Step 2: Creating a Table

In this step, we create a table named employees within the previously created database.

USE example_db;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    hire_date DATE
);

Here, we define the employees table with columns for id, first_name, last_name, email, and hire_date. The id column is set as the primary key and will auto-increment.

Step 3: Inserting Data into the Table

This step involves inserting some sample data into the employees table.

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('John', 'Doe', 'john.doe@example.com', '2023-01-01');
INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Jane', 'Smith', 'jane.smith@example.com', '2023-02-01');
INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Alice', 'Johnson', 'alice.johnson@example.com', '2023-03-01');
INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Bob', 'Brown', 'bob.brown@example.com', '2023-04-01');

Here, we insert four rows of data into the employees table.

Step 4: Using the AND Operator

This step involves retrieving rows from the employees table where the last_name is 'Doe' and the hire_date is '2023-01-01'.

SELECT * FROM employees
WHERE last_name = 'Doe' AND hire_date = '2023-01-01';

This command will return the rows from the employees table where the last_name is 'Doe' and the hire_date is '2023-01-01'.

You can also use the AND operator with other conditions and comparison operators, such as:

  • = (equal to)
  • != or <> (not equal to)
  • > (greater than)
  • < (less than)
  • >= (greater than or equal to)
  • <= (less than or equal to)
  • LIKE (pattern matching)
  • IN (matching any value in a list)
  • BETWEEN (within a range of values)

For example, to retrieve rows where the last_name is 'Doe' and the email contains 'example.com':

SELECT * FROM employees
WHERE last_name = 'Doe' AND email LIKE '%example.com';

This command will return the rows from the employees table where the last_name is 'Doe' and the email contains 'example.com'.