Search code examples
sqlpostgresqljoinlimit

Not able to join two tables with limit in Postgres

I have table A with col1,col2,col3 and Table B col1.

I want to join both tables using the limit

I want some thing like 

select a.col1,a.col2,a.col3,b.col1 
from tableA a, tableB b limit 5 and a.col1 between 1 AND 10;

So I have 10 records in table b and 10 in table a. I should get total of 50 records by limiting only 5 records from table b

Solution

  • You need a derived table (aka "sub-query") for that. In the derived table, you can limit the number of rows. 

    select a.col1, a.col2, b.col3, b.col1
from tablea a
  join (
    select b.col3, b.col1
    from tableb
    limit 5 -- makes no sense without an ORDER BY
  ) b on b.some_column = a.some_column --<< you need a join condition
where a.col1 between 1 and 10;

    Note that using LIMIT without an ORDER BY usually makes no sense.