How to Use WHERE Clause in SQL
Structured Query Language or SQL is a domain-specific language used for managing databases. A WHERE clause is one of the essential components of SQL that helps retrieve specific data from a database. It is used along with SELECT, UPDATE, DELETE, and other SQL statements and is particularly useful when working with large databases. In this article, we will discuss how to use the WHERE clause in SQL and its various functionalities.
The Basics of the WHERE Clause
The WHERE clause in SQL helps you filter data from a database based on a specified condition. It allows you to retrieve rows from a table that satisfy a specific condition or set of conditions. The syntax for WHERE clause is as follows:
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Here, column1, column2 etc., represents the names of the columns in the table, table_name represents the name of the table, and condition represents the filter criteria.
For example, let’s consider a table “Employees” with columns “ID,” “Name,” “Salary,” and “Department.”
ID Name Salary Department
1 John 5000 Sales
2 Jack 6000 HR
3 Sarah 8000 Marketing
4 Emily 6500 Finance
5 Chris 4000 Sales
To retrieve the details of employees with a salary greater than 6000, we use the following SQL statement:
SELECT Name, Salary, Department
FROM Employees
WHERE Salary > 6000;
This query returns the following results:
Name Salary Department
Sarah 8000 Marketing
Emily 6500 Finance
Advanced Usage of WHERE Clause
Using Comparison Operators
The WHERE clause in SQL supports various comparison operators such as equals to ( = ), greater than( > ), less than( < ), greater than or equal to ( >= ), less than or equal to ( <= ), not equal to ( <> or != ).
Let us consider the following Employees table and query:
ID Name Salary
1 John 5000
2 Jack 6000
3 Sarah 8000
4 Emily 6500
5 Chris 4000
To retrieve employees with a salary less than 6000, we use:
SELECT Name, Salary
FROM Employees
WHERE Salary < 6000;
This query will return the following output:
Name Salary
John 5000
Chris 4000
Using Logical Operators
SQL also supports logical operators such as AND, OR, NOT, etc.
For example, to retrieve employees with a salary between 5000 and 8000, we use the following SQL statement:
SELECT Name, Salary
FROM Employees
WHERE Salary >= 5000 AND Salary <= 8000;
This query will return the following output:
Name Salary
John 5000
Jack 6000
Sarah 8000
Emily 6500
Using Wildcards
SQL also supports the use of wildcards in the WHERE clause. Wildcards are placeholders that can be used to match any character or groups of characters. The commonly used wildcards are:
- The percent sign (%): matches any number of characters.
- The underscore (_): matches a single character.
Let us consider the following table, “Employees”:
ID Name Salary
1 John Doe 5000
2 Jack Adam 6000
3 Sarah Lee 8000
4 Emily Roe 6500
To retrieve all employees whose name starts with “S,” we use the following SQL statement:
SELECT Name, Salary
FROM Employees
WHERE Name LIKE 'S%';
This query will return the following output:
Name Salary
Sarah Lee 8000
Using Subqueries
A subquery is a query inside another query. It is mostly used in the WHERE clause to filter data. The subquery is enclosed within parentheses and is placed inside the WHERE clause.
Let us consider two tables, the “Orders” table and the “Customers” table.
Customers
CustomerID CustomerName
1 John Doe
2 Jack Adam
3 Sarah Lee
4 Emily Roe
Orders
OrderID CustomerID OrderDate
1 3 2019-11-12
2 2 2019-11-14
3 2 2019-11-15
To retrieve customers who have placed orders, we use the following SQL statement:
SELECT CustomerName
FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders);
This query will return the following output:
CustomerName
Jack Adam
Sarah Lee
Frequently Asked Questions (FAQs)
What is the WHERE clause in SQL?
The WHERE clause in SQL is used to filter data based on certain conditions and is used across different SQL statements.
2. What are the comparison operators used in the WHERE clause?
The comparison operators used in the WHERE clause are equals to ( = ), greater than( > ), less than( < ), greater than or equal to ( >= ), less than or equal to ( <= ), not equal to ( <> or != ).
3. What are wildcards in SQL?
Wildcards are placeholders that can be used to match any character(s). Commonly used wildcards are % and _.
4. Can we use subqueries in the WHERE clause?
Yes, subqueries can be used in the WHERE clause to filter data.
5. What are the logical operators used in SQL?
The logical operators used in SQL are AND, OR, NOT, etc and they are used for combining conditions in WHERE clause.
Conclusion
The WHERE clause in SQL is an important feature that enables efficient filtering of data from a database. Whether you are a beginner in SQL or a seasoned developer, this article has given you an in-depth understanding of how to use the WHERE clause. We have covered various functionalities such as comparison operators, logical operators, wildcards, and subqueries in the WHERE clause. We hope this article has been helpful, and you now have some excellent pointers to help you write more efficient SQL queries.
Useful Links:
📕 Related articles about MySQL
- How to use DIVISION in SQL
- Installing MySQL on Linux: A Comprehensive Guide
- How to use EXISTS in SQL
- MySQL Delete Data: A Comprehensive Guide to Data Deletion in MySQL
- Overview of MySQL Programs: An In-Depth Breakdown
- MySQL CREATE FUNCTION Statement: An In-Depth Guide