DbSchema Tutorial | SQL WHERE Clause

---

SQL Tutorial • 21-Jul-2023

by Malik Umair Sajjad

---

Welcome to this comprehensive guide on the SQL WHERE Clause for beginners. By the end of this tutorial, you will have a thorough understanding of how the WHERE clause functions in SQL, including its use with various operators.

SQL, or Structured Query Language, is a powerful tool that allows you to interact with databases. One of the crucial components of SQL is the WHERE clause. It helps filter records and retrieve data as per specific conditions. Let’s delve deeper into this topic!

Table of Contents

1. What is the WHERE Clause?
2. Syntax of the WHERE Clause
3. Using WHERE Clause with Different Operators
4. Common Mistakes
5. FAQ’s
6. Practice Questions
7. Conclusion

What is the WHERE Clause?

The SQL WHERE clause is used to filter the results of a SELECT, UPDATE, or DELETE query. It specifies a condition while fetching the data from a single table or by joining with multiple tables. If the given condition is satisfied (i.e., returns TRUE), then only it returns specific value from the table.

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

Syntax of the WHERE Clause

The basic syntax of the WHERE clause can be given with the SELECT, UPDATE or DELETE command followed by a condition.

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

The condition in the WHERE clause could be equality, or it could be any condition like comparison or range. The WHERE clause basically returns the rows where condition is TRUE.

Using WHERE Clause with Different Operators

You can use the WHERE clause with different operators to create the condition. Below is a summary of the operators you can use with the WHERE clause.

Operator	Description
=	Equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
<> or !=	Not equal
BETWEEN	Between an inclusive range
LIKE	Search for a pattern
IN	If you know the exact value you want to return for at least one of the columns

Here are some examples to demonstrate these operators in action with WHERE clause.

-- Selects all records where age is equal to 30
SELECT * FROM Customers
WHERE age = 30;

-- Selects all records where age is greater than 30
SELECT * FROM Customers
WHERE age > 30;

-- Selects all records where age is less than or equal to 30
SELECT * FROM Customers
WHERE age <= 30;

-- Selects all records where the age is not equal to 30
SELECT * FROM Customers
WHERE age <> 30;

-- Selects all records where the age is between 25 and 35
SELECT * FROM Customers
WHERE age BETWEEN 25 AND 35;

-- Selects all records where the city starts with 'L'
SELECT * FROM Customers
WHERE city LIKE 'L%';

-- Selects all records where the city is one of 'London', 'Berlin', 'Paris'
SELECT * FROM Customers
WHERE city IN ('London', 'Berlin', 'Paris');

Demo Database:

Consider the following table Customers:

Table: Customers

CustomerID	Name	Age	City
1	John	28	New York
2	Alice	30	London
3	James	32	New York
4	Clara	27	Paris
5	Bob	35	London
6	Jules	22	Berlin
7	Jimmy	45	Los Angeles

Results from the Queries:

Following results will be obtained by executing above queries on Customers table:

• Selects all records where age is equal to 30:

CustomerID	Name	Age	City
2	Alice	30	London

• Selects all records where age is greater than 30:

CustomerID	Name	Age	City
3	James	32	New York
5	Bob	35	London
7	Jimmy	45	Los Angeles

• Selects all records where age is less than or equal to 30:

CustomerID	Name	Age	City
1	John	28	New York
2	Alice	30	London
4	Clara	27	Paris
6	Jules	22	Berlin

• Selects all records where the age is not equal to 30:

CustomerID	Name	Age	City
1	John	28	New York
3	James	32	New York
4	Clara	27	Paris
5	Bob	35	London
6	Jules	22	Berlin
7	Jimmy	45	Los Angeles

• Selects all records where the age is between 25 and 35:

CustomerID	Name	Age	City
1	John	28	New York
2	Alice	30	London
3	James	32	New York
5	Bob	35	London

• Selects all records where the city starts with L‘

CustomerID	Name	Age	City
2	Alice	30	London
5	Bob	35	London
7	Jimmy	45	Los Angeles

• Selects all records where the city is one of London, Berlin, Paris:

CustomerID	Name	Age	City
2	Alice	30	London
4	Clara	27	Paris
5	Bob	35	London
6	Jules	22	Berlin

Common Mistakes

While using the WHERE clause, it’s quite common for beginners to make some errors. Here are a few common mistakes:

1. Ignoring Case Sensitivity: SQL is case-insensitive, but data inside the database may not be. Always consider the case when working with text conditions.

2. Using = instead of LIKE for Pattern Matching: For pattern matching, always use the LIKE keyword and not the = operator.

3. Misuse of NULL: Remember, NULL is different from an empty string or a zero. Use IS NULL or IS NOT NULL to filter out NULL values, not = or !=.

4. Forgetting Parentheses in Complex Conditions: When using AND and OR operators in the same WHERE clause, use parentheses to make sure conditions are executed in the correct order.

Frequently Asked Questions

1. What is the difference between the WHERE and HAVING clause in SQL?

   The WHERE clause introduces a condition on individual rows; the HAVING clause introduces a condition on aggregations or results of a selection where a single result, such as average, sum, count, etc., has to be compared against a given condition.

2. Can we use the WHERE clause with the UPDATE and DELETE commands?

   Yes, the WHERE clause can be used with SELECT, UPDATE, and DELETE commands to filter & manipulate data on the basis of a certain condition.

3. Can I use two or more conditions in a WHERE clause?

   Yes, you can use multiple conditions in the WHERE clause using AND or OR operators. Make sure to use parentheses for clarity when combining these operators.

Practice Questions

To reinforce what you’ve learned, try out these practice questions:

1. Write a query to find all customers whose names start with ‘J’.
2. Write a query to find all customers who live in ‘New York’ and their age is above 25.
3. Write a query to find all products that have a price between $100 and $500.
4. Write a query to find all employees who do not work in the ‘Sales’ department.

Conclusion

The SQL WHERE clause is a powerful tool that allows you to filter the results of your SQL queries. By using different operators, you can create complex conditions to retrieve specific data from your databases. Keep practicing with different operators and conditions to improve your SQL skills.

Thank you for following along with this tutorial. Keep practicing and stay tuned for more comprehensive SQL tutorials!

Happy coding!

---

Remember: Practice makes perfect! The more you use these commands in different combinations, the more comfortable you’ll become with the SQL syntax.