Use IN if you want to know the values contained in the subquery result table

Use in if you want to know the values contained in

This preview shows page 27 - 35 out of 35 pages.

Use IN if you want to know the values contained in the subquery result table.
Image of page 27
Join vs. Subquery Some queries could be accomplished by either a join or a subquery Query : What are the name and address of the customer who placed order number 8? Join version Subquery version
Image of page 28
Graphical Depiction of Two Ways to Answer a Query with Different Types of Joins (1 of 2) a) Join query approach Join vs. Subquery
Image of page 29
Graphical Depiction of Two Ways to Answer a Query with Different Types of Joins (2 of 2) b) Subquery approach Join vs. Subquery
Image of page 30
Subquery : Derived Tables Show the product description and standard price for all products that have a standard price that is higher than the average standard price. SELECT ProductDescription, ProductStandardPrice FROM (SELECT AVG(ProductStandardPrice) AvgPrice FROM Product_T) AS PR, Product_T WHERE ProductStandardPrice > AvgPrice ; Subqueries can be located at different spot of a query. In this case, the subquery returns a table that is within the FROM clause. In this case we call the result of the subquery a Derived table. The FROM clause use two tables : Product_T and the Derived table represented by the alias PR. Note that Derived tables are required to have an alias when you use MySQL.
Image of page 31
Union Queries Combine the output (union of multiple queries) together into a single result table First query Second query In general a UNION is structured like: SELECT statement UNION SELECT statement
Image of page 32