Where Clause in SQL Server

The WHERE clause in SQL Server is used to filter the data returned by a query based on one or more conditions. You can use the WHERE clause with the SELECT, UPDATE, DELETE, and other statements. Here are some basic points about the WHERE clause:

  • The WHERE clause is used to filter records.
  • It is used to extract only those records that fulfill a specified condition.

Syntax: -

SELECT column1, column2, ...FROM table_name
WHERE condition;

WHERE Clause Examples,

  • The following SQL statement selects all the customers from the country \”Mexico\”, in the \”Customers\” table:

SELECT * FROM Customers WHERE Country=\’Mexico\’;

  • SQL requires single quotes around text values (most database systems will also allow double quotes). However, numeric fields should not be enclosed in quotes:

SELECT * FROM Customers WHERE CustomerID=1;

Operators in The WHERE Clause

The following operators can be used in the WHERE clause:

OperatorDescription
=Equal
Greater than
Less than
>=Greater than or equal
<=Less than or equal
<> Not equal. Note: In some versions of SQL this operator may be written as !=
BETWEENBetween a certain range
LIKESearch for a pattern
INTo specify multiple possible values for a column

The WHERE clause in SQL Server is used to filter the data returned by a query based on one or more conditions. You can use the WHERE clause with the SELECT, UPDATE, DELETE, and other statements. Here are some basic points about the WHERE clause:

  • The syntax of the WHERE clause is: SELECT column1, column2, … FROM table_name WHERE condition;
  • The condition can be a logical expression that evaluates to true, false, or unknown. You can use comparison operators, logical operators, and functions to construct the condition.
  • The WHERE clause can be combined with other clauses such as GROUP BY, HAVING, and ORDER BY to further refine the query results.
  • The WHERE clause can also use subqueries, which are queries nested within another query. Subqueries can return a single value, a list of values, or a table of values that can be compared with the outer query.
  • The WHERE clause can also use some special operators such as IN, BETWEEN, LIKE, and EXISTS to test for multiple values, ranges, patterns, and existence of rows.

Some examples of using the WHERE clause in SQL Server are:

  • To select all customers from Mexico: SELECT * FROM Customers WHERE Country = \’Mexico\’;
  • To select all products with a price between 10 and 20: SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;
  • To select all customers whose name starts with ‘A’: SELECT * FROM Customers WHERE CustomerName LIKE \’A%\’;
  • To select all orders that have at least one order detail: SELECT * FROM Orders WHERE EXISTS (SELECT * FROM OrderDetails WHERE OrderDetails.OrderID = Orders.OrderID);