Database Reference
In-Depth Information
SELECT INTO
Use the SELECT command with an INTO clause to insert the rows retrieved by a query into a table. As
shown in Figure B-14, you must indicate the name of the table into which the row(s) will be inserted and the
query whose results will be inserted into the named table.
355
Clause
Description
Required?
SELECT ield list
Indicates the list of ields to be selected.
Yes
INTO
table name
Indicates the name of the table into which the row(s) will be inserted.
Yes
remainder of query
Indicates the remainder of the query (for example, FROM clause
and WHERE clause) whose results will be inserted into the table.
Yes
FIGURE B-14
SELECT command with INTO clause
The following SELECT command with an INTO clause inserts rows selected by a query into the
SmallCust table:
SELECT *
INTO SmallCust
FROM Customer
WHERE CreditLimit
<¼
7500
;
Oracle and MySQL do not support the previous SELECT command. To accomplish the same task, you
would create the SmallCust table using a CREATE TABLE command. You would then use an INSERT com-
mand to insert the appropriate data into the SmallCust table.
SUBQUERIES
In some cases, it is useful to obtain the results you want in two stages. You can do so by placing one query
inside another. The inner query is called a subquery and is evaluated first. After the subquery has been eval-
uated, the outer query can be evaluated.
The following command contains a subquery that produces a list of part numbers located in warehouse 3.
The outer query then produces those order numbers in the OrderLine table that are on any rows containing
a part number in the list.
SELECT OrderNum
FROM OrderLine
WHERE PartNum IN
(SELECT PartNum
FROM Part
WHERE Warehouse
¼'
3
'
)
;
UNION
Connecting two SELECT commands with the UNION operator produces all the rows that would be in the
results of the first command, the second command, or both.
