最有效的MySQL (面试查询)

Question

最近在一次采访中，我遇到了以下问题：

我有一个有以下表的数据库(为了这个例子，假设这些表是有意义的)：

• 用户(id，name，cc_id)
• CreditCard(id)
• 电影(id，名字)
• 购买(movie_id，cc_id)

我想找到购买电影“黑暗骑士”的顾客的名字。

我的回答是：

SELECT name FROM User AS u
    INNER JOIN CreditCard AS cc ON u.id = cc.id
    INNER JOIN Purchase AS p ON p.movie_id = 
        (SELECT id FROM Movie AS m WHERE m.name = "Dark Knight") AND cc.id = p.cc_id

撇开语法不谈，我想知道这是否是一个更有效的解决这个问题的方法。

Answer 1

最好也加入movie表，不要加入creditcard

SELECT u.name 
FROM `User` AS u
INNER JOIN Purchase AS p ON p.cc_id = u.cc_id
INNER JOIN movie m on p.movie_id = m.id
WHERE m.name = 'Dark Knight'

Answer 2

SELECT name from User as u
    JOIN CreditCards as cc on cc.id = u.cc_id
    JOIN Purchase AS p ON p.cc_id = c.id
    JOIN Movie AS m on m.id = p.movie_id
WHERE m.name = 'Dark Night';