When to use (SELCET * FROM CTE) or just LEFT JOIN CTE?

Whether to use

SELECT * FROM CTE
or
LEFT JOIN CTE

directly depends on the specific requirements of your query and the structure of your Common Table Expression (CTE).

If your CTE returns a single result set that can be easily joined to other tables in your query, then using LEFT JOIN CTE directly can be more efficient and easier to read.

However, if your CTE includes multiple queries or subqueries that need to be combined before being joined to other tables, or if you need to apply further filtering or sorting to the CTE results before joining to other tables, then using SELECT * FROM CTE can provide more flexibility.

Additionally, keep in mind that when using LEFT JOIN CTE directly, the CTE will be executed for each row of the left-hand table in the join. This can cause performance issues if the CTE is complex or returns a large number of rows.

Let's take an example

Suppose we have a table called orders that contains information about customer orders, and we want to use a CTE to calculate the total revenue generated by each customer. The CTE might look something like this:

WITH customer_revenue AS (
  SELECT customer_id, SUM(price * quantity) AS total_revenue
  FROM orders
  GROUP BY customer_id
)

Now, suppose we want to join the results of this CTE with another table called customers that contains information about each customer. We could do this using either SELECT * FROM customer_revenue or LEFT JOIN customer_revenue:

-- Option 1: SELECT * FROM CTE
SELECT *
FROM customers
JOIN (SELECT * FROM customer_revenue) AS revenue
  ON customers.customer_id = revenue.customer_id;

-- Option 2: LEFT JOIN CTE
SELECT *
FROM customers
LEFT JOIN customer_revenue
  ON customers.customer_id = customer_revenue.customer_id;

In this case, both options will produce the same result. However, there are some important differences to consider:

• Option 1 (SELECT * FROM CTE) allows us to apply further filtering or sorting to the CTE results before joining to other tables. For example, we could add a WHERE clause to the inner query to only include customers who have generated more than $100 in revenue:

SELECT *
FROM customers
JOIN (SELECT * FROM customer_revenue WHERE total_revenue > 100) AS revenue
  ON customers.customer_id = revenue.customer_id;

• Option 2 (LEFT JOIN CTE) can be more efficient if the CTE is complex or returns a large number of rows. This is because the CTE is only executed once and then joined to the other tables, rather than being executed once for each row in the join.

In summary, both options are valid and can be used depending on the specific requirements of your query and the structure of your CTE. It is recommended to test both approaches and choose the one that provides the best performance and readability for your use case.