Zanam Zanam - 2 months ago 7
SQL Question

Why use left join at all

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 column_name(s)
FROM table1


instead of writing

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

Answer

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.