mysql隐式联接+子查询

Question

我打算使用隐式连接来获得所有由angelina jolie担任导演或她主演的电影，以下是我迄今为止的

SELECT DISTINCT title, relYear
FROM actor,movie 
WHERE director ='Angelina Jolie' OR aID in (SELECT aID 
FROM actor
WHERE fName='Angelina' and surname='Jolie'

以下是的相关表格

movie(id, title, relYear, category, runTime, director,
studioName, description, rating)
actor(aID, fName, surname, gender)
stars(movieID, actorID)
movGenre(movieID, genre) 

这会返回所有的电影，我想这是因为aID in (SELECT aID

我不知道如何在不对三个表使用显式联接的情况下做到这一点，子查询甚至是最有效的方法吗？感谢

Answer 1

这就是我在MSSQL上要做的。我认为它应该在MySql上运行。

Select title, relYear FROM movie WHERE director = 'Angelina Jolie' OR id IN 
(SELECT movieId FROM stars inner join actor ON stars.actorId = actor.aID WHERE
actor.fName = 'Angelina' AND surname = 'Jolie')