Home > Articles > Data > SQL

  • Print
  • + Share This
Like this article? We recommend

Selecting Rows: The WHERE Clause

The WHERE clause is the part of the SELECT statement that specifies the search conditions. These conditions determine exactly which rows are retrieved. The general format is this:

SYNTAX

SELECT select_list
FROM table_list
WHERE search_conditions
 

When you run a SELECT statement with a WHERE clause, your system searches for the rows in the table that meet your conditions (also called qualifications).

SQL provides a variety of operators and keywords for expressing the search conditions, including these:

  • Comparison operators

     (=, <, >, and so on)
    select title
    from titles 
    where advance * 2 > ytd_sales * price
  • Combinations or logical negations of conditions

    (AND, OR, NOT)
    select title
    from titles 
    where advance < 5000 or ytd_sales > 2000
  • Ranges (BETWEEN and NOT BETWEEN)

    select title
    from titles 
    where ytd_sales between 4095 and 12000
  • Lists (IN, NOT IN)

    select pub_name
    from publishers
    where state in ('CA', 'IN', 'MD')
  • Unknown values

    (IS NULL and IS NOT NULL)
    select title
    from titles 
    where advance is null
  • Character matches

    (LIKE and NOT LIKE)
    select au_lname
    from authors
    where phone not like '415%'

Each of these keywords and operators is explained and illustrated in this chapter. In addition, the WHERE clause can include join conditions (see Chapter 7) and subqueries (see Chapter 8).

Comparison Operators

You often want to look at values in relation to one another to find out which is "larger" or "smaller" or "lower" in the alphabet sort or "equal" to some other database value or to a constant. SQL provides a set of comparison operators for these purposes. In most dialects, the comparison operators are these:

Operator

Meaning

=

equal to

>

greater than

<

less than

>=

greater than or equal to

<=

less than or equal to

< >

not equal to


The operators are used in the syntax:

SYNTAX

WHERE expression comparison_operator expression
 

An expression can be a plain column name or something more complex—a character string, a function or calculation (usually involving a column name), or any combination of these elements connected by arithmetic operators. When evaluated, an expression produces a single value per row.

In contexts other than SQL, the comparison operators are usually used with numeric values. In SQL, they are also used with char and varchar data (< means earlier in the dictionary order and > means later) and with dates (< means earlier in chronological order and > means later). When you use character and date values in a SQL statement, be sure to put quotes around them.

TIP

The order in which uppercase and lowercase characters and special characters are evaluated depends on the character-sorting sequence you are using, imposed by your database system or by the machine you are using. (There are more details on sort order in "Character Sets and Sort Orders"). Check your system to see how it handles trailing blanks in comparisons. Is "Dirk" considered the same as "Dirk "?

Comparing Numbers

The following SELECT statements and their results should give you a good sense of how the comparison operators are used. The first query finds the books that cost more than $25.00.

SQL
select title, price
from titles
where price > $25.00
title                                                           price
=============================================================== =====
Secrets of Silicon Valley                                       40.00
The Busy Executive's Database Guide                             29.99
Prolonged Data Deprivation: Four Case Studies                   29.99
Silicon Valley Gastronomic Treats                               29.99
Sushi, Anyone?                                                  29.99
But Is It User Friendly?                                        42.95
Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean 40.95
Straight Talk About Computers                                   29.99
Computer Phobic and Non-Phobic Individuals: Behavior Variations 41.59
[9 rows]
 

SQL Variants

Check your system to see if it allows dollar signs with money values. Most do not. Transact-SQL is an exception, and so is Adaptive Server Anywhere.

Comparing Character Values

The next SELECT statement finds the authors whose last names follow McBadden in the alphabet. Notice the name is in single quotes. (Some systems allow both single and double quotes around character and date constants in the WHERE clause, but most allow single quotes only.)

SQL
select au_lname, au_fname
from authors
where au_lname >'McBadden'
au_lname                                 au_fname
======================================== ===============
O'Leary                                  Michael
Panteley                                 Sylvia
Ringer                                   Albert
Ringer                                   Anne
Smith                                    Meander
Straight                                 Dick
Stringer                                 Dirk
White                                    Johnson
Yokomoto                                 Akiko
[9 rows]
 

(Your results may differ, depending on the sort order your system uses. See Chapter 5 for more on this issue.)

Comparing Imaginary Values

The next query displays hypothetical information—it calculates double the price of all books for which advances over $10,000 were paid and displays the title identification numbers and calculated prices:

SQL
select title_id, price * 2
from titles
where advance > 10000
title_id titles.price*2
======== ==============
BU2075            25.98
MC3021            25.98
[2 rows]
 

Finding Values Not Equal to Some Value

Following is a query that finds the telephone numbers of authors who don't live in California, using the not equal comparison operator (in some SQL dialects, you can use != as the not equal operator).

SQL
select au_id, phone
from authors
where state <> 'CA'
au_id       phone
=========== ============
998-72-3567 801 826-0752
899-46-2035 801 826-0752
722-51-5454 219 547-9982
807-91-6654 301 946-8853
527-72-3246 615 297-2723
712-45-1867 615 996-8275
648-92-1872 503 745-6402
341-22-1782 913 843-0462
[8 rows]
 

Connecting Conditions with Logical Operators

Use the logical operators AND, OR, and NOT when you're dealing with more than one condition in a WHERE clause. The logical operators are also called Boolean operators.

AND

AND joins two or more conditions and returns results only when all of the conditions are true. For example, the following query will find only the rows in which the author's last name is Ringer and the author's first name is Anne. It will not find the row for Albert Ringer.

SQL
select au_id, au_lname, au_fname
from authors
where au_lname = 'Ringer'
  and au_fname = 'Anne'

au_id       au_lname                            au_fname
=========== =================================== ========
899-46-2035 Ringer                              Anne
[1 row]
 

The next example finds business books with a price higher than $20.00 and for which an advance of less than $20,000 was paid:

SQL
select title, type, price, advance
from titles
where type = 'business'
  and price > 20.00
  and advance < 20000
title                                     type     price  advance
=======================================   ======== ====== ========
The Busy Executive's Database Guide       business  29.99  5000.00
Cooking with Computers: Surreptitious 
  Balance Sheets                          business  21.95  5000.00
Straight Talk About Computers             business  29.99  5000.00
[3 rows]
 

OR

OR also connects two or more conditions, but it returns results when any of the conditions is true. The following query searches for rows containing Anne or Ann in the au_fname column:

SQL
select au_id, au_lname, au_fname
from authors
where au_fname = 'Anne'
  or au_fname = 'Ann'
au_id       au_lname                          au_fname
=========== ================================= ==========
899-46-2035 Ringer                            Anne
427-17-2319 Dull                              Ann
[2 rows]
 

The following query searches for books with a price higher than $20.00 or an advance less than $5,000:

SQL
select title, type, price, advance
from titles
where price > $30.00
  or advance < $5000
title                                      type         price advance
========================================== ============ ===== =======
Secrets of Silicon Valley                  popular_comp 40.00 8000.00
Emotional Security: A New Algorithm        psychology   17.99 4000.00
Prolonged Data Deprivation: Four Case    
   Studies                                 psychology   29.99 2000.00
Silicon Valley Gastronomic Treats          mod_cook     29.99    0.00
Fifty Years in Buckingham Palace Kitchens  trad_cook    21.95 4000.00
But Is It User Friendly?                   popular_comp 42.95 7000.00
Is Anger the Enemy?                        psychology   21.95 2275.00
Onions, Leeks, and Garlic: Cooking     
   Secrets of the Mediterranean            trad_cook    40.95 7000.00
Computer Phobic and Non-Phobic     
   Individuals: Behavior Variations        psychology   41.59 7000.00
[9 rows]
 

Semantic Issues with OR and AND

One more example using OR will demonstrate a potential for confusion. Let's say you want to find all the business books, as well as any books with a price higher than $10 and any books with an advance less than $20,000. The English phrasing of this problem suggests the use of the operator AND, but the logical meaning dictates the use of OR because you want to find all the books in all three categories, not just books that meet all three characteristics at once. Here's the SQL statement that finds what you're looking for:

SQL
select title, type, price, advance
from titles
where type = 'business'
  or price > $20.00
  or advance < $20000
