This article will talk about the SQL WHERE statement.
Relational database tables might contain billions of data rows. In a practical scenario, you might not want to work with entire record sets at one time. For example, if we have a table that records all of a customer’s orders, we might not want to display all the data available in the application since the table was created. You might need data for a specific order, orders for a specific duration such as quarters, years or orders for a specific client or region.
The structured query language (SQL) uses the SQL WHERE Clause for data filtering based on the applied conditions. It is commonly used in the Select, Update, or delete statement.
Let’s go ahead and explore the WHERE clause, its syntax, usage for single or multiple conditions, including and excluding data based on query expression.
Introduction to SQL Where Clause
The Where clause is helpful in the SQL Statements Select, Update, Delete for filtering data satisfying a specific condition. The following SQL query retrieves all data stored in the [SalesLT].[SalesOrderDetail] table. As shown below, it retrieves 540 records.
1 2 3 |
SELECT * FROM SalesLT.SalesOrderDetail |
Suppose your customer calls up in customer service desk for getting some information on his order. If the table is enormous and you retrieve all records for looking for specific customer order id. In this case,
- The query execution would take longer due to the number of records
- Your turnaround time would be higher, and you might not satisfy customers due to a long hold
- It is tough to look for a specific order id if the data set is vast and unsorted
Therefore, you can add where clause after the from clause and specify the condition. The query optimizer would retrieve only specific rows satisfying conditions and display them to you.
1 2 3 |
SELECT * FROM SalesLT.SalesOrderDetail WHERE SalesOrderID=71776 |
Let’s compare both query executions from a performance point of view.
- Query 1 has a relative cost of 70% in the batch, and it uses the clustered index scan operator
- Query 2 is relatively cheaper than query (30% cost), and it uses clustered index seek operator
If we deep dive into clustered index scan and seek operators, we can see a difference in the estimated CPU Cost, IO cost, the number of rows read.
Note: The execution plan operators and their performance depend on various factors such as index, statistics, query writing techniques.
SQL WHERE statement Examples
Let’s explore SQL WHERE Clause examples in this section.
SQL WHERE statement with Numeric comparison
You can use where clause with a numeric comparison for a supported column type such as int, bigint.
1 2 3 |
SELECT * FROM SalesLT.SalesOrderDetail WHERE OrderQty=1 |
Note: You cannot use numeric comparison for a column with other data types such as varchar, nvarchar.
SQL WHERE statement with Characters comparison
By default, SQL Server performs case-insensitive searches. You can filter records from the table using the characters such as strings. For example, below, the query returns the records having product names specified in the where clause.
1 2 3 |
SELECT * FROM SalesLT.Product WHERE name='awc Logo Cap' |
If we specify strings in the upper case or lower, SQL Server returns similar results.
1 2 3 |
SELECT * FROM SalesLT.Product WHERE name='AWC LOGO CAP' |
SQL Where statement with Comparison operators
SQL Server has various comparison operators to construct the condition and returns results satisfied the query. The comparison operators are as below.
- equal operator (=)
- greater than(>) and less than (<)
- greater than or equal (>=)
- less than or equal (<=)
- not equal( <>)
For example, the below query uses not equal (<>) operator in the where clause to exclude the product name specified in the where clause.
1 2 3 |
SELECT * FROM SalesLT.Product WHERE name<>'AWC LOGO CAP' |
Similarly, the below query returns all products having a Product ID greater than or equal to 800.
1 2 3 |
SELECT * FROM SalesLT.Product WHERE ProductID >= 800 |
SQL Where statement with Logical operators
SQL Server has logical operators that return the true or false result on the specific conditions. These logical operators are AND, OR and NOT.
- AND operators In the AND operators, all expressions should satisfy to get the results.
- The query returns records that satisfy the following conditions.
- Color= Red
- Name= Road-150 Red, 44
1 2 3 |
SELECT * FROM SalesLT.Product WHERE color= 'Red' and [Name]='Road-150 Red, 44' |
If either condition does not return true, the query does not get any record in the output.
- OR operator: If either condition satisfies the OR clause, the query returns the result.
- The query returns records that satisfy that meet at least one of the following conditions.
- Color= blue
- Name= Road-150 Red, 44
1 2 3 4 |
SELECT * FROM SalesLT.Product WHERE color= 'Blue' OR [Name]='Road-150 Red, 44' |
- Not operator: The Not operator reverses the result of a condition. For example, the below query returns the query having Product ID 707 or 708.
We can use NOT logical operator to exclude the product id 707 or 708 from the output.
Use SQL where statement with dates
The SQL query can filter the records using the date column. It is like the character data types and comparison operators’ filter records.
1 2 3 4 |
SELECT ProductID, Name, SellStartDate FROM SalesLT.Product WHERE SellStartDate>='2005-06-30' |
Functions in the where statement
You can combine functions such as YEAR(), Month() in the where clause to filter records on the specific condition. For example, the below query uses the year() function and finds records whose sell start date is 2005.
1 2 3 4 |
SELECT ProductID, Name, SellStartDate FROM SalesLT.Product WHERE year(SellStartDate)='2005' |
Update and delete with WHERE statement
The update statement also uses where clause for updating the specific rows. For example, if you want to update the cost for a specific product, you can specify the product id in the where clause and update the records.
Note: The where clause is if you do not specify the where condition in the update statement, the SQL query will update all records in the table.
1 2 3 4 5 |
UPDATE SalesLT.Product SET standardcost=100 WHERE Productid=710 |
Similarly, we can specify where clause in the delete statement removes the specific records for a table.
1 2 3 |
DELETE from SalesLT.Product WHERE Productid=710 |
Where and Having clauses
Sometimes, you work with aggregated data such as average value, minimum and maximum values. You cannot filter records from the aggregated data using the where clause. Therefore, in specific cases, such as GROUP BY, MAX(), MIN(), AVG() function, we can use the HAVING clause to filter the records.
For example, the below T-SQL script groups the rows based on the product id and calculates the average price using the AVG() function for the product id greater than 976. It uses the having clause for filtering the records from the aggregated data.
1 2 3 4 5 |
SELECT avg(standardcost) as AvgPrice,ProductID FROM SalesLT.Product GROUP BY ProductID HAVING ProductID>976 |
Similarly, the following T-SQL script calculates sums of the standard cost for product id between 976 and 980 in the product table.
1 2 3 4 5 |
SELECT SUM(standardcost) as SUMPrice,ProductID FROM SalesLT.Product GROUP BY ProductID HAVING ProductID between 976 AND 980 |
Helpful considerations for the WHERE statement
- You should avoid selecting all records from a large table to avoid performance issues. It is good to use SQL Where clause to restrict the number of rows in the output.
- You can use where clause for the numeric, character data type, logical, comparison operators.
- You should always use a delete statement with a where clause. In case you want to remove all records from a table, prefer using the TRUNCATE TABLE statement.
- You can combine multiple conditions in the where clause and combine them with the logical operators.
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023