mysql - What type of JOIN to use

Question

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

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

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"

Answer 1

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.

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;

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

• SQL SELECT last entry without limiting

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:

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