SQL: how to limit the connection in the first row found?

Question

How to make a join between two tables, but restricting the first row that matches the join condition?

In this simple example, I would like to get for each row of 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 

Maybe Oracle provides such a feature (performance is a problem).

Answer 1

If you need only one value, you can use the scalar subquery:

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

Answer 2

The keyword here is FIRST. You can use the FIRST_VALUE analytic function or the FIRST aggregate construct.
For FIRST or LAST performance is never worse and often better than the equivalent FIRST_VALUE or LAST_VALUE , because we do not have a redundant window view and, as a result, 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 the LATERAL operator, as well as CROSS/OUTER APPLY , you can use the correlated subquery on the right side of the 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 3

 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 .. 

Answer 4

Oracle12c finally has a new cross / outer apply statement that allows you to request without any workarounds.

The following is an example that looks at vocabulary representations for only one of the (possibly) many objects belonging to those users whose name begins 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 ) 

In Oracle 11g and previous versions, you should only use workarounds that usually completely look at the second table based on the identifiers of the second table to get the same results, but for testing the navel you can enable the side statement (also available on 12c without the need to add new materials ) and use this other

 -- 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 5

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 ... 

results

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