Java Reference
In-Depth Information
Caution
Although SQL ignores case in commands, table names, column names, and so on,
case can matter when you are using a name in a
WHERE
clause. Thus, 'Corleone' and
'CORLEONE' are not necessarily the same. You should read the documentation for
your particular DBMS.
While the simple SELECT statements discussed so far in this chapter give you an idea of what can be
done in a SQL query, you are likely to need to use more complex queries in practice. The
next section
discusses creating more complex queries.
Using Operators in More Complex WHERE Clauses
The queries discussed so far have been very simple, but in practice you will frequently be using
queries that depend on the values of a number of fields in various combinations. SQL provides a
number of operators that enable you to create more complex queries based on value comparisons.
In practice, many queries will require the evaluation of more than a single condition or test. In such
cases
operators
are used in the
WHERE
clause to specify a combination of conditions which must be
evaluated. SQL has the following types of operators:
DISTINCT
TOP
Comparison operators
Character comparison
Logical
Arithmetic
IN
and
BETWEEN
Set operators
Note
There is also a keyword
ALL
, as in
SELECT ALL
, but since
ALL
is implied unless
DISTINCT
is used, the expression SELECT ALL is rarely, if ever, used in practice.
The DISTINCT Operator
A basic
SELECT
statement tells the database-management system to return all records matching the
query in the
ResultSet
. For example, you could request all Last_Names from Contact_Info using this
query:
SELECT Last_Name
FROM Contact_Info;
Using the data shown in
Table 7-1
would give you Corleone repeated five times.
The
DISTINCT
operator tells the database-management system not to return duplicate records in a
ResultSet
. For example, to return all Last_Names from the Contact_Info table with no duplicates,
you would use this query:
SELECT DISTINCT Last_Name
FROM Contact_Info;
