优化SQL查询-连接4个表

I am trying to join 4 tables. Currently I've achieved it by doing this.

我想加入4张桌子。目前我已经做到了。

    SELECT columns
    FROM tableA
    LEFT OUTER JOIN tableB ON tableB.address_id = tableA.address_id
    INNER JOIN tableC ON  tableC.company_id = tableA.company_id AND tableC.client_id = ?
    UNION
    SELECT columns
    FROM tableA
    LEFT OUTER JOIN tableB ON tableB.address_id = tableA.gaddress_id
    INNER JOIN tableD ON tableD.company_id = tableA.company_id AND tableD.branch_id = ?

The structure of tableC and tableD is very similar. Let's say that tableC contains data for clients. And tableD contains data for client's branch. tableA are companies and tableB are addresses My goal is to get data from tableA that are joined to table B (All companies that has addresses) and all the data from tableD and also from tableC.

tableC和tableD的结构非常相似。假设tableC包含客户端数据。表中包含客户端分支的数据。表a是公司，表B是地址，我的目标是从表a获取数据，表B连接到表B(所有有地址的公司)以及表B和表B的所有数据。

This wroks nice, but I am afraid that is would be very slow.

这很好，但我担心它会很慢。

3 个解决方案

#1

I think you can trick it like this:

我认为你可以像这样欺骗它:

First UNION between C,D and only the join to the rest of the query, it should improve the query significantly :

C和D之间的第一个结合，并且只有连接到查询的其余部分，它应该显著地改进查询:

SELECT columns
FROM TableA
LEFT OUTER JOIN tableB ON tableB.address_id = tableA.address_id
INNER JOIN(SELECT Columns,'1' as ind_where FROM tableC
           UNION ALL
           SELECT Columns,'2' FROM TableD) joined_Table
 ON  (joined_Table.company_id = tableA.company_id AND joined_Table.New_Col_ID= ?)

The New_Col_ID -> just select both branch_id and client_id in the same column and alias it as New_Col_ID or what ever

New_Col_ID ->只是在同一列中选择了branch_id和client_id，并将其别名为New_Col_ID或其他任何内容。

In addition you can index the tables(if not exists yet) :

此外，您还可以对表进行索引(如果还不存在的话):

TableA(address_id,company_id)
TableB(address_id)
TableC(company_id,client_id)
TableD(company_id,branch_id)

#2

Why should that be slow? You select client adresses and branch addresses and show the complete result. That seems straight-forward.

为什么要这么慢?选择客户端地址和分支地址并显示完整的结果。这似乎很简单。

You join on IDs and this should be fast (as there should be indexes available accordingly). You may want to introduce composite indexes on

您在IDs上进行连接，这应该很快(因为应该有相应的索引可用)。您可能想在上面引入复合索引

create index idx_c on tableC(client_id, company_id)

and

和

create index idx_d on tableD(branch_id, company_id)

However: UNION is a lot of work for the DBMS, because it has to look for and eliminate duplicates. Can there even be any? Otherwise use UNION ALL.

然而:对于DBMS来说，UNION是一项繁重的工作，因为它必须查找和消除重复。甚至会有吗?否则使用UNION ALL。

#3

Try CTE so that you don't have to go through TableA and TableB twice for the union.

试试CTE，这样你就不用为union遍历表a和表b两次了。

; WITH TempTable (Column1, Column2, ...)
    AS (    SELECT columns
            FROM tableA
            LEFT OUTER JOIN tableB
                 ON tableB.address_id = tableA.gaddress_id
        )
SELECT Columns
FROM    TempTable
INNER JOIN tableC 
    ON  tableC.company_id = tableA.company_id AND tableC.client_id = ?
UNION
SELECT Columns
FROM    TempTable
INNER JOIN tableD ON tableD.company_id = tableA.company_id AND tableD.branch_id = ?

#1