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.

SQL WHERE Clause - Example Table

1. Selecting Rows Based on a Single Condition

To select all students who are in the 9th grade:

</>
Copy
SELECT * FROM students
WHERE grade = '9th';

This query returns rows where the grade is “9th”, displaying the records for Bob and Eva.

2. Using WHERE with Multiple Conditions (AND)

To select students who are 14 years old and live in “Northside”:

</>
Copy
SELECT * FROM students
WHERE age = 14 AND locality = 'Northside';

This query retrieves students who match both conditions, age 14 and locality “Northside”, returning Bob and Eva.

SQL WHERE Clause - Using WHERE with Multiple Conditions

3. Using WHERE with Multiple Conditions (OR)

To select students who are either 15 years old or live in “Southend”:

</>
Copy
SELECT * FROM students
WHERE age = 15 OR locality = 'Southend';

This query returns all students who meet either condition, displaying records for Bob, Eva, and David.

SQL WHERE Clause - Using WHERE with Multiple Conditions

4. Using WHERE with Comparison Operators

To select students who are older than 14:

</>
Copy
SELECT * FROM students
WHERE age > 14;

This query returns students older than 14, displaying records for Bob, David, and Eva.

SQL WHERE Clause - Using WHERE with Comparison Operators

5. Using WHERE with LIKE for Pattern Matching

To select students whose names contain the letter “a”:

</>
Copy
SELECT * FROM students
WHERE name LIKE '%a%';

This query returns students with “a” in their names, displaying Alice, Charlie, David, and Eva.

SQL WHERE Clause - Using WHERE with LIKE for Pattern Matching

6. Using WHERE with IN to Filter Multiple Values

To select students who are in either “8th” or “10th” grade:

</>
Copy
SELECT * FROM students
WHERE grade IN ('8th', '10th');

This query returns all students in the specified grades, displaying records for Alice, Charlie, and David.

SQL WHERE Clause - Using WHERE with IN to Filter Multiple Values

FAQs for SQL WHERE

1. What does the SQL WHERE clause do?

The WHERE clause filters rows based on a specified condition, allowing you to retrieve, update, or delete only the rows that meet the criteria.

2. Can I use multiple conditions in a WHERE clause?

Yes, you can use multiple conditions with AND and OR operators to create complex filters in the WHERE clause.

3. How does the LIKE operator work in WHERE?

The LIKE operator is used for pattern matching, allowing you to filter rows based on partial matches within a column.

4. Can I use WHERE with other SQL commands?

Yes, the WHERE clause can be used with SELECT, UPDATE, and DELETE statements to filter data.

5. How does WHERE differ from HAVING?

The WHERE clause filters rows before grouping, while HAVING filters groups after aggregation. Use WHERE for raw data filtering and HAVING with aggregate functions.