CODEWITHSUNDEEP
sqloracle-databasejoinunionoracle12c
Vinoth Karthick
Vinoth Karthick

Reputation: 1087

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

Upvotes: 0

Views: 259

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

use join and union all and never use coma separated join

  select coalesce(a.name,b.name) as name from 
  Secondtable b  join FirstTable a
  a.name=b.name or a.number=b.number 
  union all
  select name from Secondtable where name is null

Upvotes: 0

Vasyl Moskalov
Vasyl Moskalov

Reputation: 4650

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)

Upvotes: 1

Related Questions