title                                     type         price advance
========================================= ============ ===== =======
Secrets of Silicon Valley                 popular_comp 40.00  8000.00
The Busy Executive's Database Guide       business     29.99  5000.00
Emotional Security: A New Algorithm       psychology   17.99  4000.00
Prolonged Data Deprivation:    
  Four Case Studies                       psychology   29.99  2000.00
Cooking with Computers:    
  Surreptitious Balance Sheets            business     21.95  5000.00
Silicon Valley Gastronomic Treats         mod_cook     29.99     0.00
Sushi, Anyone?                            trad_cook    29.99  8000.00
Fifty Years in Buckingham Palace Kitchens trad_cook    21.95  4000.00
But Is It User Friendly?                  popular_comp 42.95  7000.00
You Can Combat Computer Stress!           business     12.99 10125.00
Is Anger the Enemy?                       psychology   21.95  2275.00
Life Without Fear                         psychology   17.00  6000.00
The Gourmet Microwave                     mod_cook     12.99 15000.00
Onions, Leeks, and Garlic:    
  Cooking Secrets of the Mediterranean    trad_cook    40.95  7000.00
Straight Talk About Computers             business     29.99  5000.00
Computer Phobic and Non-Phobic     
   Individuals: Behavior Variations       psychology   41.59  7000.00
[16 rows]
 

Compare this query and its results to the earlier example that is identical except for the use of AND instead of OR.

NOT

The logical operator NOT negates an expression. When you use it with comparison operators, put it before the expression rather than before the comparison operator. The following two queries are equivalent:

SQL
select au_lname, au_fname, state
from authors
where state <> 'CA'
SQL
select au_lname, au_fname, state
from authors
where not state = 'CA'

Here are the results:

Results
au_lname                           au_fname        state
================================== =============== =====
Ringer                             Albert          UT
Ringer                             Anne            UT
DeFrance                           Michel          IN
Panteley                           Sylvia          MD
Greene                             Morningstar     TN
del Castillo                       Innes           MI
Blotchet-Halls                     Reginald        OR
Smith                              Meander         KS
[8 rows]
 

Logical Operator Precedence

Like the arithmetic operators, logical operators are handled according to precedence rules. When both kinds of operators occur in the same statement, arithmetic operators are handled before logical operators. When more than one logical operator is used in a statement, NOT is evaluated first, then AND, and finally OR. Figure 4.5 shows the hierarchy.

Figure 4.5 Precedence Hierarchy for Logical Operators

Some examples will clarify the situation. The following query finds all the business books in the titles table, no matter what their advances are, as well as all psychology books that have an advance greater than $5,500. The advance condition pertains to psychology books and not to business books because the AND is handled before the OR.

SQL
select title_id, type, advance
from titles
where type = 'business'
  or type = 'psychology'
  and advance > 5500
title_id type              advance
======== ============ ============
BU1032   business          5000.00
BU1111   business          5000.00
BU2075   business         10125.00
PS2106   psychology        6000.00
BU7832   business          5000.00
PS1372   psychology        7000.00
[6 rows]
 

The results include three business books with advances less than $5,500 because the query was evaluated according to the following precedence rules:

  1. Find all psychology books with advances greater than $5,500.

  2. Find all business books (never mind about advances).

  3. Display both sets of rows in the results.

You can change the meaning of the previous query by adding parentheses to force evaluation of the OR first. With parentheses added, the query executes differently:

  1. Find all business and psychology books.

  2. Locate those that have advances over $5,500.

  3. Display only the final subset.

SQL
select title_id, type, advance
from titles
where (type = 'business' or type = 'psychology')
  and advance > 5500
title_id type              advance
======== ============ ============
BU2075   business         10125.00
PS2106   psychology        6000.00
PS1372   psychology        7000.00
[3 rows]
 

The parentheses cause SQL to find all business and psychology books and, from among those, to find those with advances greater than $5,500.

Here's a query that includes arithmetic operators, comparison operators, and logical operators. It searches for books that are not bringing in enough money to offset their advances. Specifically, the query searches for any books with gross revenues (that is, ytd_sales times price) less than twice the advance paid to the author(s). The user who constructed this query has tacked on another condition: She wants to include in the results only books published before October 15, 2000, because those books have had long enough to establish a sales pattern. The last condition is connected with the logical operator AND; according to the rules of precedence, it is evaluated after the arithmetic operations.

SQL
select title_id, type, price, advance, ytd_sales
from titles
where price * ytd_sales <  2 * advance
  and pubdate < '10/15/2000'
title_id type            price     advance   ytd_sales
======== =========== ========= =========== ===========
PS2106   psychology      17.00     6000.00         111
[1 row]
 

SQL VariantS

If you run this query on a system with a different date format, you may need to change the pubdate value to correspond to that format. For example, if your SQL engine expects dates to look like DD-MON-YYYY, you could write the query like this:

Oracle
SQL> select title_id, type, price, advance, ytd_sales
  2   from titles
  3   where price * ytd_sales <  2 * advance
  4  and pubdate < '21 OCT 2000';
TITLE_ TYPE             PRICE   ADVANCE YTD_SALES
------ ------------ --------- --------- ---------
PS2106 psychology          17      6000       111
 

Ranges (BETWEEN and NOT BETWEEN)

Another common search condition is a range. There are two different ways to specify ranges:

  • With the comparison operators > and <

  • With the keyword BETWEEN

Use BETWEEN to specify an inclusive range, in which you search for the lower value and the upper value as well as the values they bracket. For example, to find all the books with sales between (and including) 4,095 and 12,000, you could write this query:

SQL
select title_id, ytd_sales
from titles
where ytd_sales between 4095 and 12000
title_id   ytd_sales
======== ===========
PC8888          4095
BU1032          4095
TC7777          4095
PC1035          8780
BU7832          4095
[5 rows]
 

Notice that books with sales of 4,095 are included in the results. If there were any with sales of 12,000, they would be included too. In this way, the BETWEEN range is different from the greater-than/less-than (> <) range. The same query using the greater-than and less-than operators returns different results because the range is not inclusive:

SQL
select title_id, ytd_sales
from titles
where ytd_sales > 4095 and ytd_sales < 12000
title_id   ytd_sales
======== ===========
PC1035          8780
[1 row]
 

NOT BETWEEN

The phrase NOT BETWEEN finds all the rows that are not inside the range. To find all the books with sales outside the range of 4,095 to 12,000, type this:

SQL
select title_id, ytd_sales
from titles
where ytd_sales not between 4095 and 12000
title_id   ytd_sales
======== ===========
PS7777          3336
PS3333          4072
BU1111          3876
MC2222          2032
TC4203         15096
BU2075         18722
PS2091          2045
PS2106           111
MC3021         22246
TC3218           375
PS1372           375
[11 rows]
 

You can get the same results with comparison operators, but notice in this query that you use OR between the two ytd_sales comparisons rather than AND.

SQL
select title_id, ytd_sales
from titles
where ytd_sales < 4095 or ytd_sales > 12000
title_id   ytd_sales
======== ===========
PS7777          3336
PS3333          4072
BU1111          3876
MC2222          2032
TC4203         15096
BU2075         18722
PS2091          2045
PS2106           111
MC3021         22246
TC3218           375
PS1372           375
[11 rows]
 

This is another case where it's easy to get confused because of the way the question can be phrased in English. You might ask to see all books whose sales are less than 4,095 and all books whose sales are greater than 12,000. The logical meaning, however, calls for the use of the Boolean operator OR. If you substitute AND, you'll get no results at all because no book can have sales that are simultaneously less than 4,095 and greater than 12,000.

Lists (IN and NOT IN)

The IN keyword allows you to select values that match any one of a list of values. For example, without IN, if you want a list of the names and states of all the authors who live in California, Indiana, or Maryland, you can type this query:

SQL
select au_lname, state
from authors
where state = 'CA' or state = 'IN' or state = 'MD'
 

However, you get the same results with less typing if you use IN. The items following the IN keyword must be

  • inside parentheses

  • separated by commas

  • enclosed in quotes, if they are character or date values

SQL
select au_lname, state
from authors
where state in ('CA', 'IN', 'MD')

