Prepare for the second stage of testing

Typical problems

Let it be necessary to define all ships with a known launch year (the training database “Ships”) for each class. When “for each class” is said, we already know that we must use outer join, for example, the left one:

Solution 8.6.1

Console

Execute

SELECT Classes.class, name, launched 
FROM Classes LEFT JOIN 
 Ships ON Classes.class = Ships.class AND 
 launched IS NOT NULL;

SELECT Classes.class, name, launched FROM Classes LEFT JOIN Ships ON Classes.class = Ships.class AND launched IS NOT NULL;

    Scheme

That is, we join the Classes table with the Ships table by the ‘class’ column and choose the ships with a known launch year. And this is what we get in result, apart from anything else:

But how can that be? We’ve used ‘launched IS NOT NULL’ in the join predicate, haven’t we? And the answer to our question lies in the join predicate. Let’s get back to the definition of the left outer join:

All rows from the left table are combined with those from the right, for which the predicate value is true. If for any row from the left table there is not a single corresponding row from the right table then the values of the right table are NULLs.

In the Ships table there is no vessel of Bismarck class. That’s why we got this row, that is, the Bismarck class is in the Classes table. And what if such ship existed? Let’s add two ships of the Bismarck class into the Ships table – one with a known launch year, another with undefined:

Console

Execute

SELECT * 
FROM Ships
UNION ALL
SELECT 'B_1' AS name, 'Bismarck' AS class, 1941 AS launched
UNION ALL
SELECT 'B_2' AS name, 'Bismarck' AS class, NULL AS launched;

SELECT * FROM Ships UNION ALL SELECT 'B_1' AS name, 'Bismarck' AS class, 1941 AS launched UNION ALL SELECT 'B_2' AS name, 'Bismarck' AS class, NULL AS launched;

    Scheme

Now we rewrite our solution 8.6.1, taking these new ships into account: 

Solution 8.6.2

Console

Execute

SELECT Classes.class, name, launched 
FROM Classes LEFT JOIN 
 (SELECT * 
 FROM Ships
 UNION ALL
 SELECT 'B_1' AS name, 'Bismarck' AS class, 1941 AS launched
 UNION ALL
 SELECT 'B_2' AS name, 'Bismarck' AS class, NULL AS launched
 ) Ships ON Classes.class = Ships.class AND 
 launched IS NOT NULL;

SELECT Classes.class, name, launched FROM Classes LEFT JOIN (SELECT * FROM Ships UNION ALL SELECT 'B_1' AS name, 'Bismarck' AS class, 1941 AS launched UNION ALL SELECT 'B_2' AS name, 'Bismarck' AS class, NULL AS launched ) Ships ON Classes.class = Ships.class AND launched IS NOT NULL;

    Scheme

Conclusion. If you need to restrict the resulting set of the outer join use WHERE clause, which serves exactly this purpose:

Solution 8.6.3

Console

Execute

SELECT Classes.class, name, launched 
FROM Classes LEFT JOIN 
 Ships ON Classes.class = Ships.class
WHERE launched IS NOT NULL;

SELECT Classes.class, name, launched FROM Classes LEFT JOIN Ships ON Classes.class = Ships.class WHERE launched IS NOT NULL;

    Scheme

And the join predicate only defines which rows from different tables will be concatenated in the resulting set.

Finally, I’d note that this example is not quite revealing, as the INNER JOIN would fit into this solution as well, despite the “for each class” words. But the flexibility of  SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL language allows finding different variants, and stereotype usage is fully justified.