Why use left join at all

Question

I am looking at the following link Left Join

If left join uses all elements from table 1 and doesn't care about table 2, why not just write:

SELECT table1.column_name(s)
FROM table1

instead of writing

SELECT table1.column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

Answer 1

It isn't that one cares about the values in TABLE1 and, as you write, doesn't care about TABLE2. It is that one cares about the values in TABLE1 and also about the values in TABLE2 if they exist.

Suppose you want a report of employees and their managers. You wouldn't want to exclude the president of your company simply because he doesn't have a manager. So, you would write it as a LEFT OUTER JOIN.

SELECT e.employee_name, m.employee_name manager_name
FROM   employees e 
LEFT JOIN employees m ON m.employee_id = e.manager_id

That way, you will get a row with your president's name and a NULL manager name for the president. If you had written it as an INNER join, you wouldn't have gotten a row for the president at all.

Answer 2

A LEFT JOIN on another table without a WHERE clause is indeed quite unnessairy, when only columns from the first table are needed.

In fact, it could return more rows than expected.

For example:

First some sample data:

Table1:

Id  Text1    SomeKey
1   Tralala  10
2   Joehoe   20
3   Trololo  30

Table2:

Id  SomeKey Text2 
1   10      Blah
2   20      Bleh
3   20      Blergh

A query only on Table1 would return 3 rows.

But a query with a left join on SomeKey would return 4 rows:

select t1.Text1
from Table1 t1
left join Table2 t2 on (t1.SomeKey = t2.SomeKey)

returns:

Text1
-------
Tralala
Joehoe
Joehoe
Trololo

A common reason why one would do such a left join, without selecting fields from the joined table, is to find records in Table1 that don't have a match in Table2. Or use criteria based on Table2 fields.

select t1.Text1, t1.SomeKey 
from Table1 t1
left join Table2 t2 on (t1.SomeKey = t2.SomeKey)
where t2.Id is null;

returns:

Text1    SomeKey
-------- -------
Trololo  30