The EXPLAIN verb is your best friend.   This will tell you exactly how the SQL engine proposes to determine the answers that you seek.   Efficiency is very much a matter of exactly how you say it, and this tangle of subqueries is likely to be profoundly expensive in any system at all.