Boolean operators AND, OR & NOT – WHERE Clause

WHERE clause conditions can either be simple or contain multiple conditions. Multiple conditions can be built using the Boolean operators AND, OR, and NOT. The behavior of these operators was described in section “NULL Values” using truth tables.

If two conditions are connected by the AND operator, rows are retrieved for which both conditions are true. If two conditions are connected by the OR operator, all rows of a table are retrieved in which either the first or the second condition (or both) is true, as shown in Example 6.5.

Get the employee numbers for all employees who work for either project p1 or project p2 (or both):

USE sample;
SELECT project_no, emp_no
	FROM works_on
	WHERE project_no = 'p1'
	OR project_no = 'p2';Code language: PHP (php)

The result is

The result of Example 6.5 contains some duplicate values of the emp_no column. To eliminate this redundant information, use the DISTINCT option, as shown here:

USE sample;
SELECT DISTINCT emp_no
	FROM works_on
	WHERE project_no = 'p1'
	OR project_no = 'p2';Code language: PHP (php)

In this case, the result is

Note that the DISTINCT option can be used only once in a SELECT list, and it must precede all column names in that list. Therefore, Example 6.6 is wrong.

USE sample;
SELECT emp_fname, DISTINCT emp_no
	FROM employee
	WHERE emp_lname = 'Moser';Code language: PHP (php)

The result is

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'DISTINCT'.Code language: JavaScript (javascript)

The WHERE clause may include any number of the same or different Boolean operations. You should be aware that the three Boolean operations have different priorities for evaluation: the NOT operation has the highest priority, AND is evaluated next, and the OR operation has the lowest priority. If you do not pay attention to these different priorities for Boolean operations, you will get unexpected results, as Example 6.7 shows.

USE sample;
SELECT emp_no, emp_fname, emp_lname
	FROM employee
	WHERE emp_no = 25348 AND emp_lname = 'Smith'
	OR emp_fname = 'Matthew' AND dept_no = 'd1';
SELECT emp_no, emp_fname, emp_lname
	FROM employee
	WHERE ((emp_no = 25348 AND emp_lname = 'Smith')
	OR emp_fname ='Matthew') AND dept_no = 'd1';Code language: PHP (php)

The result is

As the results of Example 6.7 show, the two SELECT statements display two different result sets. In the first SELECT statement, the system evaluates both AND operators first (from the left to the right), and then evaluates the OR operator. In the second SELECT statement, the use of parentheses changes the operation execution, with all expressions within parentheses being executed first, in sequence from left to right. As you can see, the first statement returned one row, while the second statement returned zero rows.

The existence of several Boolean operations in a WHERE clause complicates the corresponding SELECT statement and makes it error prone. In such cases, the use of parentheses is highly recommended, even if they are not necessary. The readability of such SELECT statements will be greatly improved, and possible errors can be avoided. Here is the first SELECT statement from Example 6.7, modified using the recommended form:

USE sample;
SELECT emp_no, emp_fname, emp_lname
	FROM employee
	WHERE (emp_no = 25348 AND emp_lname = 'Smith')
	OR (emp_fname = 'Matthew' AND dept_no = 'd1');Code language: PHP (php)

The third Boolean operator, NOT, changes the logical value of the corresponding condition. The truth table for NOT in section “NULL Values” shows that the negation of the TRUE value is FALSE and vice versa; the negation of the NULL value is also NULL. Example 6.8 shows the use of the NOT operator.

Get the employee numbers and first names of all employees who do not belong to the department d2:

USE sample
SELECT emp_no, emp_lname
	FROM employee
	WHERE NOT dept_no = 'd2';Code language: PHP (php)

The result is

In this case, the NOT operator can be replaced by the comparison operator <> (not equal).