You can create queries that include any number of search conditions, linked with any number of AND and OR operators. A query with a combination of AND and OR clauses can become complex, so it is helpful to understand how such a query is interpreted when you execute it, and how such a query is represented in the Grid and SQL panes.
Note For details about search conditions that contain only one AND or OR operator, see Specifying Multiple Search Conditions for One Column and Specifying Multiple Search Conditions for Multiple Columns.
Below you will find information about:
To help you understand the discussion below, imagine that you are working with an employee table containing the columns hire_date, job_lvl, and status. The examples assume that you need to know information such as how long an employee has worked with the company (that is, what the employee’s hire date is), what type of job the employee performs (what the job level is), and the employee’s status (for example, retired).
When a query is executed, it evaluates first the clauses linked with AND, and then those linked with OR.
Note The NOT operator takes precedence over both AND and OR.
For example, to find either employees who have been with the company for more than five years in lower-level jobs or employees with middle-level jobs without regard for their hire date, you can construct a WHERE clause such as the following:
WHERE
hire_date < '01/01/90' AND
job_lvl = 100 OR
job_lvl = 200
To override the default precedence of AND over OR, you can put parentheses around specific conditions in the SQL pane. Conditions in parentheses are always evaluated first. For example, to find all employees who have been with the company more than five years in either lower or middle-level jobs, you can construct a WHERE clause such as the following:
WHERE
hire_date < '01/01/90' AND
(job_lvl = 100 OR job_lvl = 200)
Tip It is recommended that, for clarity, you always include parentheses when combining AND and OR clauses instead of relying on the default precedence.
Understanding how AND and OR clauses are related when combined can help you construct and understand complex queries in the Query Designer.
If you link multiple conditions using AND, the first set of conditions linked with AND applies to all the conditions in the second set. In other words, a condition linked with AND to another condition is distributed to all the conditions in the second set. For example, the following schematic representation shows an AND condition linked to a set of OR conditions:
A AND (B OR C)
The representation above is logically equivalent to the following schematic representation, which shows how the AND condition is distributed to the second set of conditions:
(A AND B) OR (A AND C)
This distributive principle affects how you use the Query Designer. For example, imagine that you are looking for all employees who have been with the company more than five years in either lower or middle-level jobs. You enter the following WHERE clause into the statement in the SQL pane:
WHERE (hire_date < '01/01/90' ) AND
(job_lvl = 100 OR job_lvl = 200)
The clause linked with AND applies to both clauses linked with OR. An explicit way to express this is to repeat the AND condition once for each condition in the OR clause. The following statement is more explicit (and longer) than the previous statement, but is logically equivalent to it:
WHERE (hire_date < '01/01/90' ) AND
(job_lvl = 100) OR
(hire_date < '01/01/90' ) AND
(job_lvl = 200)
The principle of distributing AND clauses to linked OR clauses applies no matter how many individual conditions are involved. For example, imagine that you want to find lower or middle-level employees who have been with the company more than five years or are retired. The WHERE clause might look like this:
WHERE
(job_lvl = 200 OR job_lvl = 300) AND
(hire_date < '01/01/90' ) OR (status = 'R')
After the conditions linked with AND have been distributed, the WHERE clause will look like this:
WHERE
(job_lvl = 200 AND hire_date < '01/01/90' ) OR
(job_lvl = 200 AND status = 'R') OR
(job_lvl = 300 AND hire_date < '01/01/90' ) OR
(job_lvl = 300 AND status = 'R')
The Query Designer represents your search conditions in the Grid pane. However, in some cases that involve multiple clauses linked with AND and OR, the representation in the Grid pane might not be what you expect. In addition, if you modify your query in the Grid or Diagram panes, you might find that your SQL statement has been changed from what you entered.
In general, these rules dictate how AND and OR clauses appear in the Grid pane:
Note The exact format of date information that you enter will depend on the database you are using. For details about entering dates, see Entering Search Values.
For example, in the SQL pane you might create a search condition such as the following, in which two clauses linked with AND take precedence over a third one linked with OR:
WHERE (hire_date < '01/01/90' ) AND
(job_lvl = 100) OR
(status = 'R')
The Query Designer represents this WHERE clause in the Grid pane as follows:
However, if the linked OR clauses take precedence over an AND clause, the AND clause is repeated for each OR clause. This causes the AND clause to be distributed to each OR clause. For example, in the SQL pane you might create a WHERE clause such as the following:
WHERE (hire_date < '01/01/90' ) AND
( (job_lvl = 100) OR
(status = 'R') )
The Query Designer represents this WHERE clause in the Grid pane as follows:
If the linked OR clauses involve only one data column, the Query Designer can place the entire OR clause into a single cell of the grid, avoiding the need to repeat the AND clause. For example, in the SQL pane you might create a WHERE clause such as the following:
WHERE (hire_date < '01/01/90' ) AND
((status = 'R') OR (status = 'A'))
The Query Designer represents this WHERE clause in the Grid pane as follows:
If you make a change to the query (such as changing one of the values in the Grid pane), the Query Designer recreates the SQL statement in the SQL pane. The recreated SQL statement will resemble the Grid pane display rather than your original statement. For example, if the Grid pane contains distributed AND clauses, the resulting statement in the SQL pane will be recreated with explicit distributed AND clauses. For details, see "How AND Works with Multiple OR Clauses" earlier in this topic.