Databases Reference
In-Depth Information
The easiest way to understand how these keywords work is to see them
in use. For this purpose, we'll use a database based on the fictitious General
Hardware Company business. Figure 7-1 is a SQL Server database diagram of
General Hardware showing you the tables contained in the database.
The key icon identifies each table's primary key. The links between tables
identify foreign key relationships established between the tables. These relation-
ships are used to maintain relational integrity between the tables. If you want to
retrieve data based on these relationships, you must use joining statements,
which are statements that combine results from multiple tables.
7.1.2 Filtering Your Result
Let's start with options for filtering your result. You can use the comparison oper-
ators
,
,
,
,
, and both
and !
for “not equal to” for simple
filtering. If you need to combine conditions, use the AND and OR operators:
▲
AND:
the search condition is satisfied if both conditions are satisfied.
▲
OR:
the search condition is satisfied if either condition is satisfied.
Using AND and OR
When using both AND and OR, AND is evaluated first. You may need to use
parentheses to control operator precedence or to leave no question about how
a query is evaluated. For example, the following query uses parentheses, even
though operators are evaluated in default order:
SELECT CUSTNUM, CUSTNAME FROM CUSTOMER
WHERE HQCITY='New York' OR
(CUSTNUM>1500 AND HQCITY='Atlanta')
This query captures customers with an HQCITY value of New York, as well as
those who meet both of the remaining conditions, CUSTNUM over 1500 and
an HQCITY of Atlanta. This results in:
CUSTNUM CUSTNAME
0121 Main St. Hardware
1525 Fred's Tool Stores
1826 City Hardware
2198 Western Hardware
2267 Central Stores
If you want the OR operator evaluated first, you would use the following
query:
SELECT CUSTNUM, CUSTNAME FROM CUSTOMER
WHERE (HQCITY='New York' OR CUSTNUM>1500) AND
HQCITY='Atlanta'
