In Oracle how to join two tables one of the referred table column having with null values?

Question

Let me explain the question in detail. I have 4 tables A,B,C,D. A,B,C are the tables having master data in which no row or column is null. But D is a mapping table using master tables A,B,C but having null values in some rows.

Now I wanted to join the 4 tables and display the output.

   An Example:

CREATE TABLE A
  (aid NUMBER,aval VARCHAR2(1)
  );
CREATE TABLE B
  (bid NUMBER,bval VARCHAR2(1)
  );
CREATE TABLE C
  (cid NUMBER,cval VARCHAR2(1)
  );
CREATE TABLE D
  (
    did NUMBER,
    aid NUMBER,
    bid NUMBER,
    cid NUMBER,
    CONSTRAINT fk1 FOREIGN KEY (aid) REFERENCES A(aid),
    CONSTRAINT fk2 FOREIGN KEY (bid) REFERENCES B(bid),
    CONSTRAINT fk3 FOREIGN KEY (cid) REFERENCES C(cid)
  );
INSERT INTO A VALUES
  (1,'s'
  );
INSERT INTO A VALUES
  (2,'p'
  );
INSERT INTO B VALUES
  (1,'K'
  );
INSERT INTO B VALUES
  (2,'L'
  );
INSERT INTO C VALUES
  (1,'M'
  );
INSERT INTO C VALUES
  (2,'N'
  );
INSERT INTO D VALUES
  (1,1,1,1
  );
INSERT INTO D VALUES
  (1,2,NULL,NULL
  );
INSERT INTO D VALUES
  (1,1,2,NULL
  );
INSERT INTO D VALUES
  (1,1,NULL,2
  );

Now i want the output to be like:

s  K      M
p  null   null
s  L      null
s  null   N

I explained the edited question in the comment below. The exact query looks like:

SELECT DISTINCT A.NM,
  B.NM,
  G.NM
FROM w H
INNER JOIN A
ON H.id   = A.ID
AND H.FLG ='Y'
INNER JOIN F
ON F.ID  = H.ID
AND F.ID =1
INNER JOIN E
ON F.ID        = E.ID
AND E.BYu NOT IN ('7','1')
AND E.Dex      = 'A'
LEFT JOIN B
ON B.ID= E.ID
LEFT JOIN G
ON G.ID = E.ID
ORDER BY 1;

D table in my first post was the all the code or selection combined till left join of B. This query is working but I am not sure this is the best practise or not. I want to know how to filter a left side table in a left outer join before joining the tables( I don't want to filter it after using the where clause as it reduces the performance). I also know that right side table in left outer join is filtered using "and clause in the join itself.

Any help is very much appreciated.

Answer 1

This appears to be a simple outer join...

  select aval,bval,cval from d
  left join a on a.aid = d.aid
  left join b on b.bid = d.bid
  left join C on c.cid = d.cid

Maybe you need to read up on how joins work. Here's a great article

and query with results.