DbSchema Tutorial | SQL WHERE Clause
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
- What is the WHERE Clause?
- Syntax of the WHERE Clause
- Using WHERE Clause with Different Operators
- Common Mistakes
- FAQ’s
- Practice Questions
- 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:
Ignoring Case Sensitivity: SQL is case-insensitive, but data inside the database may not be. Always consider the case when working with text conditions.
Using = instead of LIKE for Pattern Matching: For pattern matching, always use the LIKE keyword and not the = operator.
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 !=.
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
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.
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.
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:
- Write a query to find all customers whose names start with ‘J’.
- Write a query to find all customers who live in ‘New York’ and their age is above 25.
- Write a query to find all products that have a price between $100 and $500.
- 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.
