使用哪种类型的JOIN [英] What type of JOIN to use

问题描述

我将使用哪种类型的JOIN来使table1和table2仅匹配一次.例如，我有table1(40行)和table2(10000行).但是当我在table1.LocationArea = table2.Location

What type of JOIN would I use to get table1 and table2 to be matched only once. For example, I have table1 (40 rows) and table2 (10000 rows). But I get table1 repeated over and over when I use a join on table1.LocationArea = table2.Location

What I get:                         What I wish I could get:
t1.LocationArea,t2.Location         t1.LocationArea,t2.Location
---------------------------         ---------------------------
az,az                               az,az
az,az                               null,az
ca,ca                               ca,ca
il,il                               il,il
tx,tx                               tx,tx
tx,tx                               null,tx
az,az                               null,az
                                    null,il
                                    null,ca

我希望在查询中得到10000条记录.

I wish to end up with 10000 records in the query.

我尝试了inner join，left，并且我正在使用不支持外部联接的ZOHO报告.

I have tried inner join, left, and I am using ZOHO reports which does not support outer join's.

SELECT "table1"."LocationArea", "Location" 
FROM "table2"
left join "table1" on  "Location" = "table1"."LocationArea"

推荐答案

很显然，两个连接列都有重复的值.代替笛卡尔积，会产生一个[INNER] JOIN，您希望每一行都是仅使用一次.您可以通过为每个重复项添加行号(rn)并另外加入rn来实现.

Obviously, you have duplicate values for both of the joining columns. Instead of the Cartesian product an [INNER] JOIN would produce for this, you want each row to be used only once. You can achieve this by adding a row number (rn) per duplicate and join on rn additionally.

对于每个表，相同值的重复项可以比另一个表更多或更少，除非您有其他限制(例如FK约束)-但是您的问题没有任何内容.要保留所有行，请使用 LEFT [OUTER] JOIN (具有40行)-并从table1中排除可能过多的行.

Each table can have more or fewer dupes for the same value than the other unless you have additional restrictions in place (like a FK constraint) - but there is nothing in your question. To keep all rows one would use a FULL [OUTER] JOIN. But you want to keep 10000 records in the result, which is the cardinality of table2. So it must be a LEFT [OUTER] JOIN on table1 (with 40 rows) - and exclude possible excessive rows from table1.

SELECT t1."LocationArea", t2."Location"
FROM  (
   SELECT "Location"
        , row_number() OVER (PARTITION BY "Location") AS rn
   FROM   table2
   ) t2
LEFT JOIN (
   SELECT "LocationArea"
        , row_number() OVER (PARTITION BY "LocationArea") AS rn
   FROM   table1
   ) t1 ON t1."LocationArea" = t2."Location"
       AND t1.rn = t2.rn;

适用于Postgres或SQL Server. MySQL不支持窗口函数，您需要一个替代方法:

Works for Postgres or SQL Server. MySQL doesn't support window functions, you would need a substitute:

• SQL SELECT的最后一项没有限制

要清楚:LEFT JOIN只是LEFT OUTER JOIN的简写，因此您已经在使用外部联接.您的陈述是一个误解:

To be clear: LEFT JOIN is just shorthand for LEFT OUTER JOIN, so you are already using an outer join. Your statement is a misunderstanding:

我正在使用不支持外部联接的ZOHO报告.

I am using ZOHO reports which does not support outer join's.

这篇关于使用哪种类型的JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！