{"id":182,"date":"2018-04-13T13:50:46","date_gmt":"2018-04-13T06:50:46","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=182"},"modified":"2024-09-24T21:15:47","modified_gmt":"2024-09-24T14:15:47","slug":"sql-server-where","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-where\/","title":{"rendered":"SQL Server WHERE Clause"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server WHERE<\/code> clause to filter rows returned by a query.<\/p>\n\n\n\n

Introduction to SQL Server WHERE clause #<\/a><\/h2>\n\n\n\n

The SELECT<\/a><\/code> statement retrieves all rows from a table. However, this is often unnecessary because the application may only need to process a subset of rows at the time.<\/p>\n\n\n\n

To retrieve rows that satisfy one or more conditions, you use the WHERE<\/code> clause in the SELECT<\/code> statement.<\/p>\n\n\n\n

Here’s the syntax of the WHERE<\/code> clause:<\/p>\n\n\n

SELECT<\/span>\n    select_list\nFROM<\/span>\n    table_name\nWHERE<\/span>\n    search_condition;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the search_condition<\/code> is a logical expression or a combination of multiple logical expressions. In SQL, a logical expression is also known as a predicate<\/em>.<\/p>\n\n\n\n
In the WHERE<\/code> clause, you specify a search condition to filter rows returned by the FROM<\/code> clause. The WHERE<\/code> clause only returns the rows for which the search_condition<\/code> evaluates to TRUE<\/code>.<\/p>\n\n\n\n
Note that SQL Server uses three-valued predicate logic where a logical expression can evaluate to TRUE<\/code>, FALSE<\/code>, or UNKNOWN<\/code>. The WHERE<\/code> clause will not return any row that causes the predicate to evaluate to FALSE<\/code> or UNKNOWN<\/code>.<\/p>\n\n\n\n
SQL Server WHERE examples #<\/a><\/h2>\n\n\n\n
We will use the production.products<\/code> table from the sample database<\/a> for the demonstration.<\/p>\n\n\n\n
\"Products<\/figure>\n\n\n\n
1) Using the WHERE clause with a simple equality operator #<\/a><\/h3>\n\n\n\n
The following query uses a WHERE<\/code> clause to retrieve products with the category ID 1:<\/p>\n\n\n
SELECT<\/span>\n    product_id,\n    product_name,\n    category_id,\n    model_year,\n    list_price\nFROM<\/span>\n    production.products\nWHERE<\/span>\n    category_id = 1<\/span>\nORDER<\/span> BY<\/span>\n    list_price DESC<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
2) Using the WHERE clause with the AND operator #<\/a><\/h3>\n\n\n\n
The following example uses a WHERE<\/code> clause to find products that belong to category id 1 and the model 2018:<\/p>\n\n\n
SELECT<\/span>\n    product_id,\n    product_name,\n    category_id,\n    model_year,\n    list_price\nFROM<\/span>\n    production.products\nWHERE<\/span>\n    category_id = 1<\/span> AND<\/span> model_year = 2018<\/span>\nORDER<\/span> BY<\/span>\n    list_price DESC<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
In this example, the condition in the WHERE<\/code> clause uses the logical operator AND<\/a><\/code> to combine the two conditions.<\/p>\n\n\n\n
3) Using WHERE to filter rows using a comparison operator #<\/a><\/h3>\n\n\n\n
The following statement finds the products with a list price greater than 300 and the model of 2018.<\/p>\n\n\n
SELECT<\/span>\n    product_id,\n    product_name,\n    category_id,\n    model_year,\n    list_price\nFROM<\/span>\n    production.products\nWHERE<\/span>\n    list_price > 300<\/span> AND<\/span> model_year = 2018<\/span>\nORDER<\/span> BY<\/span>\n    list_price DESC<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
4) Using the WHERE clause to filter rows that meet any of two conditions #<\/a><\/h3>\n\n\n\n
The following query uses a WHERE<\/code> clause to find products that meet either condition: a list price greater than 3,000 or the model of 2018:<\/p>\n\n\n
SELECT<\/span>\n    product_id,\n    product_name,\n    category_id,\n    model_year,\n    list_price\nFROM<\/span>\n    production.products\nWHERE<\/span>\n    list_price > 3000<\/span> OR<\/span> model_year = 2018<\/span>\nORDER<\/span> BY<\/span>\n    list_price DESC<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
Note that the WHERE<\/code> clause uses the OR<\/a><\/code> operator to combine conditions.<\/p>\n\n\n\n
5) Using the WHERE clause to filter rows with the value between two values #<\/a><\/h3>\n\n\n\n
The following statement finds the products with list prices between 1,899 and 1,999.99:<\/p>\n\n\n
SELECT<\/span>\n    product_id,\n    product_name,\n    category_id,\n    model_year,\n    list_price\nFROM<\/span>\n    production.products\nWHERE<\/span>\n    list_price BETWEEN<\/span> 1899.00<\/span> AND<\/span> 1999.99<\/span>\nORDER<\/span> BY<\/span>\n    list_price DESC<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n\n
\"SQL<\/figure> #<\/a><\/h3>\n\n\n\n
6) Using the WHERE clause to filter rows that have a value in a list of values #<\/a><\/h3>\n\n\n\n
The following example uses the IN<\/a><\/code> operator to find products with a list price of 299.99, 466.99, or 489.99.<\/p>\n\n\n
SELECT<\/span>\n    product_id,\n    product_name,\n    category_id,\n    model_year,\n    list_price\nFROM<\/span>\n    production.products\nWHERE<\/span>\n    list_price IN<\/span> (299.99<\/span>, 369.99<\/span>, 489.99<\/span>)\nORDER<\/span> BY<\/span>\n    list_price DESC<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n\n
\"SQL<\/figure> #<\/a><\/h3>\n\n\n\n
7) Finding rows whose values contain a string #<\/a><\/h3>\n\n\n\n
The following example uses the LIKE<\/a><\/code> operator to find products whose name contains the string Cruiser<\/code>:<\/p>\n\n\n
SELECT<\/span>\n    product_id,\n    product_name,\n    category_id,\n    model_year,\n    list_price\nFROM<\/span>\n    production.products\nWHERE<\/span>\n    product_name LIKE<\/span> '%Cruiser%'<\/span>\nORDER<\/span> BY<\/span>\n    list_price;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Use the SQL Server WHERE<\/code> clause to filter rows based on one or more conditions.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t