Use IN if you want to know the values contained in the subquery result table.
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
Graphical Depiction of Two Ways to Answer a Query with Different Types of Joins (1 of 2) a) Join query approach Join vs. Subquery
Graphical Depiction of Two Ways to Answer a Query with Different Types of Joins (2 of 2) b) Subquery approach Join vs. Subquery
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.
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