Following is what results from either query:

Results
au_lname                                 state
======================================== =====
Bennet                                   CA
Green                                    CA
Carson                                   CA
DeFrance                                 IN
Panteley                                 MD
McBadden                                 CA
Stringer                                 CA
Straight                                 CA
Karsen                                   CA
MacFeather                               CA
Dull                                     CA
Yokomoto                                 CA
O'Leary                                  CA
Gringlesby                               CA
White                                    CA
Hunter                                   CA
Locksley                                 CA
[17 rows]
 

The more items in the list, the greater the savings in typing by using IN rather than specifying each condition separately.

An important use for the IN keyword is in nested queries, also referred to as subqueries. For a full discussion of subqueries, see Chapter 8.

Selecting Null Values

From earlier chapters ("NULLs" in Chapter 1), you may recall that NULL is a placeholder for unknown information. It does not mean zero or blank.

To clarify this NULL–zero difference, take a look at the following listing showing title and advance amount for books belonging to one particular publisher.

SQL
select title, advance
from titles
where pub_id = '0877'
title                                                         advance
============================================================= =======
Silicon Valley Gastronomic Treats                                0.00
Sushi, Anyone?                                                8000.00
Fifty Years in Buckingham Palace Kitchens                     4000.00
The Gourmet Microwave                                        15000.00
Onions, Leeks, and Garlic: Cooking Secrets of the 
  Mediterranean                                               7000.00
The Psychology of Computer Cooking                             (NULL)
[6 rows]
 

A cursory perusal shows that one book (Silicon Valley Gastronomic Treats) has an advance of $0.00, probably due to extremely poor negotiating skills on the author's part. This author will receive no money until the royalties start coming in. Another book (The Psychology of Computer Cooking) has a NULL advance: Perhaps the author and the publisher are still working out the details of their deal, or perhaps the data entry clerk hasn't made the entry yet. Eventually, in this case, an amount will be known and recorded. Maybe it will be zero, maybe millions, maybe a couple of thousand dollars. The point is that right now the data does not disclose what the advance for this book is, so the advance value in the table is NULL.

What happens in the case of comparisons involving NULLs? Since a NULL represents the unknown, it doesn't match anything, even another NULL. For example, a query that finds all the title identification numbers and advances for books with moderate advances (under $5,000) will not find the row for MC3026, The Psychology of Computer Cooking.

SQL
select title_id, advance
from titles
where advance < $5000
title_id      advance
======== ============
PS7777        4000.00
PS3333        2000.00
MC2222           0.00
TC4203        4000.00
PS2091        2275.00
[5 rows]
 

Neither will a query for all books with an advance over $5,000:

SQL
select title_id, advance
from titles
where advance > $5000
title_id      advance
======== ============
PC8888        8000.00
TC7777        8000.00
PC1035        7000.00
BU2075       10125.00
PS2106        6000.00
MC3021       15000.00
TC3218        7000.00
PS1372        7000.00
[8 rows]
 

TIP

NULL is neither above nor below (nor equal to) $5,000 because NULL is unknown.

IS NULL

But don't despair! You can retrieve rows on the basis of their NULL/NOT NULL status with the following special pattern:

SYNTAX

WHERE column_name IS [NOT] NULL

Use it to find the row for books with null advances like this:

SQL
select title_id, advance
from titles
where advance is null
title_id      advance
======== ============
MC3026         (NULL)
PC9999         (NULL)
[2 rows]
 

SQL Variants

Some systems allow the equal sign, in addition to "is".

Adaptive Server Enterprise
select title_id, advance
from titles
where advance = null

Since IS NULL is specified in the ANSI standard, it makes sense to use it, rather than use the less common = NULL.

IS NULL and Other Comparison Operators

You can use the IS NULL pattern in combination with other comparison operators. Here's how a query for books with an advance under $5,000 or a null advance would look:

SQL
select title_id, advance
from titles
where advance < $5000
  or advance is null
title_id      advance
======== ============
PS7777        4000.00
PS3333        2000.00
MC2222           0.00
TC4203        4000.00
PS2091        2275.00
MC3026         (NULL)
PC9999         (NULL)
[7rows]
 

