A feature that is not yet available in MySQL is the subselect. The subselect allows you to base a query
on the results of another query. For example
SELECT * FROM Customers
WHERE Customer_ID
IN (SELECT Customer_ID FROM Orders AS O, Shippers As S
WHERE S.Shipper_ID=12
AND S.Shipper_ID = O.Shipper_ID)This is not the greatest example, but it shows basically what a subselect is. There are certainly
workarounds for this type of select. You could do more multiple joins inside a single query. Of course,
this isn't the best answer, but until MySQL has subselects, that is all that you can do.
Another new feature that is available in version 3.23 is the ability to SELECT INTO a table. This is an
extremely useful feature, especially in batch programming. The table that is created here can only be a
temporary table and will be dropped when the current connection is dropped. To do this, enter the
following command:
CREATE TEMPORARY TABLE temp_table SELECT * FROM Customers
This command creates a temporary table that holds an exact copy of the records contained in the
Customers table. This feature is close to the SELECT INTO that is offered by other RDBMs of the
same class. Another workaround for SELECT INTO is the INSERT INTO command. It basically does
the same thing.
INSERT
The INSERT INTO command adds new rows of data to an existing table. The format is basically the
same as a SELECT statement. In fact, you can use a combination of the INSERT INTO and SELECT
syntax to insert rows of data from one table into a different one. The basic INSERT INTO command
looks like the following:
INSERT INTO Customers VALUES(NULL, "Glen", Brazil")
The name of the table into which you want to insert the new record is after the INTO keyword. Following
the VALUES keyword is a list of values that you want to insert. If you insert a record this way, you must
have a value for every column, and the order of the data must match the order in which your columns
were declared in the database. In the example, you have a table that is defined by the following
columns: Customer_ID, First_Name, Last_Name. When you insert a row of data into this table, the
data must fall in this specified order—the Customer_ID value, the first name, and so on. The inserted
values must match the column order precisely, or an error will occur. To avoid this hassle, SQL allows
you to name the values you are going to insert. For example, your SQL INSERT statement could look
like the following:
INSERT INTO Customers (Customer_ID, Last_Name, First_Name)
VALUES (NULL, "Brazil", "Glen")
Using this technique, the order or number of columns in the table do not have to match. This is the
preferred way of inserting a row of data. In this way, you specify the order and the actual columns that
you want to insert. You are not bound by the table's order but your own. This method also allows you to
add the data you want to add—you don't have to add the values you don't have or don't want to add as
you would have to with the previous method.
With MySQL version 3.23, you can use the INSERT INTO...SELECT statement. This statement
allows you to add data to a table based on the criteria in the SELECT statement. For example, you could
extract required data from a table and insert that information into another. Suppose that you needed to
extract all the customers from the Customers table that are from Maryland and place them into a table
that the shippers use so they can plan their trucking routes. To do this, you have to have an existing
table that you'll call Shipper_Info. This table has the following schema:
CREATE TABLE Shipper_Info (
Shipper_Info_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
Customer_Name VARCHAR(50),
Address VARCHAR(30),
City VARCHAR(20),
Zip VARCHAR(15),