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:

id emploee_id name deadline
1 1 Fix a bug on the frontend 2022-06-01
2 2 Fix a bug on the backend 2022-06-15
3 5 Buy coffee 2022-07-01
4 5 Buy coffee 2022-08-01
5 5 Will buy coffee 2022-09-01
6 (NULL) Clean up the office (NULL)
7 4 Enjoy life (NULL)
8 6 Enjoy life (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:

id name occupation salary age join_date id emploee_id name
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
6 Vaska cat 1000 3 2018-11-11 8 6 Enjoy life
(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.

JOIN types

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:

id name occupation salary age join_date id emploee_id name
3 Ivanov Sergey Tester 40000 thirty 2014-01-01 (NULL) (NULL) (NULL)

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:

id name occupation salary age join_date
3 Ivanov Sergey Tester 40000 thirty 2014-01-01

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.

Java university