Why Does MySQL Prohibit Subqueries in a View's FROM Clause?

"View's SELECT Contains a Subquery in the FROM Clause": Understanding MySQL View Constraints

MySQL views provide a convenient way to simplify data retrieval by presenting a customized subset of data from underlying tables. However, there are certain restrictions to ensure the integrity and performance of these views.

One such restriction is the prohibition of subqueries in the FROM clause of a view. In the provided example, the query:

create view view_credit_status as 
(select credit_orders.client_id, 
        sum(credit_orders.number_of_credits) as purchased, 
        ifnull(t1.credits_used,0) as used 
 from credit_orders
 left outer join (select * from (select credit_usage.client_id, 
                                        sum(credits_used) as credits_used 
                                 from credit_usage 
                                 group by credit_usage.client_id) as t0
                  ) as t1 on t1.client_id = credit_orders.client_id
 where credit_orders.payment_status='Paid'
 group by credit_orders.client_id)

throws the error "View's SELECT contains a subquery in the FROM clause" because it contains the following subquery:

(select * from (select credit_usage.client_id, 
                                        sum(credits_used) as credits_used 
                                 from credit_usage 
                                 group by credit_usage.client_id) as t0
)

To resolve this issue, one can create separate views for the subqueries. For instance, one could create the following views:

create view view_credit_orders as 
(select credit_orders.client_id, 
        sum(credit_orders.number_of_credits) as purchased 
 from credit_orders
 where credit_orders.payment_status='Paid'
 group by credit_orders.client_id)

create view view_credit_usage as 
(select credit_usage.client_id, 
        sum(credit_usage.credits_used) as credits_used 
 from credit_usage 
 group by credit_usage.client_id)

Once these views are created, the original view can be created using the following modified query:

create view view_credit_status as 
(select view_credit_orders.client_id, 
        view_credit_orders.purchased, 
        ifnull(view_credit_usage.credits_used,0) as used 
 from view_credit_orders
 left outer join view_credit_usage 
  on view_credit_orders.client_id = view_credit_usage.client_id)

The above is the detailed content of Why Does MySQL Prohibit Subqueries in a View's FROM Clause?. For more information, please follow other related articles on the PHP Chinese website!