Best practices of Oracle LEFT OUTER JOIN

Question

I am new to sql, i use Sql Developer (Oracle db). When I need to select some data with null values I write one of these selects:

1)

SELECT i.number
      ,i.amount
      ,(SELECT value FROM ATTRIBUTES a
        WHERE a.parameter = 'aaa' AND a.item_nr = i.number) AS atr_value
FROM ITEMS i

2)

SELECT i.number
      ,i.amount
      ,a.value as atr_value
FROM ITEMS i
left outer join ATTRIBUTES a
    on a.parameter = 'aaa'
   and a.item_nr = i.number

Questions:

1. What is difference?
2. How first approach is called (how can I google it)? Where can I read about it?
3. Which one should I use further (what is best practices), maybe there is better way to select same data?

Axample of tables:

Answer 1

Your two queries are not exactly the same. If you have multiple matches in the second table, then the first generates an error and the second generates multiple rows.

Which is better? As a general rule, the LEFT JOIN method (the second method) is considered the better practice than the correlated subquery (the first method). Oracle has a pretty good optimizer and it offers many ways of optimizing joins. I also think Oracle can use JOIN algorithms for the correlated subqueries (not all databases are so smart). And, with the right indexes, the two forms probably have very similar performance.

There are situations where correlated subqueries have better performance than the equivalent JOIN construct. For this example, though, I would expect the performance to be similar.