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

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.

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)

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.

CodePudding user response：

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