SQL AND Operator

Introduction

In this chapter, we will learn how to use the AND operator in SQL. The AND operator is used to combine multiple conditions in a SQL query. This chapter will cover the syntax, usage, and provide examples to help you understand how to use the AND operator effectively.

What is the AND Operator?

The AND operator is used to combine two or more conditions in a SQL statement. When using the AND operator, all the conditions must be true for the row to be included in the result set. It is commonly used in the WHERE clause to filter records based on multiple criteria.

Syntax for AND

Basic Syntax

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

• column1, column2, ...: The columns you want to retrieve.
• table_name: The name of the table from which you want to retrieve data.
• condition1, condition2, ...: The conditions that must be true for a row to be included in the result set.

Example

Assume we have a table named employees:

SELECT first_name, last_name, email
FROM employees
WHERE department_id = 1 AND salary > 50000;

This command retrieves the first_name, last_name, and email columns from the employees table where the department_id is 1 and the salary is greater than 50000.

Step-by-Step Example

1. Create Sample Tables

First, we will create two sample tables named employees and departments.

CREATE TABLE employees (
id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
department_id INT,
salary DECIMAL(10, 2)
);

CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(50)
);

2. Insert Sample Data

INSERT INTO employees (first_name, last_name, email, department_id, salary)
VALUES
('Ramesh', 'Kumar', 'ramesh.kumar@example.com', 1, 50000),
('Sita', 'Patel', 'sita.patel@example.com', 2, 60000),
('Arjun', 'Singh', 'arjun.singh@example.com', 1, 55000),
('Priya', 'Sharma', 'priya.sharma@example.com', 2, 62000),
('Ramesh', 'Kumar', 'ramesh.kumar2@example.com', 3, 50000);

INSERT INTO departments (department_id, department_name)
VALUES
(1, 'HR'),
(2, 'Finance'),
(3, 'IT');

3. Using the AND Operator

To retrieve employees in department 1 with a salary greater than 50000:

SELECT first_name, last_name, email
FROM employees
WHERE department_id = 1 AND salary > 50000;

Output

first_name	last_name	email
Arjun	Singh	arjun.singh@example.com

4. Combining Multiple Conditions

You can combine more than two conditions using the AND operator.

Example

To retrieve employees in department 1 with a salary greater than 50000 and whose first name starts with 'A':

SELECT first_name, last_name, email
FROM employees
WHERE department_id = 1 AND salary > 50000 AND first_name LIKE 'A%';

Output

first_name	last_name	email
Arjun	Singh	arjun.singh@example.com

Using AND with Other Operators

The AND operator can be used with other SQL operators like OR, NOT, IN, BETWEEN, etc., to create more complex queries.

Example with OR

To retrieve employees in department 1 or employees with a salary greater than 60000:

SELECT first_name, last_name, email
FROM employees
WHERE department_id = 1 OR salary > 60000;

Output

first_name	last_name	email
Ramesh	Kumar	ramesh.kumar@example.com
Sita	Patel	sita.patel@example.com
Arjun	Singh	arjun.singh@example.com
Priya	Sharma	priya.sharma@example.com

Example with NOT

To retrieve employees not in department 2 and with a salary greater than 50000:

SELECT first_name, last_name, email
FROM employees
WHERE NOT department_id = 2 AND salary > 50000;

Output

first_name	last_name	email
Arjun	Singh	arjun.singh@example.com

Example with IN

To retrieve employees in departments 1 and 3 with a salary greater than 50000:

SELECT first_name, last_name, email
FROM employees
WHERE department_id IN (1, 3) AND salary > 50000;

Output

first_name	last_name	email
Arjun	Singh	arjun.singh@example.com

Example with BETWEEN

To retrieve employees with a salary between 50000 and 60000 in department 1:

SELECT first_name, last_name, email
FROM employees
WHERE salary BETWEEN 50000 AND 60000 AND department_id = 1;

Output

first_name	last_name	email
Ramesh	Kumar	ramesh.kumar@example.com

Conclusion

The AND operator is a fundamental tool for combining multiple conditions in SQL queries. This chapter covered the basic syntax, combining multiple conditions, and using the AND operator with other SQL operators. Understanding how to use the AND operator effectively will enhance your ability to query and analyze your database data.