In the previous article, Using the WHERE clause with the SQL SELECT Statement, we demonstrated how you would use the SQL WHERE clause to conditionally select data from the database table. The WHERE clause can be simple and use only a single condition (like the one presented in the previous article), or it can be used to include multiple search conditions.
SQL allows us to combine two or more simple conditions by using the AND and OR or NOT operators. Any number of simple conditions can be present in a single SQL statement, to allow us to create complex WHERE clauses that allow us to control which rows are included in our query results.
We will use the following syntax to execute an SQL SELECT query with multiple conditions in the WHERE clause:
SELECT column_list FROM table_name WHERE column_name condition {[AND|OR} column_name condition}
The order of precedence for the logical operators is NOT (highest), followed by AND, followed by OR. The order of evaluation at the same precedence level is from left to right. Parentheses can be used to override this order in a search condition.
If we include multiple operators in the query, SQL Server evaluates them in the following order:
Now, lets see a couple of examples of using multiple conditions for the WHERE clause in the SQL SELECT statement. We will use the following Employee data table:
EmpName | EmpAddress | EmpTown | EmpPostcode | EmpAge |
---|---|---|---|---|
Elaine Jones | 1 Old Road | Manchester | M27 1SN | 28 |
David Thomas | 245 Lane End | Lincoln | LN34 2TH | 41 |
Simon Lee | 9 Swallow Lane | Wigan | WN3 0NR | 58 |
Leslie Ward | 3 North End Road | Nottingham | NG8 2LJ | 30 |
Harry Webb | 1002 Trinity Road Sth | Swindon | SN2 1JH | 22 |
Ronny Scott | 1 The Lane | Nottingham | NG25 9LK | 55 |
Janet Fuller | 14 Garrett Hill | London | SW1 | 40 |
Sally Timmings | Edgeham Hollow | London | WE3 | 24 |
Simon Lee | 9 Swallow Lane | Wigan | WN3 0NR | 19 |
Using the AND operator, the query will display a row if ALL conditions listed are true. If we want to search for Employees who live in London AND are aged greater than 30, we would use the following SQL statement:
SELECT * FROM tblEmployee WHERE EmpTown = 'London' AND EmpAge > 30
This statement will return only one result from our table that meets both of the conditions specified:
EmpName | EmpAddress | EmpTown | EmpPostcode | EmpAge |
---|---|---|---|---|
Janet Fuller | 14 Garrett Hill | London | SW1 | 40 |
Using the OR operator, the query will display a row if ANY of the conditions listed are true. If we want to search for Employees who live in London OR Employees who live in Swindon, we would use the following SQL statement:
SELECT * FROM tblEmployee WHERE EmpTown = 'London' OR EmpTown = 'Swindon'
This statement will return three results from our table. It will return those Employees who live in London OR live in Swindon:
EmpName | EmpAddress | EmpTown | EmpPostcode | EmpAge |
---|---|---|---|---|
Harry Webb | 1002 Trinity Road Sth | Swindon | SN2 1JH | 22 |
Janet Fuller | 14 Garrett Hill | London | SW1 | 40 |
Sally Timmings | Edgeham Hollow | London | WE3 | 24 |
We can also combine the AND and OR conditions, to create even more complex SQL statements (you may need to use parentheses to form complex expressions). Consider that we want to find all Employees who live in London AND are aged greater than 30 OR Employees who live in Swindon regardless of their age. The syntax for this multiple condition SQL statement would like like the following:
SELECT * FROM tblEmployee WHERE (EmpTown = 'London' AND EmpAge > 30) OR EmpTown = 'Swindon'
This time, we see that the statement will return two records; the record for Janet Fuller (who lives in London AND whose age is greater than 30), and also the record for Harry Web who lives in Swindon.
EmpName | EmpAddress | EmpTown | EmpPostcode | EmpAge |
---|---|---|---|---|
Harry Webb | 1002 Trinity Road Sth | Swindon | SN2 1JH | 22 |
Janet Fuller | 14 Garrett Hill | London | SW1 | 40 |
SQL server evaluates conditions that use the logical AND before it evaluates those that use the logical OR. If you want to be sure of how SQL Server will process your database queries containing multiple operators, you should always use parentheses.