Course Module 4. Working with databases - Lecture: 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:

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.