MySQL联接+子查询混淆

Question

我想问一些关于联接的问题

我的想法，我相信这过去对我有用，但似乎我错过了一些东西：

此查询将生成来自两个表的数据，其中client_id为1-工作正常。

/* gets data for one client_id*/

approach 1A

SELECT * 
FROM clients  LEFT JOIN client_modules 
                ON client_modules.client_id = clients.client_id
WHERE clients.client_id = 1;

现在，我认为这个查询应该返回相同的结果，因为我已经使用联接中的子查询来首先过滤结果。已经从client_id 1的模块中获取数据，但出于某种原因，它给出了最终结果中的其他client_ids数据。

/* gets data for one client_id sub-query approach*/

approach 2A

SELECT * 
FROM clients LEFT JOIN (SELECT client_id, module_name 
                        FROM client_modules 
                        WHERE client_modules.client_id = 1) 
             AS client_moduless ON client_moduless.client_id = clients.client_id;



/* gets data for all client_ids */

approach 1B

SELECT * FROM clients
LEFT JOIN client_modules ON client_modules.client_id = clients.client_id;



/* gets data for all client_ids*/

approach 2B

SELECT * 
FROM clients LEFT JOIN (SELECT client_id, module_name 
                        FROM client_modules) AS client_moduless
              ON client_moduless.client_id = clients.client_id;

问题：

1)在xA and xB中使用大量数据时，哪种方法更有效

2)为什么第二种方法2A会给出来自client_ids other then 1的结果，尽管在join中分别运行子查询很好。

3)如果不使用where子句，2B中的子查询会对父记录执行吗？

4)如果我将1A查询更改为

SELECT * FROM clients
JOIN client_modules ON client_modules.client_id = clients.client_id AND client_modules.client_id = 1

刚刚删除了client表上的where子句，并在子表的join子句中添加了这是有效的还是where子句？

问候

Answer 1

不是他们中的。在我看来，我认为您不应该使用left join。您应该使用join。像老虎钳：

SELECT * FROM clients
JOIN client_modules ON client_modules.client_id = clients.client_id
WHERE clients.client_id = 1;

这是更严格的前进，你限制在where语句中的LEFT JOIN，所以它有同样的效果。

SELECT * 
FROM clients LEFT JOIN (SELECT client_id, module_name 
                        FROM client_modules 
                        WHERE client_modules.client_id = 1) 
             AS client_moduless ON client_moduless.client_id = clients.client_id;

使用此查询，您将返回客户端表中的所有行，对于与client_moduless.client_id = clients.client_id匹配的行，将有一个该表。但是这不是一个限制联接，而是一个左连接，这意味着当没有匹配值时，它返回null。子查询将针对每一行运行。为了达到同样的效果，你可以这样做：

SELECT * 
FROM clients JOIN (SELECT client_id, module_name 
                        FROM client_modules 
                        WHERE client_modules.client_id = 1) 
             AS client_moduless ON client_moduless.client_id = clients.client_id;

现在，这将限制clients表，您将只获得client_modules中匹配的值。但我看不出这样做有什么意义。我会选择海峡前方的join。

这也取决于你对什么感兴趣。如果您只是在客户端表的列中。然后你就可以这样做：

SELECT * 
FROM clients
WHERE EXISTS
(
    SELECT 
        NULL
    FROM
        client_modules=1
        AND client_moduless.client_id = clients.client_id
)

因此，如果您希望clients表和client_modules中的所有列都与联接一起使用。否则，请与exists一起使用

编辑

我想这个：

SELECT * FROM clients
JOIN client_modules ON client_modules.client_id = clients.client_id 
AND client_modules.client_id = 1

这是：

SELECT * FROM clients
JOIN client_modules ON client_modules.client_id = clients.client_id
WHERE client_modules.client_id = 1

是一样的。这将导致相同的查询计划。

Answer 2

2a吸引额外的客户端，因为您已经使用了一个左连接-即所有记录从左侧，但只有相关的记录从右侧。你应该使用“完全连接”或“加入”，而不是“左加入”。

不确定其他的问题，但我更喜欢1B而不是分选。

编辑以添加-要了解左联接发生了什么，考虑它的许多用途之一-客户端在client_modules中没有任何记录？

这是很容易写的：

SELECT * FROM clients WHERE clientid NOT IN (select distinct(clientid) FROM client_modules)

但是，编写以下内容可能更有效：

SELECT * FROM clients
     LEFT JOIN  client_modules ON clients.clientid = client_modules.clientid
WHERE client_modules.clientid IS NULL

(即仅显示来自客户端的无法连接到client_module行的记录)