Limit number of rows fetching in a left outer join in Oracle

Question

I'm going to create a data model in oracle fusion applications. I need to create column End_date based on two tables in the query. So I used two methods.

1. Using a subquery:

SELECT *
  FROM (SELECT projects_A.end_date
          FROM projects_A, projects_B
         WHERE projects_A.p_id = projects_B.p_id
           AND rownum = 1)

2. Using a LEFT OUTER JOIN:

SELECT projects_A.end_date
  FROM projects_A
  LEFT JOIN projects_B
    ON projects_A.p_id = projects_B.p_id
 WHERE rownum = 1

Here when I used a subquery, the query returns the results as expected. But when I use left outer join with WHERE rownum = 1 the result is zero. Without WHERE rownum = 1 it retrieves all the results. But I want only the first result. So how can I do that using left outer join? Thank you.

Answer 1

Looks like you want to bring a non-null end_date value(So, add NULLS LAST), but the sorting order is not determined yet(you might add a DESC to the end of the ORDER BY clause depending on this fact ), and use FETCH clause(the DB version is 12c+ as understood from the comment) with ONLY option to exclude ties as yo want to bring only single row.

So, you can use the following query :

SELECT A.end_date
  FROM projects_A A
  LEFT JOIN projects_B B
    ON A.p_id = B.p_id
 ORDER BY end_date NULLS LAST
 FETCH FIRST 1 ROW ONLY