How to use SQL WHERE

SQL WHERE clauses enable you to narrow down your search with clear search criteria. They are among the most important tools for running efficient searches in SQL.

What is SQL WHERE?

SQL offers various tools to efficiently filter your searches, whether you’re performing a query, analysis or a complex calculation in a large dataset. One of the most important filter tools is the SQL WHERE clause. WHERE clauses can be used to define precise criteria and conditions that narrow down data for queries, merges, edits and updates. They reduce the number of records you’re working with, optimise the efficiency of your analyses and save time and money.

SQL WHERE clauses can be combined with most SQL commands, operators and functions. They have a wide variety of uses in database management systems and optimise your work in databases, reducing errors and leading to more accurate results tables.

What is the syntax of SQL WHERE?

The basic syntax of SQL WHERE look as follows:

SELECT  ColumnA, ColumnB, … or *
FROM  Table1
WHERE  Condition_Name =  'Condition'

The following parameters are used:

• SELECT: SELECT specifies the columns that you want to apply the WHERE clause to. Using an asterisk * indicates that you want to select all record in the table.
• FROM: Specifies the table that contains the records in question.
• WHERE: Defines the object of the condition (i.e. a selected column) and the condition that should be satisfied. Operators like =, <, > and != are often used here.

What is SQL WHERE used for?

Like SELECT and FROM, WHERE is one of the most important and most frequently used elements in SQL. It is indispensable for filtering records and can be combined with other statements, operators and functions in a wide variety of ways. Here are some of its most common uses:

• Filtering and narrowing down records based on user-defined criteria
• Sorting records into groups and categories
• Adding, comparing or ignoring values
• Identifying trends and patterns based on period or quantity analyses

Examples of SQL WHERE

We’ll now turn to two examples that show how SQL WHERE works.

Only retrieve customers from a certain region

In this example, we’ll retrieve all customers from the UK in a table called ‘Customers’ with a column called ‘Country’:

SELECT  *
FROM  Customers
WHERE  Country  =  'UK'

Only retrieve orders over a minimum amount

In this example, we’ll retrieve only orders over £50 from a table called ‘Orders’ with a column called ‘Price’:

SELECT  *
FROM  Orders
WHERE  Price  >  50;

What are the alternatives to SQL WHERE?

In addition to SQL WHERE, there are several other clauses, statements and keywords that can be used to filter based on certain criteria:

• HAVING: HAVING clauses are used to filter results with aggregate functions. Whereas WHERE is used to filter data before aggregating, HAVING can filter data after aggregation.
• CASE: SQL CASE is used to filter and compare records using the ‘if-then-else’ principle. You can use it with or without a WHERE clause. Since CASE statements can contain multiple, nested criteria, they provide even more options for filtering than WHERE. WHERE, on the other hand, enables simpler and clearer searches.