Oracle: nested query with data set extension in FROM clause

Oracle (as well as MS SQL and some others) allows nested queries in FROM clause of the SELECT statement. Being combined with data set extension using UNION keyword, this can greatly simplify simplify complex table joins.

Let's imagine we have two simple tables:

PERSONS
id  name
1   John Smith
2   Mike Douglas

COMPUTERS
id  name       main_user_id
1   Computer1  1
2   Computer2  2
3   Computer3  NULL

Now we want to have a full list of computers with appropriate main user names.

SELECT c.name as "computer", p.name as "user"
FROM COMPUTERS c, PERSONS p 
WHERE c.main_user_id = p.id

> computer   user
> Computer1  John Smith
> Computer2  Mike Douglas


To get full list of computers we could use outher join:

SELECT c.name as "computer", p.name as "user"
FROM COMPUTERS c, PERSONS p 
WHERE c.main_user_id = p.id (+)

> computer   user
> Computer1  John Smith
> Computer2  Mike Douglas
> Computer3  NULL

Or, we can use a nested SELECT to extend PERSONS table by an "unknown" user in the context of this query:

SELECT c.name as "computer", p.name as "user"

FROM
  COMPUTERS c, 
  (SELECT *
    FROM PERSONS
    UNION
      SELECT 0 AS "id", 'unknown' AS "name"
      FROM DUAL) p 
WHERE c.main_user_id = p.id

> computer   user
> Computer1  John Smith
> Computer2  Mike Douglas
> Computer3  unknown

This is much more verbose way in comparison to simple outher join, but it gives us additional level of control on the input data and actually simplifies WHERE clause.