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

On Oracle12c there finally is the new cross/outer apply operator that will allow what you asked for without any workaround.

the following is an example that looks on dictionary views for just one of the (probably)many objects owned by those users having their name starting with 'SYS':

select *
from (
        select USERNAME
        from ALL_USERS
        where USERNAME like 'SYS%'
    ) U
    cross apply (
        select OBJECT_NAME
        from ALL_OBJECTS O
        where O.OWNER = U.USERNAME
            and ROWNUM = 1
    )

On Oracle 11g and prior versions you should only use workarounds that generally full scan the second table based on IDs of the second table to get the same results, but for testing puposes you may enable the lateral operator (also available on 12c without need of enabling new stuff) and use this other one

-- Enables some new features
alter session set events '22829 trace name context forever';

select *
from (
        select USERNAME
        from ALL_USERS
        where USERNAME like 'SYS%'
    ) U,
    lateral (
        select OBJECT_NAME
        from ALL_OBJECTS O
        where O.OWNER = U.USERNAME
            and ROWNUM = 1
    );

Answer 2

Query:

SELECT a.id,
       a.name,
       b.city
FROM   table_A a
       INNER JOIN
       ( SELECT id2,
                city
         FROM   (
           SELECT id2,
                  city,
                  ROW_NUMBER() OVER ( PARTITION BY id2 ORDER BY NULL ) rn
           FROM   Table_B
         )
         WHERE rn = 1
       ) b
       ON ( a.id = b.id2 )
--WHERE  ...

Outputs:

        ID NAME CITY   
---------- ---- --------
         1 John New York 
         2 Marc Boston   

Answer 3

This solution uses the whole table, like in a regular join, but limits to the first row. I am posting this because for me the other solutions were not sufficient because they use one field only, or they have performance issues with large tables. I am no expert at Oracle so if someone can improve this please do so, I will be happy to use your version.

select *
from tableA A
cross apply (
    select *
    from (
        select B.*, 
            ROW_NUMBER() OVER (
                -- replace this by your own partition/order statement
                partition by B.ITEM_ID order by B.DELIVERYDATE desc
            ) as ROW_NUM
        from tableB B
        where 
        A.ITEM_ID=B.ITEM_ID
    )
    where ROW_NUM=1
) B

Answer 4

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

Answer 5

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 6

select table_A.id, table_A.name,
FIRST_VALUE(table_B.city) IGNORE NULLS 
         OVER (PARTITION BY table_B.id2 ORDER BY table_B.city) AS "city"
from table_A join table_B 
on table_A.id = table_B.id2
where ..