3Operators
3.1. Joins
3.1.1. Introduction
Joining in relational databases means extracting data from multiple linked tables. The data to be searched is distributed over several tables to comply with the normalization rules of the relational model. This standardization avoids or minimizes data redundancy while ensuring the referential integrity of the data.
The SQL1 standard (SQL’89) did not implement joins, but proposed a mechanism that simulated this operation by using a Cartesian product followed by a filter. This practice, although it continues to exist, is strongly discouraged because it has a very negative impact on the readability and performance of the query.
The SQL2 standard (SQL’92) quickly remedied this problem with the invention of the JOIN operator. The latter normalizes the join operation through improving the readability of the query by giving it semantics. This JOIN operator also reflects considerable processing by the DBMS to improve performance. These efforts are materialized by three optimization algorithms which are NESTED LOOP, MERGE SORT and HASH. These concepts fall within the theme of SQL query performance generally referred to as SQL Tuning, which will not be addressed in this book.
The following example illustrates this poor practice for displaying the list of products with their respective subcategories.
Get Analytic SQL in SQL Server 2014/2016 now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
