Subqueries in SELECT
Sub-queries in select statements are usually slow and it is possible to rewrite them using joins. However, there may be instances where they may be better performing or inevitable. There are several flavors of sub-queries with increasing complexity. SQL Frames supports many of these flavors.
Uncorrelated sub-queries
simple sub-select
Below the order details are shown along with the unit price of a specific product.
aggregate sub-select
Below the order details are shown along with the average unit price of all the products.
Correlated sub-queries
simple sub-select
Below the order details are shown along with the product name by resolving it from the products table.
joins in sub-select
Below the order details are shown along with the category name by resolving the product first and then the category via multiple joins.
aggregates in sub-select
Below the orders are shown along with the total order amount by aggregating from the order details.