Matching Character Strings: LIKE

Some problems can't be solved with comparisons. Here are a few examples:

  • "His name begins with 'Mc' or 'Mac'—I can't remember the rest."

  • "We need a list of all the 415 area code phone numbers."

  • "I forget the name of the book, but it has a mention of exercise in the notes."

  • "Well, it's Carson, or maybe Karsen—something like that."

  • "His first name is 'Dirk' or 'Dick.' Four letters, starts with a D and ends with a k."

In each of these cases, you know a pattern embedded somewhere in a column, and you need to use the pattern to retrieve all or part of the row. The LIKE keyword is designed to solve this problem. You can use it with character fields (and on some systems, with date fields). It doesn't work with numeric fields defined as integer, money, and decimal or float. The syntax is this:

SYNTAX

WHERE column_name [NOT] LIKE 'pattern' 
   [ESCAPE escape_char]
 

The pattern must be enclosed in quotes and must include one or more wildcards (symbols that take the place of missing letters or strings in the pattern). You use the ESCAPE keyword when your pattern includes one of the wildcards and you need to treat it as a literal.

ANSI SQL provides two wildcard characters for use with LIKE, the percent sign (%) and the underscore or underbar (_).

Wildcard

Meaning

%

any string of zero or more characters

_

any single character


SQL Variants

Many systems offer variations (notations for single characters that fall within a range or set, for example). Check your system's reference guide to see what's available.

LIKE Examples

Following are answers to the questions just posed and the queries that generated them. First, the search for Scottish or Irish surnames:

SQL
select au_lname, city
from authors
where au_lname like 'Mc%' or au_lname like 'Mac%'
au_lname                               city
====================================== =================
McBadden                               Vacaville
MacFeather                             Oakland
[2 rows]
 

The LIKE pattern instructs the system to search for a name that begins with "Mc" and is followed by a string of any number of characters (%) or that begins with "Mac" and is followed by any number of characters. Notice that the wildcard is inside the quotes.

Now the 415 area code list:

SQL
select au_lname, phone
from authors
where phone like '415%'
au_lname                                 phone
======================================== ============
Bennet                                   415 658-9932
Green                                    415 986-7020
Carson                                   415 548-7723
Stringer                                 415 843-2991
Straight                                 415 834-2919
Karsen                                   415 534-9219
MacFeather                               415 354-7128
Dull                                     415 836-7128
Yokomoto                                 415 935-4228
Hunter                                   415 836-7128
Locksley                                 415 585-4620
(11 rows affected)
 

Here again, you're looking for some known initial characters followed by a string of unknown characters.

The book with "exercise" somewhere in its notes is a little trickier. You don't know if it's at the beginning or end of the column, and you don't know whether the first letter of the word is capitalized. You can cover all these possibilities by leaving the first letter out of the pattern and using the same "string of zero or more characters" wildcard at the beginning and end of the pattern.

SQL
select title_id, notes
from titles
where notes like '%xercise%'
title_id           notes
======== ======================================================
PS2106   New exercise, meditation, and nutritional techniques
         that can reduce the shock of daily interactions.
         Popular audience.  Sample menus included, exercise
         video available separately.
[1 row]
 

When you know the number of characters missing, you can use the single-character wildcard, (_). In the next example, the first letter is either K or C and the next to the last is either e or o. If the authors table contained the last name Karson, it would also be included in the results. Starson or Karstin would not.

SQL
select au_lname, city
from authors
where au_lname like '_ars_n'
au_lname                                 city
======================================== ====================
Carson                                   Berkeley
Karsen                                   Oakland
(2 rows affected)

The next example is similar to the previous one. It looks for four-letter first names starting with D and ending with k.

SQL
select au_lname, au_fname, city
from authors
where au_fname like 'D_ _k'
au_lname                            au_fname           city
=================================== ================== ==============
Stringer                            Dirk               Oakland
Straight                            Dick               Oakland
[2 rows]
 

NOT LIKE

You can also use NOT LIKE with wildcards. To find all the phone numbers in the authors table that do not have 415 as the area code, you could use either of these queries (they are equivalent):

SQL
select phone
from authors
where phone not like '415%'
select phone
from authors
where not phone like '415%'
 

