databasedev.co.uk - database solutions and downloads for microsoft access

SQL AND/OR Conditions

SQL - Multiple Conditions; using the AND and OR conditions:

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:

  1. Parentheses - if you group conditional SQL statements together by parentheses, SQL Server evaluates the contents of these first.
  2. Arithmetic - multiplication (using the operators *, /, or %)
  3. Arithmetic - addition (using the operators + or -)
  4. Other - String Concatenator (+)
  5. Logical - NOT
  6. Logical - AND
  7. Logical - OR

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 SQL AND condition:

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 SQL OR condition:

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

Combining the SQL AND condition and OR condition:

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.