CODEWITHSUNDEEP
mysqlsql
neversaint
neversaint

Reputation: 64074

SQL join by limiting one table

I have two tables that looks like this

TABLE1
-------------------------------
Col1    Col2   Col3
------------------------------
foo     1       83433
bar     2       33333



TABLE2
-------------------------------
Col1    Col2   Col3       Col4
------------------------------
ksd     a       xxxx     1
asx     b       yyyy     2
....
1 million rows.

What I'm trying to do is to join Col2 of Table1 with Col4 of Table2 and but before joining them I'd like to limit only top 1000 of Table2. How can I achieve that?

Here is my current attempt that doesn't work

SELECT DISTINCT Col1, Col2, Col3
FROM TABLE1 T1
JOIN TABLE2 T2
ON T1.Col2 = T2.Col4 (LIMIT 1000)

Upvotes: 1

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271051

Do it in a subquery:

SELECT DISTINCT Col1, Col2, Col3
FROM TABLE1 T1 JOIN
     (select T2.*
      from TABLE2 T2
      limit 1000
     ) T2
ON T1.Col2 = T2.Col4;

Note that limit without an order by does not guarantee which rows are returned. Hence, I would expect an order by in this case.

Upvotes: 4

Related Questions