Escaping

Wildcard characters are almost always used together with the LIKE keyword. Without LIKE, the wildcard characters are interpreted literally and represent exactly their own values. The query that follows finds any phone numbers that consist of the four characters "415%" only. It will not find phone numbers that start with 415:

SQL
select phone
from authors
where phone = '415%'
 

What if you want to search for a value that contains one of the wildcard characters? For example, in one row in the titles table, the notes column contains a claim to increase readers' friends by some percentage. You can search for the percent mark by using ESCAPE to appoint a character to strip the percent sign of its magic meaning and convert it to an ordinary character. A wildcard directly after the escape character has only its literal meaning. Other wildcards continue to have their special significance. In the following LIKE expression, you are looking for a literal percent sign somewhere in the notes column. Since it's probably not the first or last character, you use wildcard percent signs at the beginning and end of the expression and a percent sign preceded by the escape character in the middle.

SQL
select title_id, notes 
from titles 
where notes like '%@%%' escape '@'
title_id      notes
======        ======================================================= 
TC7777        Detailed instructions on improving your position in
              life by learning how to make authentic Japanese sushi
              in your spare time. 5-10% increase in number of
              friends per recipe reported from beta test.
[1 row]
 

Following are some examples of LIKE with escaped and unescaped wildcard character searches (the @ sign is the designated escape character):

Symbol

Meaning

LIKE '27%'

27 followed by any string of 0 or more characters

LIKE '27@%'

27%

LIKE '_n'

an, in, on, etc.

LIKE '@_n'

_n


Like, Is IN LIKE Equals . . . ?

Don't get confused by the similarities of equal, IN, and LIKE.

Equals

Use the equal comparison operator when you want all data that exactly matches a single value—you know just what you are looking for. You can use the equal comparison operator with any kind of data—character, date, or numeric. Put quotes around character and date data. In this query, you are looking for authors named "Meander."

SQL
select au_lname, au_fname,  phone
from authors 
where au_fname = 'Meander'
au_lname                      au_fname      phone
============================= ============= ============
Smith                         Meander       913 843-0462
[1 row]
 

IN

Use IN when you have two or more values and are looking for data that exactly matches any one of these values. IN works with any kind of data—character, date, or numeric. Put quotes around character and date data. Here, you are trying to find any writers called "Meander," "Malcolm," or "Stearns."

SQL
select au_lname, au_fname,  phone
from authors 
where au_fname in ( 'Meander', 'Malcolm', 'Stearns')
au_lname                      au_fname      phone
============================= ============= ============
MacFeather                    Stearns      415 354-7128
Smith                         Meander      913 843-0462
[2 rows]
 

LIKE

Use LIKE when you want to find data that matches a pattern. For example, if you are trying to locate all the people with the letters "ea" in their names, you could write code like this:

SQL
select au_lname, au_fname,  phone
from authors
where au_fname like '%ea%' 
au_lname                      au_fname      phone
============================= ============= ============
McBadden                      Heather       707 448-4982
MacFeather                    Stearns       415 354-7128
Smith                         Meander       913 843-0462
[3 rows]
 

In most cases, LIKE works with character and date data only.

SQL Variants

Some systems support autoconvert capabilities that allow you to use LIKE with numeric data. Notice that you have to put quotes around the pattern, just as if it were character:

Oracle
SQL> select title_id, price
  2  from titles
  3  where price like '%.99'
TITLE_     PRICE
------ ---------
BU1032     29.99
PS7777     17.99
PS3333     29.99
MC2222     29.99
TC7777     24.99
BU2075     12.99
MC3021     12.99
BU7832     29.99
8 rows selected.
 

Other systems give an error for the same code:

SQL Server
select title_id, price
from titles
where price like '%.99'
Server: Msg 257, Level 16, State 3, Line 1
Implicit conversion from data type money to varchar is not allowed.
Use the CONVERT function to run this query.
 

Comparing the Three

The guidelines for differentiating among equal, IN, and LIKE are compared and summarized in Figure 4.6.

Figure 4.6 Equal, IN, LIKE

  • + Share This
  • 🔖 Save To Your Account

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020