SQL - Getting data from three unrelated tables in one query

Trying to understand the 9th tip in http://www.orafaq.com/papers/sqltiptr.pdf

Combining three separate queries into one:

select name from emp where emp_no=1234;
select name from dpt where dot_no=10;
select name from cat where emp_no='RD';


select e.name, d.name, c.name
from cat c, dpt d, emp e, dual x
where NVL('x',X.DUMMY)=NVL('X', E.ROWId (+))
and NVL('x',X.DUMMY)=NVL('X', D.ROWId (+))
and NVL('x',X.DUMMY)=NVL('X', C.ROWId (+))
and e.emp_no(+)=1234
and d.dept_no(+)=10
and c.cat_type(+)='RD'

Why do we need these null value comparison in the query?

 NVL('x',X.DUMMY)=NVL('X', E.ROWId (+))开发者_运维知识库
and NVL('x',X.DUMMY)=NVL('X', D.ROWId (+))
and NVL('x',X.DUMMY)=NVL('X', C.ROWId (+))

---

select name from emp where emp_no=1234
UNION ALL
select name from dpt where dot_no=10
UNION ALL
select name from cat where emp_no='RD'

or if you want all of them in one row:

SELECT (select name from emp where emp_no=1234) a,
       (select name from dpt where dot_no=10) b,
       (select name from cat where emp_no='RD') c
  FROM DUAL

---

It's a pretty esoteric trick -- the DUAL table has one row in it, and the ROWIDs in your actual tables are also unique, so using NVL('X'.. is a way to trick the JOIN into 'relating' the unrelated records. Then the rest of your WHERE clause applies the actual constraints that you're after.

I think zerkms's approach is more straightforward, but since you asked specifically about that tip ... HTH