How to Use SIMILAR and NULL Predicates in SQL Statements - dummies

How to Use SIMILAR and NULL Predicates in SQL Statements

By Allen G. Taylor

SQL:1999 added the SIMILAR predicate, which offers a more powerful way of finding partial matches than the LIKE predicate provides. With the SIMILAR predicate, you can compare a character string to a regular expression. For example, say you’re searching the OperatingSystem column of a software compatibility table to look for Microsoft Windows compatibility. You could construct a WHERE clause such as the following:

WHERE OperatingSystem SIMILAR TO
'('Windows '(3.1|95|98|ME|CE|NT|2000|XP|Vista|7|8))'

This predicate retrieves all rows that contain any of the specified Microsoft operating systems.

The NULL predicate finds all rows where the value in the selected column is null. For example, in a FOODS table comparing the nutritional value of different foods, several rows have null values in the Carbohydrate column. You can retrieve their names by using a statement such as the following:

 WHERE Carbohydrate IS NULL ;

This query returns the following values:

Beef, lean hamburger
Chicken, light meat
Opossum, roasted
Pork, ham

As you might expect, including the NOT keyword reverses the result, as in the following example:

 WHERE Carbohydrate IS NOT NULL ;

This query returns all the rows in the table except the four that the preceding query returns.

The statement Carbohydrate IS NULL is not the same as Carbohydrate = NULL. To illustrate this point, assume that, in the current row of the FOODS table, both Carbohydrate and Protein are null. From this fact, you can draw the following conclusions:

  • Carbohydrate IS NULL is True.

  • Protein IS NULL is True.

  • Carbohydrate IS NULL AND Protein IS NULL is True.

  • Carbohydrate = Protein is unknown.

  • Carbohydrate = NULL is an illegal expression.

Using the keyword NULL in a comparison is meaningless because the answer always returns as unknown.

Why is Carbohydrate = Protein defined as unknown even though Carbohydrate and Protein have the same (null) value? Because NULL simply means “I don’t know.” You don’t know what Carbohydrate is, and you don’t know what Protein is; therefore you don’t know whether those (unknown) values are the same.

Maybe Carbohydrate is 37, and Protein is 14, or maybe Carbohydrate is 93, and Protein is 93. If you don’t know both the carbohydrate value and the protein value, you can’t say whether the two are the same.