SQL WHERE Clause

🎓 Top 15 Udemy Courses (80-90% Discount): My Udemy Courses - Ramesh Fadatare — All my Udemy courses are real-time and project oriented courses.

▶️ Subscribe to My YouTube Channel (178K+ subscribers): Java Guides on YouTube

▶️ For AI, ChatGPT, Web, Tech, and Generative AI, subscribe to another channel: Ramesh Fadatare on YouTube

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.

My Top and Bestseller Udemy Courses. The sale is going on with a 70 - 80% discount. The discount coupon has been added to each course below:

Build REST APIs with Spring Boot 4, Spring Security 7, and JWT

🆕 High-Demand 80–90% OFF

[NEW] Learn Apache Maven with IntelliJ IDEA and Java 25

🆕 High-Demand 80–90% OFF

ChatGPT + Generative AI + Prompt Engineering for Beginners

🚀 Trending Now 80–90% OFF

Spring 7 and Spring Boot 4 for Beginners (Includes 8 Projects)

🔥 Bestseller 80–90% OFF
Available in Udemy for Business

Building Real-Time REST APIs with Spring Boot - Blog App

🔥 Bestseller 80–90% OFF
Available in Udemy for Business

Building Microservices with Spring Boot and Spring Cloud

🌟 Top Rated 80–90% OFF
Available in Udemy for Business

Java Full-Stack Developer Course with Spring Boot and React JS

🔥 Bestseller 80–90% OFF
Available in Udemy for Business

Build 5 Spring Boot Projects with Java: Line-by-Line Coding

🌟 Top Rated 80–90% OFF

Testing Spring Boot Application with JUnit and Mockito

🔥 Bestseller 80–90% OFF
Available in Udemy for Business

Spring Boot Thymeleaf Real-Time Web Application - Blog App

🔥 Bestseller 80–90% OFF
Available in Udemy for Business

Master Spring Data JPA with Hibernate

🔥 Bestseller 80–90% OFF
Available in Udemy for Business

Spring Boot + Apache Kafka Course - The Practical Guide

🎓 Student Favorite 80–90% OFF
Available in Udemy for Business

Java Testing: Mastering JUnit 5 Framework

🆕 New 80–90% OFF

Reactive Programming in Java: Spring WebFlux and Testing

🌟 Top Rated 80–90% OFF

Spring Boot + RabbitMQ Course - The Practical Guide

🌟 Top Rated 80–90% OFF
Available in Udemy for Business

Functional Programming in Java (Includes Java Collections)

🎓 Student Favorite 80–90% OFF

ChatGPT for Java Developers: Boost Your Productivity with AI

🚀 Trending Now 80–90% OFF