OUTER JOIN statement

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:

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:

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:

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:

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.