Left Join not returning the missing information - SQL Oracle

oracle left join
left outer join
left outer join in oracle with multiple conditions
oracle left outer join multiple tables
oracle join syntax
full outer join oracle
how to optimize sql query with multiple left joins in oracle
oracle sql join multiple tables with conditions

One is for cash and the other is for the stage with the following structure

Cash

FileID     Cash   Date
1           50   03.04.2017
2           100  08.07.2015
3           70   14.09.2018

Stage

FileID      Stage           Date_of_stage
1           Finished       06.04.2016
1           In Process     08.07.2015
2           Complication   17.08.2018
2           In Process     14.03.2018

Though my tables have many more rows. So I am joining these 2 tables coz I wanna group the cash by the stage using this select:

select fileID,  date, cash, max(date_of_stage) as max_date
from (select c.fileID, c.date, c.cash, s.stage, s.date_of_stage  
      from cash c
      inner join stage s
      on c.fileID=s.fileID
      and s.date_of_stage < c.date
      ) x
group by fileID, date, cash 

I only need max(date_of_stage) because it makes logically sense for our report and this isn't part of the question anyway.

The thing is: when I compare the total cash from cash table and the above select I get a little bit less total sum from the above select than from the cash table ( 7 Million from cash and 6.9 Milliom from the above select). Now I am trying to identify the missing records using a left join:

select *
from (select fileID, date, cash
  from cash) x

left join 

(select fileID,  date, cash, max(date_of_stage) as max_date
from (select c.fileID, c.date, c.cash, s.stage, s.date_of_stage  
      from cash c
      inner join stage s
      on c.fileID=s.fileID
      and s.date_of_stage < c.date
      ) 
group by fileID, date, cash ) y
on x.fileID=y.fileID
and x.date=y.date
and x.cash=y.cash
where y.fileID is null

But this left join doesn't give out anything so I can't identify and examine the missing records. Any tips what to do?

try like below by chaning the left table

 select x.*

 (select fileID,  date, cash, max(date_of_stage) as max_date
    from (select c.fileID, c.date, c.cash, s.stage, s.date_of_stage  
          from cash c
          inner join stage s
          on c.fileID=s.fileID
          and s.date_of_stage < c.date
          ) 
    group by fileID, date, cash ) x left join 

     (select fileID, date, cash
      from cash) y    


    on x.fileID=y.fileID
    and x.date=y.date
    and x.cash=y.cash
    where y.fileID is null

How to JOIN two table to get missing rows in the second table , Use your existing query to get the opposite of the list you want. That list can then be checked against via NOT IN to get the desired list. SELECT * FROM� As expected, by using a LEFT OUTER JOIN instead of the previous INNER JOIN, we’re getting the best of both worlds: We’re not skipping any books records (such as Hamlet) simply because the language_id value is null for that record, yet for all records where language_id exists, we get the nicely formatted language name obtained from our languages table.

I think all you need is to do the left outer join in the original query, rather than an inner join, e.g.:

WITH cash AS (SELECT 1 fileid, 50 cash, to_date('03/04/2017', 'dd/mm/yyyy') dt FROM dual UNION ALL
              SELECT 2 fileid, 100 cash, to_date('08/07/2015', 'dd/mm/yyyy') dt FROM dual UNION ALL
              SELECT 3 fileid, 70 cash, to_date('14/09/2018', 'dd/mm/yyyy') dt FROM dual),
    stage AS (SELECT 1 fileid, 'Finished' stage, to_date('06/04/2016', 'dd/mm/yyyy') date_of_stage FROM dual UNION ALL
              SELECT 1 fileid, 'In Process' stage, to_date('08/07/2015', 'dd/mm/yyyy') date_of_stage FROM dual UNION ALL
              SELECT 2 fileid, 'Complication' stage, to_date('17/08/2018', 'dd/mm/yyyy') date_of_stage FROM dual UNION ALL
              SELECT 2 fileid, 'In Process' stage, to_date('14/03/2018', 'dd/mm/yyyy') date_of_stage FROM dual)
