SQL WHERE Clause

The SQL WHERE clause is used to filter records from a table based on a specified condition. By narrowing down the data that matches a particular criterion, WHERE allows you to retrieve, update, or delete only the rows that meet specific requirements, making it one of the most powerful clauses in SQL.

In this tutorial, we will go through the syntax of WHERE clause, and how to use it in SQL statements with the help of well detailed examples.

Syntax of SQL WHERE Clause

The basic syntax of the SQL WHERE clause is as follows:

</>
Copy
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Each part of this syntax has a specific purpose:

  • SELECT: Specifies the columns to be retrieved from the table.
  • FROM: Specifies the table from which to retrieve the data.
  • WHERE: Filters the rows based on a given condition. The condition can use operators like =, >, <, !=, LIKE, IN, and others to form logical expressions.

The WHERE clause can be used with other SQL statements, including SELECT, UPDATE, and DELETE, to filter data based on specific conditions.

Step-by-Step Examples with MySQL

We’ll go through various examples demonstrating the WHERE clause in MySQL. Using MySQL 8.0 with MySQL Workbench, we’ll use a sample students table with fields id, name, age, grade, and locality.

Setup for Examples: Creating the Database and Table

1. Open MySQL Workbench and create a new database:

</>
Copy
CREATE DATABASE school;

2. Select the school database:

</>
Copy
USE school;

3. Create a table named students:

</>
Copy
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    grade VARCHAR(10),
    locality VARCHAR(50)
);

4. Insert some sample records to use in our examples:

</>
Copy
INSERT INTO students (name, age, grade, locality)
VALUES
('Alice', 14, '8th', 'Northside'),
('Bob', 15, '9th', 'Westend'),
('Charlie', 14, '8th', 'Northside'),
('David', 16, '10th', 'Southend'),
('Eva', 15, '9th', 'Westend');

Examples: Using WHERE Clause in Queries

Now, let’s explore different scenarios of using the WHERE clause in SQL queries with this table.