SQL WHERE Clause

The WHERE clause in SQL is like a gatekeeper for data. It helps us find exactly what we're looking for in a huge database by using conditions to filter rows. Imagine a library filled with books, and you're searching for ones only about space. The "WHERE" clause acts like your personal guide, picking out books that match your interest, ignoring the rest. It's a powerful tool that makes sure you get just the data you need.

Syntax

Parameters

• column1, column2, ...: These are the fields (or columns) you want to retrieve or modify. In a SELECT statement, you can specify individual column names or use an asterisk (*) to select all columns.

• table_name: This is the name of the table from which you want to fetch or modify data.

• condition: This specifies the conditions that rows need to fulfill to be selected in the query outcomes. Conditions can be formed using a variety of operators.

Operators in the WHERE Clause

Operators in WHERE clause in SQL helps you to specify the various conditions that you would like to query from a database.

Here is a list of popular operators that are used in WHERE to select desired columns for a record.

Demo Database

Let's create a table named BookCollection. This table represents a collection of books, detailing each book's unique identifier, title, author, publication year, and genre.

Table:

Where Clause with SELECT Statement

Using the WHERE clause in SQL with the SELECT statement allows you to retrieve precisely the data you're interested in from a database, making it a fundamental aspect of SQL querying for efficient data manipulation and analysis.

Query:

Suppose you want to find all the books in the Science Fiction genre. The SQL query would be:

Output:

Executing the above query would yield the following result set:

This output displays all the entries from the BookCollection table where the Genre column matches 'Science Fiction'. The query filters out books from other genres, showcasing the power of the WHERE clause in refining search outcomes according to particular conditions.

WHERE Clause with UPDATE Statement

The WHERE clause in SQL can be used with the UPDATE statement to modify specific records in a table. It specifies which records should be updated based on a condition.

Query:

This query updates the genre of books written by "Evan Bright" to "Historical Fiction".

Output:

Since this is an UPDATE operation, the output will not be a table but rather an acknowledgment that rows have been updated. Updated table is:

WHERE Clause with Logical Operators

Logical operators like AND, OR, and NOT can be used within the WHERE clause in SQL to combine multiple conditions.

Query:

Output:

This query retrieves books published after 2019 that are also in the Science Fiction genre.

WHERE Clause with BETWEEN Operator

The BETWEEN operator is used to filter the result set within a certain range. It's inclusive, meaning it includes the boundary values.

Query:

Output:

WHERE Clause with LIKE Operator

The LIKE operator, when employed within the SQL WHERE clause, facilitates the search for a designated pattern within a column.

Query:

Output:

This query finds books with titles that contain the word "Secret".

WHERE Clause with IN Operator

The IN operator enables the specification of various values within a WHERE clause, effectively acting as a shorthand for multiple OR conditions.

Query:

Output:

This query selects books authored by either "Mira Stellar" or "Luna Green".