限制左联接 [英] Restricting a LEFT JOIN

问题描述

我有一个表，我们称它为"a"，它在涉及许多表的视图中用在左联接中.但是，我只想返回"a"的行(如果它们也与另一个表"b"联接).所以现有的代码看起来像

I have a table, let's call it "a" that is used in a left join in a view that involves a lot of tables. However, I only want to return rows of "a" if they also join with another table "b". So the existing code looks like

SELECT ....
FROM main ...
...
LEFT JOIN a ON (main.col2 = a.col2)

，但是它返回太多行，特别是其中a在b中不匹配的行.我尝试过

but it's returning too many rows, specifically ones where a doesn't have a match in b. I tried

SELECT ...
FROM main ...
...
LEFT JOIN (
   SELECT a.col1, a.col2
   FROM a
   JOIN b ON (a.col3 = b.col3)) ON (a.col2 = main.col2)

这给了我正确的结果，但是不幸的是，"EXPLAIN PLAN"告诉我们，这样做会导致强制对a和b进行全表扫描，这使事情变得很慢.我的一位同事在b上建议了另一个LEFT JOIN，但这是行不通的，因为它在出现b时给了我b行，但并没有停止从b中没有匹配项的a行返回.

which gives me the correct results but unfortunately "EXPLAIN PLAN" tells that doing it this way ends up forcing a full table scan of both a and b, which is making things quite slow. One of my co-workers suggested another LEFT JOIN on b, but that doesn't work because it gives me the b row when it's present, but doesn't stop returning the rows from a that don't have a match in b.

有什么方法可以将main.col2条件放入子SELECT中，从而摆脱全表扫描吗?还是通过其他方式来做我想做的事?

Is there any way to put the main.col2 condition in the sub-SELECT, which would get rid of the full table scans? Or some other way to do what I want?

推荐答案

SELECT ...
FROM ....
LEFT JOIN ( a INNER JOIN b ON .... ) ON ....

这篇关于限制左联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！