In this module, you saw some of the unique capabilities of SQL to bring together information sets.
Joins are important because they let you combine results and at the same time filter out (or add in) information as needed.
SQL tends to be a language of refinements, and you will probably use joins in efforts to refine results rather than to create initial SQL
statements. That is, you will find that it is often easiest to start with a very large results set, and then narrow it down by using qualifiers,
joins, filters, and other items.
If you think of joins as a tool in this arsenal, and remember that you have these capabilities, you will be well on your way to understanding the capabilities of SQL and how you can incrementally refine and optimize your queries.
Joins are helpful in pulling together information from several tables. You will use joins in the class project to create the relationships between several tables that will lead to the customer and Green Snarfle Widget information needed.
In a SELECT statement, the JOIN keyword, as the name implies, joins records from two (or more) tables, while UNION is used to combine records returned from two or more SELECT statements. We will talk about both in more details in Chapter 7; here we are going to restore faith in the sanity of the people who designed SQL and put down the normalization rules. Honestly, they did know what they were doing.
JOIN Syntax
Let us take a look at the two tables: BOOKS and AUTHORS, presented in Figure 7.9 and Figure 7.9.1
Figure 7.9 - Books Table
Figure 7.9.1 - Authors Table
Why can’t we just run SQL query?
SELECT * FROM books, authors;
The returned data set contains every possible combination of the records from both tables, highly
confusing and utterly useless. Additionally, it uses obsolete syntax. The correct syntax for the previous
query is the following:
SELECT * FROM books CROSS JOIN authors;
This syntax will yield the very same result, but at least you’ll have the satisfaction that it was
produced intentionally, not because you forgot to specify JOIN criteria. This is where primary and foreign keys shine.
Let us take a closer look at proper JOIN(s), but fi rst a rule: Never use deprecated syntax in your SQL. The unintentional CROSS JOIN (or Cartesian product, as it is known) would never escape your attention with proper syntax; it is very easy to do with the old one (and things get worse as the number of tables in the JOIN increases). So to extract meaningful information from both BOOKS and AUTHORS, we should use the intermediary table: BOOKS_AUTHORS created to resolve the many-to-many (N:N) relationship (see Figure 7.9.2).
Figure 7.9.2:
The SELECT statement that matches authors with the books they wrote using the JOIN syntax would look as follows:
SELECT authors.au_last_name, books.bk_title FROM books JOIN books_authors
ON (books.bk_id = book_authors.bk_id) JOIN authors
ON (book_authors.au_id = authors.au_id)
The purpose of this code is to extract a list of authors’ last names and the books they wrote in a single SELECT statement. Because of the potential N:N relationship, an intermediary table was introduced that matched the book’s ID to the author’s ID. The JOIN produced records
from the BOOKS table that had matching IDs from the BOOKS_AUTHORS table, and combined
them with records produced by matching authors' ID in the intermediary table with them in the AUTHORS table.
The result was the correct list of authors and their respective masterpieces combined in a single happy data set.
