Java Reference
In-Depth Information
Conversely, to find all records in the Contact_Info database with any other Last_Name, you would use
a
not equals
(<>) query like this:
SELECT * FROM Contact_Info WHERE Last_Name <> 'Corleone';
Using the greater than and less than operators
The greater than (>) and less than (<) operators can also be used for lexical comparison of
CHAR
and
VARCHAR
values, so to find all records in the Contact_Info database with a Last_Name that comes
after Corleone alphabetically, you would use a query like this:
SELECT * FROM Contact_Info WHERE Last_Name > 'Corleone';
Similarly, you can combine the greater than and equals operators to find all records in the Contact_Info
database with a last name including or after Corleone in the alphabet. Here's an example:
SELECT * FROM Contact_Info WHERE Last_Name >= 'Corleone';
Using the IS NULL Operator
As mentioned in
Chapters 5
and
6
, in the discussions about creating and populating database tables,
the value in a field can sometimes be
NULL
, indicating that there is nothing in the field. It is important to
understand that this really does mean nothing, rather than, for example, a value of zero in the case of a
numeric field, or white space in the case of a
CHAR
or
VARCHAR
field.
Since the
NULL
represents an absence of data, it can't be evaluated using Greater Than, Equals, or
Less Than. SQL provides a special
IS
NULL
operator to test for
NULL
. If, for example, you added a
column to the Contact_Info table for Cell Phone numbers, leaving it
NULL
when you don't have a
contact's cell phone number, you could query the table for contacts without cell phones using this
code:
SELECT * FROM Contact_Info WHERE Cell_Phone IS NULL;
Using the IS NOT NULL operator
Another common requirement is to find records where a specific field is
IS NOT NULL
. For example, to
query the Contact_Info table for contacts with cell phones you could use this code:
SELECT * FROM Contact_Info WHERE Cell_Phone IS NOT NULL;
Note
You can't test for
NULL
using equality ( = ) or inequality ( <> ) operators, since, by
definition, there is nothing in the field.
CHAR and VARCHAR Operators
In addition to letting you use the comparison operators to work with strings, SQL adds these dedicated
string operators for use with
CHAR
and
VARCHAR
variables:
LIKE
NOT LIKE
String concatenation
