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 ef­fi­cient­ly filter your searches, whether you’re per­form­ing a query, analysis or a complex cal­cu­la­tion 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 con­di­tions that narrow down data for queries, merges, edits and updates. They reduce the number of records you’re working with, optimize the ef­fi­cien­cy 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 man­age­ment systems and optimize 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 pa­ra­me­ters 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 fre­quent­ly used elements in SQL. It is in­dis­pens­able for filtering records and can be combined with other state­ments, 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 cat­e­gories
• Adding, comparing or ignoring values
• Iden­ti­fy­ing 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 US in a table called “Customers” with a column called “Country”:

SELECT  *
FROM  Customers
WHERE  Country  =  'USA'

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 al­ter­na­tives to SQL WHERE?

In addition to SQL WHERE, there are several other clauses, state­ments 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 ag­gre­gat­ing, HAVING can filter data after ag­gre­ga­tion.
• 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 state­ments can contain multiple, nested criteria, they provide even more options for filtering than WHERE. WHERE, on the other hand, enables simpler and clearer searches.