Imagine we've got an EMPLOYEES table, that for each employee gives their employee id, their name, and the employee ID of their direct manager:
EMPLOYEE_ID EMPLOYEE_NAME MANAGER_ID
----------- ------------- ----------
1 Alice the CEO NULL
2 Bob the Director 1
3 Charlie the Grunt 2
We can get all employee-manager relationships with an inner join that finds every pair of rows in the table where the MANAGER_ID on one row matches the EMPLOYEE_ID on the other:
SELECT emp.EMPLOYEE_NAME, mgr.EMPLOYEE_NAME AS MANAGER_NAME
FROM EMPLOYEES AS emp
INNER JOIN EMPLOYEES AS mgr
ON emp.EMPLOYEE_ID = mgr.EMPLOYEE_ID
which produces this:
EMPLOYEE_NAME MANAGER_NAME
------------- -------------
Bob the Director Alice the CEO
Charlie the Grunt Bob the Director
Note that Alice does not appear in the left-hand column, because she has no manager, and Charlie doesn't appear in the right-hand column, because he doesn't manage anyone.
If we want to list all employees, with their managers where they have them, we can use a left outer join, which returns all rows on the "left" side of the join regardless of whether they have matching rows on the "right" side -- like this (cutting out bits of the query repeated from the previous example):
SELECT ... LEFT OUTER JOIN EMPLOYEES AS mgr ON ...
That produces this:
EMPLOYEE_NAME MANAGER_NAME
------------- -------------
Alice the CEO NULL
Bob the Director Alice the CEO
Charlie the Grunt Bob the Director
If we want to see who (if anyone) every employee manages, we could use a right outer join, which is similar to the previous but takes rows from the "right" side whether or not there are matching rows on the "left":
SELECT ... RIGHT OUTER JOIN EMPLOYEES AS mgr ON ...
This produces:
EMPLOYEE_NAME MANAGER_NAME
------------- -------------
Bob the Director Alice the CEO
Charlie the Grunt Bob the Director
NULL Charlie the Grunt
(To be honest, this is even more contrived than the previous examples: you can achieve the same result by reordering things in the left outer join, and that would come more naturally to most people, including me. And you would probably swap the order you display the columns in. But I include it for completeness.)
Finally, you can get a combined list of employees and their managers (if any), and potential managers and their direct reports (if any), with a full outer join, which returns rows from both sides of the join, regardless of whether they have matching rows on the other:
SELECT ... FULL OUTER JOIN EMPLOYEES AS mgr ON ...
Producing:
EMPLOYEE_NAME MANAGER_NAME
------------- -------------
Alice the CEO NULL
Bob the Director Alice the CEO
Charlie the Grunt Bob the Director
NULL Charlie the Grunt
Using a left join exclusively isn't a problem, as long as it's returning the data you need. If you use a left join where your data requirements actually call for a different type of join (e.g. "exclude any employees without a manager"), it could be a problem. You could get round it by adding a WHERE clause (e.g. "WHERE mgr.EMPLOYEE_ID IS NOT NULL"), but that's a bit ugly and hacky.
Oh, absolutely. In some (many? most?) SQL dialects, you don't have to write INNER JOIN, either: an unqualified JOIN keyword implies an inner join. I was just trying to be explicit.
If we want to list all employees, with their managers where they have them, we can use a left outer join, which returns all rows on the "left" side of the join regardless of whether they have matching rows on the "right" side -- like this (cutting out bits of the query repeated from the previous example):
That produces this: If we want to see who (if anyone) every employee manages, we could use a right outer join, which is similar to the previous but takes rows from the "right" side whether or not there are matching rows on the "left": This produces: (To be honest, this is even more contrived than the previous examples: you can achieve the same result by reordering things in the left outer join, and that would come more naturally to most people, including me. And you would probably swap the order you display the columns in. But I include it for completeness.)Finally, you can get a combined list of employees and their managers (if any), and potential managers and their direct reports (if any), with a full outer join, which returns rows from both sides of the join, regardless of whether they have matching rows on the other:
Producing: Using a left join exclusively isn't a problem, as long as it's returning the data you need. If you use a left join where your data requirements actually call for a different type of join (e.g. "exclude any employees without a manager"), it could be a problem. You could get round it by adding a WHERE clause (e.g. "WHERE mgr.EMPLOYEE_ID IS NOT NULL"), but that's a bit ugly and hacky.