Search code examples
sqloracle-databasejoinunionoracle12c

Query to Fetch Matching Records between 2 tables and not matching Null records from Second Tabl2


I've 2 tables as below. I need to fetch the matching names between the 2 tables and also the unmatching NULL Records in the 2second table. The Column Number (key Vlaues) should be in Join . I used the below query. But is there any way to get rid of Union, or is there any other better way of writing the query than below?

  FirstTable
    Number|Name
      1   |Oracle
      2   |SAP
      3   |IQ
      4   |HANA
      5   |Oracle

    Second Table
    Number|Name
      1   |Oracle
      2   |
      3   |Sybase
      4   |HANA
      5   |
    Query:
    -------
    select a.name from FirstTable a, Secondtable b 
    where a.number=b.number
    and a.name=b.name
    and b.name is not null

    union

    select a.name from FirstTable a, Secondtable b 
    where a.number=b.number
    and b.name is  null

    Required Output 

    Name
    Oracle
    SAP
    HANA

Solution

  • As for me for this request is more convenient something like

    select distinct a.name from FirstTable a,SecondTable b
    where a.number=b.number and a.name=nvl(b.name,a.name)