SELECT DISTINCT wine_id FROM wine WHERE wine_id IN (SELECT wine_id from inventory);
The query returns the
wine_id values from the wine table that are found in the inventory table. Nested queries use the
In many cases, a nested query can be rewritten as a join query. For example, to find the wines that are in stock, you can use the following join query:
SELECT DISTINCT wine.wine_id FROM wine, inventory WHERE wine.wine_id = inventory.wine_id;
However, some nested queries can't be rewritten as join queries; for difficult queries, temporary tables are often a useful workaround.
A limitation of
UPDATE is that only one table can be specified in the
FROM clause. This problem is particular to MySQL and related to the lack of support for nested queries. This limitation can make modifications of data difficult. For example, it prevents data being deleted or updated using the properties of another table. A solution involves data being copied to a temporary table using a combined
SELECT statement that joins together data from more than one table. Then, the data can be deleted or updated in the temporary table and then transferred back to the original table. Another approach, using the
concat( ) string function, is discussed in Section 126.96.36.199 in the MySQL manual.
DELETE problems, consider adding additional attributes to tables at design time. For example, in the winestore we added a
DATE attribute to the items table so that shopping-cart items can be removed easily if they aren't purchased within one day. Removing rows from the items table based on the
DATE in the orders table is difficult without support for nested queries.
MySQL doesn't support stored procedures or triggers. Stored procedures are queries that are compiled and stored in the DBMS. They are then invoked by the middle-tier application logic, with the benefit that the query is parsed only once and there is less communication overhead between the middle and database tiers. Triggers are similar to stored procedures but are invoked by the DBMS when a condition is met. Stored-procedure support is planned for MySQL, but trigger support isn't.
Views aren't supported in MySQL. Views consolidate read-only access to several tables based on a join condition. For example, a view might allow a user to browse the sales made up to April without the need to create a temporary table, as we did in the example in Section 3.8. View support is planned for the future.
Limitations that we don't discuss here include the lack of support for foreign keys and cursors. More detail on the limitations of MySQL can be found in Section 1.4 of the manual distributed with MySQL.