loading..
   English
13:28

These sly outer joins

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
  1. SELECT Classes.class, name, launched
  2. FROM Classes LEFT JOIN
  3. Ships ON Classes.class = Ships.class AND
  4. launched IS NOT NULL;

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:

Class Name launched
Bismarck NULL NULL

But how can that be? Weve used launched IS NOT NULL in the join predicate, havent we? And the answer to our question lies in the join predicate. Lets 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. Thats why we got this row, that is, the Bismarck class is in the Classes table. And what if such ship existed? Lets add two ships of the Bismarck class into the Ships table one with a known launch year, another with undefined:

Console
Execute
  1. SELECT *
  2. FROM Ships
  3. UNION ALL
  4. SELECT 'B_1' AS name, 'Bismarck' AS class, 1941 AS launched
  5. UNION ALL
  6. SELECT 'B_2' AS name, 'Bismarck' AS class, NULL AS launched;


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

Solution 8.6.2

Console
Execute
  1. SELECT Classes.class, name, launched
  2. FROM Classes LEFT JOIN
  3. (SELECT *
  4. FROM Ships
  5. UNION ALL
  6. SELECT 'B_1' AS name, 'Bismarck' AS class, 1941 AS launched
  7. UNION ALL
  8. SELECT 'B_2' AS name, 'Bismarck' AS class, NULL AS launched
  9. ) Ships ON Classes.class = Ships.class AND
  10. launched IS NOT NULL;

And we get the expected result: there will be only one ship of Bismarck class in the resulting set:
Class Name launched
Bismarck B_1 1941

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
  1. SELECT Classes.class, name, launched
  2. FROM Classes LEFT JOIN
  3. Ships ON Classes.class = Ships.class
  4. WHERE launched IS NOT NULL;

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

Finally, Id 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.


Bookmark and Share
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
yesterday
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100