SELECT c.fileid,
       c.dt,
       c.cash,
       MAX(s.date_of_stage) max_date
FROM   cash c
       LEFT OUTER JOIN stage s ON c.fileid = s.fileid AND s.date_of_stage < c.dt
GROUP BY c.fileid,
         c.dt,
         c.cash;

    FILEID DT                CASH MAX_DATE
---------- ----------- ---------- -----------
         1 03/04/2017          50 06/04/2016
         2 08/07/2015         100 
         3 14/09/2018          70 

Ask TOM "Left join with filter condition is not working as ", Developers and DBAs get help from Oracle experts on: Left join with filter Left join should not reduce rows returned by query, but it does. Predicate Information: ---------------------- 1 - filter(NULL IS NOT NULL) 2 - access("T1". Do you remember your presentation called "SQL magic" in Riga conference? In case a row in the T1 table does not have any matching row in the T2 table, the query combines column values from the row in the T1 table with a NULL value for each column in the right table that appears in the SELECT clause. In other words, a left join returns all rows from the left table and matching rows from the right table.

It is strange. With the data you provided your "checking" query works fine and shows two rows. Here is the dbfiddle demo.

Anyway if you need only to attach max date from second table use simple subquery:

select fileID,  date_, cash, 
       (select max(date_of_stage) 
          from stage s
          where fileid = c.fileid and s.date_of_stage < c.date_) as max_date
  from cash c

demo

Joins, Database SQL Reference The columns in the join conditions need not also appear in the select list. See Oracle Database Application Developer's Guide - Large Objects for more information. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which� The RIGHT JOIN: Matching records plus orphans from the right When you execute a query using the RIGHT JOIN syntax, SQL does two things: It returns all of the records from both tables that contain

NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: Oracle at , This article compares efficiency of these methods in Oracle. Which method is best to select values present in one table but missing in another one? First, Oracle's optimizer, unlike SQL Server's one, is smart enough to see an Rows not found in the hash table are returned; those that are found are not. Left Join not returning the missing information - SQL Oracle. sql oracle left-join. share | improve this question. edited Apr 5 at 8:37. a_horse_with_no_name.

NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server at , This means that LEFT JOIN / IS NULL is guaranteed to return at most one row NOT EXISTS , therefore, will return TRUE only if no row satisfying the and NOT IN predicates are the best way to search for missing values, I am a little bit skeptic about it because in Oracle using not in would kill everything. This execution plan is quite interesting. First, Oracle's optimizer, unlike SQL Server's one, is smart enough to see an opportunity to use ANTI JOIN for such a query. Since all rows from t_left should be examined, Oracle decided to use a HASH ANTI JOIN to do this: a hash table is built over the values from t_right, eliminating duplicates, and every row from t_left is searched for in the hash

Left Join doesn't return all recodrs in left table – SQLServerCentral, Left Join doesn't return all recodrs in left table – Learn more on the 2008 � T- SQL (SS2K8); Left Join doesn't return all recodrs in left table I need to keep all the records in the left table even though there are no matches in the right one. from 01 to 10 and tab2 ID ranges from 01 to 10 with missing IDs. Example 2-- Join the EMPLOYEE and DEPARTMENT tables, -- select the employee number (EMPNO), -- employee surname (LASTNAME), -- department number (WORKDEPT in the EMPLOYEE table -- and DEPTNO in the DEPARTMENT table) -- and department name (DEPTNAME) -- of all employees who were born (BIRTHDATE) earlier than 1930 SELECT EMPNO, LASTNAME, WORKDEPT, DEPTNAME FROM SAMP.EMPLOYEE LEFT OUTER JOIN SAMP

Comments
  • Please in code questions give a minimal reproducible example--cut & paste & runnable code plus desired output plus clear specification & explanation. Minimal means adding minimal problem code to minimal working code. So give minimal code that you show does what you expect & minimal code with the first place you go wrong. (Debugging fundamental.)