Databases Reference
In-Depth Information
The result is:
HQCITY
Chicago
New York
Now combine the two with the EXCEPT keyword:
SELECT HQCITY FROM CUSTOMER WHERE SPNUM = 137
EXCEPT
SELECT HQCITY FROM CUSTOMER WHERE SPNUM = 186
The result is:
HQCITY
Los Angeles
This is because the second query returned Chicago and New York. New York
was returned by both queries, and therefore is excluded when you combine the
queries with EXCEPT. You get the opposite result when you use the INTERSECT
keyword. It returns the values that appear in both queries. For example:
SELECT HQCITY FROM CUSTOMER WHERE SPNUM = 137
INTERSECT
SELECT HQCITY FROM CUSTOMER WHERE SPNUM = 186
gives you the following result:
HQCITY
New York
This is because New York is the only city that was included in the result set of
both queries.
7.1.7 Using SELECT with Other Commands
You can also use SELECT as the value source with other statements. To see this,
we'll use a couple of examples using the INSERT and UPDATE statements.
You can use a SELECT statement to replace the values clause when using
INSERT. For example, suppose you have a table named EmpCopy with three
columns: EmployeeNumber, FirstName, and LastName. You want to use values
from SALESPERSON as your source. You might use a statement like the following:
INSERT EmpCopy SELECT SPNUM, SPNAME, SPFIRSTNAME
FROM SALESPERSON
In this example, SELECT is used as a clause of the INSERT statement. Notice
that you do not enclose the SELECT clause in parentheses in this statement. The
only result returned by the command is a message specifying how many rows
are inserted by the operation.
You can also use SELECT in situations where a scalar input value is expected.
Consider the following:
