Lesson 9

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.

Here's why JOIN clauses are so fundamental and powerful in SQL:

1. Relational Nature of Databases: Relational databases store data across multiple tables that have logical relationships with each other. JOIN clauses are the key to unlocking and utilizing these relationships.
2. Retrieving Combined Data: JOINs allow you to combine data from multiple tables into single result sets based on common values in related columns. Let's see why this is crucial:
   Example: Without joins, you couldn't fetch an order record along with the associated customer's name from their respective tables in a single query.
3. Complex Queries: JOINs enable you to answer complex questions that require data from multiple sources. Examples include:
   • Aggregating Across Tables: Calculate the total sales per customer from separate Customers and Sales tables.
   • Filtering Based on Relationships: Show orders that meet specific criteria, such as orders from a specific country or exceeding a certain value.
4. Data Integrity: Often, spreading data across tables with proper foreign key relationships helps enforce data integrity and reduce redundancy. JOINs help you piece together this modular data and keep your results consistent.
5. Flexibility: SQL offers different join types (INNER JOIN, OUTER JOIN, CROSS JOIN, etc.). These provide precise control over how the data from multiple tables should be combined, allowing for a wide range of scenarios.

Importance in Practice

• Real-World Data: In any realistic database, data is rarely confined to a single table. JOINs are the primary tool for working with real-world scenarios with interconnected data.
• Report Generation: Generating meaningful reports often requires combining data from various tables for analysis and insights.

In Summary: JOIN clauses in SQL are essential because they:

• Allow you to harness the full potential of a relational database.
• Combine data from multiple tables based on relationships.
• Enable you to ask complex questions and generate meaningful insights.

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. 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. Let us take a look at the two tables: BOOKS and AUTHORS, presented in Figure 7.9 and Figure 7.9.1

Question: 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).

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.