Rajendra Gupta
View execution plan properties

Exploring the SQL WHERE statement

June 9, 2022 by

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.

Retrieve records from a table

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.

Add a Where clause

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

View Execution plan

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.

View execution plan properties
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.

Note: You cannot use numeric comparison for a column with other data types such as varchar, nvarchar.

WHERE statement with Numeric comparison

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.

statement with Characters comparison

If we specify strings in the upper case or lower, SQL Server returns similar results.

specify strings in the upper case or lower

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.

Similarly, the below query returns all products having a Product ID greater than or equal to 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

Logical operators

If either condition does not return true, the query does not get any record in the output.

Invalid conditions

  • 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

OR operator

  • 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.

Check the results

We can use NOT logical operator to exclude the product id 707 or 708 from the output.

NOT logical operator

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.

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.

SQL where statement with dates

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.

Similarly, we can specify where clause in the delete statement removes the specific records for a table.

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.

Where and Having clause

Similarly, the following T-SQL script calculates sums of the standard cost for product id between 976 and 980 in the product table.

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.

Rajendra Gupta
384 Views