(Beginner)

Q1) How to exclude null value from Oracle Inner Join Statement Result. i.e. to display only that row which has all the values (i.e. no null data in any of the attribute)

Q2) How to show ONLY those record/s who has missing/null data in any of the attributes in any of the four table

Below is the dummy example in oracle sql

Created 4 Tables i.e., name , details , social , address

`create table name
(
id number,
firstname varchar2(20),
lastname varchar2(20)
);

create table details
(
id number,
dob_month varchar2(20),
dob_day varchar2(20),
dob_year varchar2(20)
);

create table social
(
id number,
ssn varchar2(20),
telephone number
);

create table address
(
id number,
address varchar2(20)
);
`

Now insert dummy data into the above tables

`insert into name values (1, 'Will' , 'Smith');
insert into name values (2, 'Barry' , 'White');
insert into name values (3, 'Tom' , 'Jones');
insert into name values (4, 'Rod' , 'Stewart');
insert into name values (5, 'Elvis' , 'Presley');

insert into details values (1,'May',31,null);
insert into details values (2,'August',22,1980);
insert into details values (3,'October',null,1973);
insert into details values (4,'January',30,1980);
insert into details values (5,'March',11,1980);

insert into social values (1,'123-45-6789',null);
insert into social values (2,'222-45-5555',789456123);
insert into social values (3,'333-45-7777',888888888);
insert into social values (4,null,693456741);
insert into social values (5,'999-45-1111',null);

insert into address values (null, null);
insert into address values (2, '12th street');
insert into address values (null, null);
insert into address values (4, '14th Avenue');
insert into address values (5, null);`

Q1 Pictorial Explanation

For Q1,

I tried with below oracle sql query (both returns the same result) but not able to figure out the exact query which will exclude null value from Oracle Inner Join Statement Result and display only that row which has all the values (i.e. not null)

`select name.id, firstname, lastname, dob_month, dob_year, ssn, telephone, address
from name, details, social, address
where name.id=details.id
and details.id=social.id
and social.id=address.id;


select name.id, firstname, lastname, dob_month, dob_year, ssn, telephone, address
from name join details on name.id=details.id
join social on details.id=social.id
join address on social.id=address.id;`

For Q2, I am looking for sample Query i.e. How to show ONLY those record/s who has missing/null data in any of the attributes in any of the four table

CodePudding user response：

For the first question, I think you are looking for this:

select name.id, firstname, lastname, dob_month, dob_year, ssn, telephone, address
from name 
   join details on name.id=details.id
   join social on details.id=social.id
   join address on social.id=address.id
where dob_year is not null
   and dob_day is not null
   and telephone is not null
   and ssn is not null
   and address is not null;

Or a bit shorter

...
where COALESCE(dob_year, dob_day, telephone, ssn, address) is not null;

For Q2 it would be this

select *
from address
where id is null
   or address is null;

Some more notes:

It is a poor design to store date parts and then even localized strings. You should never do that. In your case it should be

create table details
(
id number,
dob_date DATE
);

Then next question, why do you create four tables? Maybe you learned at school about database normalization but you have taken too literally. Can a person have more than one SSN/telephone? If not, then add these columns to name table (and maybe rename it to person) instead of a separate table. The same question applies to table address.