SQL: how to limit a join on the first found row?

Question

How to make a join between two tables but limiting to the first row that meets the join condition ?

In this simple example, I would like to get for every row in table_A the first row from table_B that satisfies the condition :

select table_A.id, table_A.name, table_B.city  from table_A join table_B  on table_A.id = table_B.id2 where ..  table_A (id, name) 1, John 2, Marc  table_B (id2, city) 1, New York 1, Toronto 2, Boston  The output would be: 1, John, New York 2, Marc, Boston 

May be Oracle provides such a function (performance is a concern).

Answer 1

The key word here is FIRST. You can use analytic function FIRST_VALUE or aggregate construct FIRST.
For FIRST or LAST the performance is never worse and frequently better than the equivalent FIRST_VALUE or LAST_VALUE construct because we don't have a superfluous window sort and as a consequence a lower execution cost:

select table_A.id, table_A.name, firstFromB.city  from table_A  join (     select table_B.id2, max(table_B.city) keep (dense_rank first order by table_B.city) city     from table_b     group by table_B.id2     ) firstFromB on firstFromB.id2 = table_A.id  where 1=1 /* some conditions here */ ; 

Since 12c introduced operator LATERAL, as well as CROSS/OUTER APPLY joins, make it possible to use a correlated subquery on right side of JOIN clause:

select table_A.id, table_A.name, firstFromB.city  from table_A  cross apply (     select max(table_B.city) keep (dense_rank first order by table_B.city) city     from table_b     where table_B.id2 = table_A.id      ) firstFromB where 1=1 /* some conditions here */ ; 

Answer 2

If you want just single value a scalar subquery can be used:

SELECT     id, name, (SELECT city FROM table_B WHERE id2 = table_A.id AND ROWNUM = 1) city FROM     table_A