NULL Values, LIKE Operator in WHERE Clause

A NULL in the CREATE TABLE statement specifies that a special value called NULL (which usually represents unknown or not applicable values) is allowed in the column. These values differ from all other values in a database. The WHERE clause of a SELECT statement generally returns rows for which the comparison evaluates to TRUE. The concern, then, regarding queries is, how will comparisons involving NULL values be evaluated in the WHERE clause?

All comparisons with NULL values will return FALSE (even when preceded by NOT). To retrieve the rows with NULL values in the column, Transact-SQL includes the operator feature IS NULL. This specification in a WHERE clause of a SELECT statement has the following general form:

column IS [NOT] NULLCode language: CSS (css)

Example 6.14 shows the use of the IS NULL operator.

Get employee numbers and corresponding project numbers for employees with unknown jobs who work on project p2:

USE sample;
SELECT emp_no, project_no
	FROM works_on
	WHERE project_no = 'p2'
	AND job IS NULL;Code language: PHP (php)

The result is

Because all comparisons with NULL values return FALSE, Example 6.15 shows syntactically correct, but logically incorrect, usage of NULL.

USE sample;
SELECT project_no, job
	FROM works_on
	WHERE job <> NULL;Code language: PHP (php)

The result is

The condition “column IS NOT NULL” is equivalent to the condition “NOT (column IS NULL).”

The system function ISNULL allows a display of the specified value as substitution for NULL (see Example 6.16).

USE sample;
SELECT emp_no, ISNULL(job, 'Job unknown') AS task
	FROM works_on
	WHERE project_no = 'p1';Code language: PHP (php)

The result is

Example 6.16 uses a column heading called task for the job column.

LIKE Operator

LIKE is an operator that is used for pattern matching; that is, it compares column values with a specified pattern. The data type of the column can be any character or date. The general form of the LIKE operator is

column [NOT] LIKE 'pattern'Code language: JavaScript (javascript)

pattern may be a string or date constant or expression (including columns of tables) and must be compatible with the data type of the corresponding column. For the specified column, the comparison between the value in a row and the pattern evaluates to TRUE if the column value matches the pattern expression.

Certain characters within the pattern—called wildcard characters—have a specific interpretation. Two of them are

• % (percent sign) Specifies any sequence of zero or more characters
• _ (underscore) Specifies any single character

Example 6.17 shows the use of the wildcard characters % and _.

Get the first and last names and numbers of all employees whose first name contains the letter a as the second character:

USE sample;
SELECT emp_fname, emp_lname, emp_no
	FROM employee
	WHERE emp_fname LIKE '_a%';Code language: PHP (php)

The result is

In addition to the percent sign and the underscore, Transact-SQL supports other
characters that have a special meaning when used with the LIKE operator. These
characters ([, ], and ^) are demonstrated in Examples 6.18 and 6.19.

Get full details of all departments whose location begins with a character in the range C through F:

USE sample;
SELECT dept_nt, dept_name, location
	FROM department
	WHERE location LIKE '[C-F]%';Code language: PHP (php)

The result is

As shown in Example 6.18, the square brackets, [ ], delimit a range or list of characters. The order in which characters appear in a range is defined by the collating sequence, which is determined during the system installation.

The character ^ specifies the negation of a range or a list of characters. This character has this meaning only within a pair of square brackets, as shown in Example 6.19.

Get the numbers and first and last names of all employees whose last name does not begin with the letter J, K, L, M, N, or O and whose first name does not begin with the letter E or Z:

USE sample;
SELECT emp_no, emp_fname, emp_lname
	FROM employee
	WHERE emp_lname LIKE '[^J-O]%'
	AND emp_fname LIKE '[^EZ]%';Code language: PHP (php)

The result is

The condition “column NOT LIKE ‘pattern’” is equivalent to the condition “NOT (column LIKE ‘pattern’).”

Example 6.20 shows the use of the LIKE operator (together with NOT).

Get full details of all employees whose first name does not end with the character n:

USE sample;
SELECT emp_no, emp_fname, emp_lname
	FROM employee
	WHERE emp_fname NOT LIKE '%n';Code language: PHP (php)

The result is

Any of the wildcard characters (%, _, [, ], or ^) enclosed in square brackets stands for itself. An equivalent feature is available through the ESCAPE option. Therefore, both SELECT statements in Example 6.21 have the same meaning.

USE sample;
SELECT project_no, project_name
	FROM project
	WHERE project_name LIKE '%[_]%';
SELECT project_no, project_name
		FROM project
		WHERE project_name LIKE '%!_%' ESCAPE '!';Code language: PHP (php)

The result is

Both SELECT statements search for the underscore as an actual character in the column project_name. In the first SELECT statement, this search is established by enclosing the sign _ in square brackets. The second SELECT statement uses a character (in Example 6.21, the character !) as an escape character. The escape character overrides the meaning of the underscore as the wildcard character and leaves it to be interpreted as an ordinary character. (The result contains no rows because there are no project names that include the underscore character.)