Where clause is used to filter the records returned by the query.
- It works on all DML (Data Manipulation Language) statements.
- We can also use where for more than one column by taking the combination of AND/OR operators.
- We can not use Aggregated functions in where clause. But we can use other system functions as where conditions.
- OR Condition in where clause reduces the query performance.
- Where 1=1 condition gives all records.
- It filters the records before fetching the required columns mentioned in the SELECT statement.
SELECT <Column_Name(s)> FROM <Table_Name>
WHERE <Column_Name> <Operator> <value>;
WHERE [JobTitle] = ‘Database Administrator‘;
The above query returns the [BusinessEntityID] and [JobTitle] from the Employee table and displays the records only whose [JobTitle] is Database Administrator.
Where with Multiple conditions
WHERE ([OrganizationLevel] = 4 OR [MaritalStatus] = ‘M’)
AND [JobTitle] = ‘Design Engineer’;
This query returns [BusinessEntityID] and [JobTitle] from the Employee table and displays the records only whose [OrganizationLevel] = 4 OR [MaritalStatus] = ‘M’ and also [JobTitle] = ‘Design Engineer’. If the conditions having OR in between, it gives results if anyone of the conditions matches. But if there is AND condition between them, both should be matched to display the records.
- The value in Where condition must be enclosed by single quotes(‘ ‘) if the data type of the column is any of the character Date type. Single quotes are not required for the Numeric data types.
- Try to avoid OR condition as much as you can. Because it hits the query performance a lot negatively.
- I will explain about Operators and types of operators in my upcoming articles.