What is the purpose of the WHERE clause in a SQL query? — SQL Server Interview Question #9

The WHERE clause in a SQL query is used to filter data based on specific criteria. It follows the SELECT statement and is followed by the FROM statement, and its purpose is to specify which rows of data to retrieve from the specified table(s).

The syntax of a basic SQL query with a WHERE clause is as follows:

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

The condition specified in the WHERE clause can be any logical expression that evaluates to true or false. This condition can reference one or more columns in the table, as well as use various comparison and logical operators.

For example, the following query retrieves all rows from a table named "employees" where the "salary" column is greater than 50000:

SELECT *
FROM employees
WHERE salary > 50000;

The WHERE clause can also contain multiple conditions, which can be combined using logical operators such as AND and OR. For example, the following query retrieves all rows from the "employees" table where the "salary" is greater than 50000 AND the "department" is either ’Sales’ or ’Marketing’:

SELECT *
FROM employees
WHERE salary > 50000 AND department IN ('Sales', 'Marketing');

In addition to basic comparison and logical operators, the WHERE clause can also use a variety of functions and built-in operators to filter data based on specific criteria. For example, the following query retrieves all rows from a table named "orders" where the "order_date" is within the last 7 days:

SELECT *
FROM orders
WHERE order_date >= DATEADD(day, -7, GETDATE());

Overall, the WHERE clause is an essential part of SQL queries as it allows analysts and developers to select only the data they need from large tables, and to filter the data based on specific criteria that meet their business needs.