The Common Table Expression (CTE) is a temporary result set that can be referenced within the context of a SELECT, INSERT, UPDATE, or DELETE statement. In Oracle, CTEs are defined using the WITH clause. To illustrate, consider two tables below:
tbl_restaurant_menus:
tbl_daily_nutrients:
In nutrition labeling, the DV% indicates the percentage of a recommended daily intake of a particular nutrient that is provided by a serving of a food product. This can be calculated by a formula:
Now, let's consider a scenario where computing DV% for each nutrient of the menus. After that you want to retrieve rows with vitamin_c DV% greater than the average.
To achieve this, I firstly created a row reference based on the tbl_daily_nutrients table. The query below is using conditional aggregation to pivot the data in the tbl_daily_nutrients table. Originally, the table has columns: nutrient and daily_value, and each row represents the daily value for a specific nutrient, which is called long format. Then the CASE statements conditionally select the daily_value for each nutrient and the MAX function is used as a placeholder for summarizing rows: there is only one row per each nutrient daily value.
Moving onto the next task, we want to calculate DV% for each restaurant menu. Here, naming the pivot as a temporary result set and refer it for the calculations would be very convenient:
In the query shown above, the pivot result is saved as a CTE and I named this temporary set as a nutrition_info. Then the main query selects information from the tbl_restaurant_menus table and joined with the CTE using a constant condition (ON 1=1). This creates a Cartesian join, as there is no specific condition to match rows, resulting in every row from tbl_restaurant_menus being combined with the single row from nutrition_info. Here, since I gave a name nutrition_info to CTE, we can easily refer the temporary pivot results. Lastly, the query calculates DV% as desired:
SQL's CTE boosts code readability and maintainability by modularizing complex queries, promoting code-reuse, simplifying code-organization, as well as handling hierarchical data with recursion. They unlock window functions for analysis, eliminate redundancy, potentially optimize execution plans, and encapsulate logic for better focus. While their effectiveness depends on the situation and database, CTEs are a powerful tool for crafting clear, efficient SQL queries.
0 Comments