Advertisement
Online Test Banks
Score higher
See Online Test Banks
eLearning
Learning anything is easy
Browse Online Courses
Mobile Apps
Learning on the go
Explore Mobile Apps
Dummies Store
Shop for books and more
Start Shopping

How to Use SIMILAR and NULL Predicates in SQL Statements

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:

SELECT (Food)
 FROM FOODS
 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:

SELECT (Food)
 FROM FOODS
 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.

  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
Advertisement
Advertisement

Inside Dummies.com

Dummies.com Sweepstakes

Win an iPad Mini. Enter to win now!