Reasons for OUTER JOIN
By the way, do you remember when we merged our spreadsheets and our office cleaning tasks disappeared because there was no cleaner yet?
If you run a query like this:
SELECT * FROM task
Then we get this result:
|1||1||Fix a bug on the frontend||2022-06-01|
|2||2||Fix a bug on the backend||2022-06-15|
|5||5||Will buy coffee||2022-09-01|
|6||(NULL)||Clean up the office||(NULL)|
The “Clear Office” task disappears if we try to join the task table with the employee table by employee_id.
To solve this problem, various modifiers have been added to the JOIN operator that allow such orphaned rows to be stored without a pair in another table.
Let me remind you of the classic form of the JOIN operator:
table 1 JOIN table 2 ON condition
We can tell SQL Server to make sure that all data from the left table (table1) is present in the joined table. Even if there is no pair for them in the right table. To do this, you just need to write:
table 1 LEFT JOIN table 2 ON condition
If you want the joined table to have all the rows from the right table , then you need to write:
table 1 RIGHT JOIN table 2 ON condition
Let's write a query that will combine all tasks and employees so that tasks without an executor are not lost. To do this, write a query:
SELECT * FROM employee e RIGHT JOIN task t ON e.id = t.emploee_id
And the result of this query:
|1||Ivanov Ivan||Programmer||100000||25||2012-06-30||1||1||Fix a bug on the frontend|
|2||Petrov Petr||Programmer||80000||23||2013-08-12||2||2||Fix a bug on the backend|
|4||Rabinovich Moisha||Director||200000||35||2015-05-12||7||4||Enjoy life|
|5||Kirienko Anastasia||Office Manager||40000||25||2015-10-10||3||5||Buy coffee|
|5||Kirienko Anastasia||Office Manager||40000||25||2015-10-10||4||5||Buy coffee|
|5||Kirienko Anastasia||Office Manager||40000||25||2015-10-10||5||5||Buy coffee|
|(NULL)||(NULL)||(NULL)||(NULL)||(NULL)||(NULL)||6||(NULL)||Clean up the office|
Another row has been added to our table, and interestingly, there are a lot of NULL values in it. All data that was taken from the employee table is displayed as NULL, since there was no executor from the employee table for the “Clean office” task.
There are 4 types of JOINs in total. They are presented in the table below:
|Brief entry||long entry||Explanation|
|1||JOIN||INNER JOIN||Only records that are in tables A and B|
|2||LEFT JOIN||LEFT OUTER JOIN||All rows without a pair from table A must be|
|3||RIGHT JOIN||RIGHT OUTER JOIN||All rows without a pair from table B must be|
|4||OUTER JOIN||FULL OUTER JOIN||All rows of base pairs from tables A and B must be|
For simplicity, if we represent tables as sets, then JOIN can be displayed as a picture:
Set intersection means that for one table there is a corresponding record from another table to which it refers.
Question from the interview
Sometimes novice programmers are bombarded with a very simple question at an interview. Given our tables, it can be formulated as follows:“Write a query that will display a list of all employees for whom there are no tasks .” First, let's try to rephrase this question a bit: "Write a query that will display a list of all employees from the employee table for which there are no tasks in the task table ." We need to get this set:
There are many ways to solve this problem, but I'll start with the simplest: First, you can join our tables with a LEFT JOIN, and then use WHERE to exclude all rows for which the missing data was padded with NULLs.
SELECT * FROM employee e LEFT JOIN task t ON e.id = t.emploee_id WHERE t.id IS NULL
And the result of this query:
The only disadvantage of this solution is that here the rows in the table contain NULL, and by condition we need to display a list of employees.
To do this, you either need to list the required columns of the employee table in SELECT, or if you need to display all of them, you can write the following construction:
SELECT e.* FROM employee e, task t
The complete request will look like this:
SELECT e.* FROM employee e RIGHT JOIN task t ON e.id = t.emploee_id WHERE t.id IS NULL
the result of this query:
The rest of the methods are left to you for homework. I don't want to deprive you of the pleasure of finding them yourself.