使用子查询实现多表连接查询，寻求更优解决方案

首先你的原查询存在语法错误：INNER JOIN film语句末尾的分号需要删除，否则WHERE子句会被识别为独立SQL语句，导致执行报错。

修正后的原查询 #

SELECT DISTINCT first_name, last_name, title, description, release_year, rating
FROM film_actor
INNER JOIN actor ON film_actor.actor_id = actor.actor_id
INNER JOIN film ON film_actor.film_id = film.film_id
WHERE rental_rate > (SELECT AVG(rental_rate) FROM film);

更简便高效的实现方式 #

1. 提前计算平均租金，减少表扫描次数
   原查询的子查询会单独扫描一次film表计算平均租金，改用CROSS JOIN提前计算该值，避免重复扫描：

   SELECT DISTINCT first_name, last_name, title, description, release_year, rating
   FROM film_actor
   INNER JOIN actor ON film_actor.actor_id = actor.actor_id
   INNER JOIN film ON film_actor.film_id = film.film_id
   CROSS JOIN (SELECT AVG(rental_rate) AS avg_rental FROM film) AS avg_rates
   WHERE film.rental_rate > avg_rates.avg_rental;
   

2. 用表别名简化语句，逻辑更清晰
   给表添加别名后，SQL结构更简洁，也更贴合“演员-参演关系-电影信息”的业务逻辑：

   SELECT DISTINCT a.first_name, a.last_name, f.title, f.description, f.release_year, f.rating
   FROM actor a
   INNER JOIN film_actor fa ON a.actor_id = fa.actor_id
   INNER JOIN film f ON fa.film_id = f.film_id
   WHERE f.rental_rate > (SELECT AVG(rental_rate) FROM film);
   

3. 按需移除DISTINCT
   如果film_actor表中(actor_id, film_id)是唯一键（即同一个演员不会重复关联同一部电影），那么DISTINCT可以直接去掉，因为JOIN后的结果本身不会存在重复行：

   SELECT a.first_name, a.last_name, f.title, f.description, f.release_year, f.rating
   FROM actor a
   INNER JOIN film_actor fa ON a.actor_id = fa.actor_id
   INNER JOIN film f ON fa.film_id = f.film_id
   WHERE f.rental_rate > (SELECT AVG(rental_rate) FROM film);
   

补充说明 #

• DISTINCT(first_name)是错误写法，DISTINCT作用于整个SELECT列集合，而非单个列，正确写法为SELECT DISTINCT col1, col2...。

内容的提问来源于stack exchange，提问作者Mason