mysql - 在加入它们之前在多个表上使用相同的 where 子句

Question

是否有更好的方法连接两个表并在它们上使用相同的 where 子句？我是这样做的:

SELECT a.account_id,
       a.account,
       b.tax,
       b.rate
FROM   (SELECT account_id,
               account
        FROM   accounts
        WHERE  account_id IN (SELECT account_id
                              FROM   account_location
                              WHERE  location = "A")) AS a
       LEFT JOIN (SELECT tax,
                         rate
                  FROM   tax
                  WHERE  tax_id IN (SELECT tax_id
                                    FROM   account_tax
                                    WHERE  account_id IN (SELECT account_id
                                                          FROM   account_location
                                                          WHERE  location = "A"))) AS b
              ON a.account_id = b.account_id 

我有 4 张 table 。 Accounts，account_location 包含映射到位置的账户列表，tax 包含所有税项，account_tax 包含适用于每个账户的所有税项的映射。代码工作正常，但是可以做得更快吗？

Answer 1

如果我没记错的话，这个查询应该做同样的事情:

select
  a.account_id, a.account, t.tax, t.rate
from 
  accounts a
  inner join account_location al 
    on al.account_id = a.account_id 
    and al.location = 'A'
  left join account_tax at
    on at.account_id = a.account_id
  left join tax t on t.tax_id = at.tax_id

我认为更高效的连接会更快(MySQL 不适合子选择，尤其是在那些连接条件下。另外，我认为它更具可读性。