Joining two tables using a double-valued column, master -lookup tables

Question

I have a table (x) that has 9 million rows, and one of its columns is cosub, which repeats repeated values, there is another table (y) that has related details for cosub, it has 59 thousand rows and additional columns that provide cosub related details (acting like a lookup table) how can I join two tables by querying 9 million rows and picking out more information about cosub from table (y). Example:

Table x table y 
id cosub cosub div 

1 AB 6
2 BA 5
3 CC 7
4 AA 5
5 BB 6
6 CA 5
.....................

the query result should look like this (select all 9 million rows from table x)

1 A 5
2 B 6
3 C 7
4 A 5
5 B 6
6 C 7

Answer 1

SELECT DISTINCT X.id, X.cosub, Y.div
FROM X
LEFT OUTER JOIN Y ON Y.cosub = X.cosub
-- WHERE  xxxx here for optional where condition
-- ORDER BY xxxx  here for optional ordering clause

I am not 100% sure that you need DISTINCT (it would be nice to avoid it), it depends on whether the small table has duplicates. The text of the question, apparently, does not imply such duplicates, but then dups is given in the example ...

It should also be remembered that if table Y has several div values ​​for a given cosub (i.e., several records with conflicting div values ​​for a given cosub), the above query will display several rows in the result list, one for different ( but repeating the data from table X).

, LEFT OUTER JOIN, X ( , Y), , X cosub, Y. JOIN, EXCLUDING (.. X, cosub, Y)

Answer 2

:

   SELECT DISTINCT
          x.id,  
          x.cosub,
          y.div
     FROM TABLE_x x
LEFT JOIN (SELECT t.cosub,
                  t.div,
                  --other columns
             FROM TABLE_Y t) y ON y.cosub = x.cosub

, , TABLE_Y, div/etc .