`WHERE` Clause in DBMS (MySQL) [ English ]

Languages

• English

Introduction

In a Database Management System (DBMS), tables may contain a large amount of data. When retrieving or modifying data, users often need only specific records instead of the entire table. The WHERE clause is used to filter records based on a specified condition.

The WHERE clause is commonly used with SQL commands such as SELECT, UPDATE, and DELETE to work with particular rows in a table.

Definition

The WHERE clause is used in SQL to specify conditions for retrieving, updating, or deleting specific records from a table.

Only the rows that satisfy the given condition are affected by the query.

Syntax

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

Where:

• column1, column2 – Columns to be retrieved.
• table_name – The name of the table.
• condition – The condition used to filter records.

Example Table

Suppose we have a table named students.

Example 1: Selecting Records with a Condition

SELECT * FROM students
WHERE course = 'BCA';

Output

Only students enrolled in BCA are displayed.

Example 2: Using WHERE with Numeric Conditions

SELECT * FROM students
WHERE age > 20;

Output

Common Comparison Operators

Example:

SELECT * FROM students
WHERE age >= 21;

Using Logical Operators with WHERE

The WHERE clause can also use logical operators to combine conditions.

Example using AND

SELECT * FROM students
WHERE course = 'BCA' AND age > 19;

Example using OR

SELECT * FROM students
WHERE course = 'BCA' OR course = 'BBA';

Using WHERE with UPDATE

UPDATE students
SET course = 'BCA'
WHERE id = 2;

Only the record with id = 2 will be updated.

Using WHERE with DELETE

DELETE FROM students
WHERE id = 3;

This will delete the record where id = 3.

Key Points

• The WHERE clause is used to filter records based on conditions.
• It can be used with SELECT, UPDATE, and DELETE statements.
• It supports comparison operators and logical operators.
• It helps retrieve or modify specific rows instead of the entire table.

Summary

The WHERE clause is an essential part of SQL that allows users to retrieve, update, or delete specific records based on conditions. By filtering data according to requirements, it makes database operations more precise and efficient.