SQL WHERE Clause

Introduction

In this chapter, we will learn how to use the WHERE clause in SQL to filter the results of a query. The WHERE clause is used to specify conditions that must be met for the rows to be included in the result set. This chapter will cover the syntax, usage, and provide examples to help you understand how to use the WHERE clause effectively.

What is the WHERE Clause?

The WHERE clause is used to filter records in SQL. It is used to extract only those records that fulfill a specified condition. The WHERE clause can be used in SELECT, UPDATE, DELETE, and INSERT statements.

Syntax for WHERE

Basic Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition;

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

Example

Assume we have a table named employees:

SELECT first_name, last_name, email
FROM employees
WHERE department_id = 1;

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

Step-by-Step Example

1. Create a Sample Table

First, we will create a sample table named employees.

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

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)
);

2. Insert Sample Data

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


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);

3. Retrieve Specific Rows

To retrieve employees in department 1:

SELECT first_name, last_name, email
FROM employees
WHERE department_id = 1;

Output

first_name	last_name	email
Ramesh	Kumar	ramesh.kumar@example.com
Arjun	Singh	arjun.singh@example.com

Using Comparison Operators

Comparison operators can be used to specify conditions in the WHERE clause.

Examples

• Equal to (=):

  SELECT first_name, last_name, email
  FROM employees
  WHERE department_id = 1;
  

• Not equal to (!= or <>):

  SELECT first_name, last_name, email
  FROM employees
  WHERE department_id != 1;
  

• Greater than (>):

  SELECT first_name, last_name, email
  FROM employees
  WHERE salary > 55000;
  

• Less than (<):

  SELECT first_name, last_name, email
  FROM employees
  WHERE salary < 55000;
  

• Greater than or equal to (>=):

  SELECT first_name, last_name, email
  FROM employees
  WHERE salary >= 55000;
  

• Less than or equal to (<=):

  SELECT first_name, last_name, email
  FROM employees
  WHERE salary <= 55000;
  

Using Logical Operators

Logical operators can be used to combine multiple conditions in the WHERE clause.

Examples

• AND:

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

  This command retrieves employees in department 1 with a salary greater than 50000.

• OR:

  SELECT first_name, last_name, email
  FROM employees
  WHERE department_id = 1 OR department_id = 2;
  

  This command retrieves employees in department 1 or department 2.

• NOT:

  SELECT first_name, last_name, email
  FROM employees
  WHERE NOT department_id = 1;
  

  This command retrieves employees not in department 1.

Using Wildcards

Wildcards can be used with the LIKE operator to filter records based on patterns.

Examples

• % (percent sign): Represents zero, one, or multiple characters.

  SELECT first_name, last_name, email
  FROM employees
  WHERE first_name LIKE 'R%';
  

  This command retrieves employees whose first name starts with 'R'.

• _ (underscore): Represents a single character.

  SELECT first_name, last_name, email
  FROM employees
  WHERE first_name LIKE '_a%';
  

  This command retrieves employees whose first name has 'a' as the second character.

Conclusion

The WHERE clause is an essential tool for filtering records in SQL queries. This chapter covered the basic syntax, comparison operators, logical operators. Understanding how to use the WHERE clause effectively will enhance your ability to query and analyze your database data.