CTEs to the Rescue: Referencing Derived Tables with Multiple Aliases in MariaDB 5.5

1. Define the Derived Table using CTE:

   • A CTE is a temporary named result set defined within a SQL statement.
   • You can use a WITH clause to define the CTE, followed by a subquery that specifies the columns and data for the derived table.
   • Assign an alias (e.g., derived_data) to the CTE for reference.

2. Join the CTE to itself with multiple aliases:

   • In the main SELECT statement, you can reference the CTE multiple times, assigning a different alias each time.
   • This creates separate virtual tables based on the same underlying data in the CTE.

Here's an example:

WITH derived_data AS (
  SELECT col1, col2, col3
  FROM source_table
)
SELECT *
FROM derived_data AS alias1, derived_data AS alias2;

In this example:

• The CTE derived_data is defined, selecting columns from the source_table.
• The SELECT statement retrieves all columns (*) from the derived table.
• The CTE is then referenced twice with aliases alias1 and alias2.

This essentially creates two virtual tables based on the data in derived_data, allowing you to reference the same data with different names within the query.

Benefits of using CTEs with aliases:

• Improved readability: Aliases can make complex queries easier to understand by providing meaningful names to derived tables.
• Reusability: You can reference the CTE multiple times within the query with different aliases, reducing redundancy.

WITH order_data AS (
  SELECT order_id, customer_id, product_name, quantity
  FROM orders
)
SELECT 
  o1.order_id, o1.customer_id, o1.product_name, o1.quantity AS ordered_qty,
  o2.quantity AS repeated_qty
FROM order_data AS o1
LEFT JOIN order_data AS o2 ON o1.product_name = o2.product_name
WHERE o1.order_id <> o2.order_id;

1. Defines a CTE order_data containing order details.
2. Uses the CTE twice with aliases o1 and o2.
3. Filters data based on product name (assuming same product can appear in multiple orders).
4. Uses different aliases (ordered_qty and repeated_qty) to distinguish quantities from the same CTE.

Example 2: Calculating statistics with aliases

WITH product_sales AS (
  SELECT product_id, product_name, SUM(quantity * price) AS total_sales
  FROM order_details
  GROUP BY product_id, product_name
)
SELECT 
  p.product_id, p.product_name, ps.total_sales,
  ps.total_sales / (SELECT SUM(total_sales) FROM product_sales) AS sales_percentage
FROM products AS p
INNER JOIN product_sales AS ps ON p.product_id = ps.product_id;

1. Defines a CTE product_sales to calculate total sales per product.
2. Uses the CTE with alias ps to join with the products table.
3. Calculates a sales percentage for each product using another reference to the CTE.

1. Subqueries with aliases:

   • You can nest subqueries within your main SELECT statement, assigning aliases to each subquery.
   • This approach can become cumbersome for complex queries and might affect readability.

2. Temporary tables:

   • Create a temporary table based on your derived table logic.
   • Reference the temporary table multiple times with different aliases in your main SELECT statement.
   • This method requires additional steps to create and drop the temporary table, potentially impacting performance.

Here's a brief example of using subqueries with aliases:

SELECT col1, col2, col3,
  (SELECT SUM(value) FROM another_table WHERE id = t1.id) AS sum1,
  (SELECT AVG(value) FROM another_table WHERE id = t1.id) AS avg1
FROM source_table AS t1;

• Two subqueries are used with aliases sum1 and avg1 to calculate aggregations on another table based on the id from the main table.

Keep in mind:

• Subqueries can become nested and complex, reducing readability.
• Temporary tables might introduce overhead for creation and deletion.