15. JOIN functions: Basic tasks

JOIN functions: Basic tasks

When working with multiple tables in SQL, we usually use JOIN statements. To learn more about different types of joins in SQL, we highly recommend watching the following YouTube videos:

 

SQL JOINS Tutorial for beginners | Practice SQL Queries using JOINS - Part 1

 

 

SQL JOINS Tutorial for beginners | Practice SQL Queries using JOINS - Part 2

 

 

If you prefer the reading material, we recommend exploring this document.

 

Let's go back to our Bookstore dataset and solve a couple of basic SQL tasks using joins.

Task #1: List Books with Author Names: Display the title of each book along with the name of its author.

This task involves merging information from two different tables: one containing book details and the other containing author names. The goal is to pair each book with its author. If an author’s details are unavailable or missing, the book is still included in the list, but the author's name is left blank. This approach ensures a complete catalog that pairs books with their authors wherever possible.

Objective: Create a comprehensive book catalog that includes the title of each book along with its author's name.

Expected Result: A catalog listing each book with details such as title, genre, and publish date, alongside the name of its author.

Data Source: dataacademykz.bookstore.{table_name}

Click To Show / Hide Solution

-- Selecting book details along with author names

SELECT
  a.name, -- Author name
  b.title, -- Book title
  b.genre, -- Book genre
  b.price, -- Book price
  b.publish_date -- Book publish date
FROM
  `dataacademykz.bookstore.books` AS b -- Books table alias
LEFT JOIN
  `dataacademykz.bookstore.authors` AS a -- Authors table alias
ON
  a.author_id = b.author_id -- Join condition on author ID

Explanation:

Think of this query as creating a catalog of books, where for each book, we want to include the name of the author. We start with our complete list of books and then look up the authors' names to add to this list. If we find the author of a book, we add their name next to the book's details. If we can't find the author (maybe because the author’s details are missing or unknown), we still keep the book on our list; we just leave the author's name blank.

This way, we ensure that every book is included in our catalog, but we also provide author information wherever it's available.

Technical Breakdown:

In this SQL query, we are employing a LEFT JOIN to merge rows from two tables: books (aliased as b) and authors (aliased as a). The LEFT JOIN is based on a common field, author_id, which exists in both tables. Here’s how the LEFT JOIN operates:

• Primary Table: The books table is our primary or "left" table. This means that the query will retrieve all records from the books table regardless of whether a matching record in the authors table exists.

• Joining Condition: The ON a.author_id = b.author_id clause specifies the condition for the join. It pairs each row from the books table with rows from the authors table where the author_id fields are equal.

• Result: For each book in the books table, the query fetches the corresponding author's name from the authors table. If a book does not have a matching author in the authors table (i.e., if author_id is not found or is NULL in the authors table), the author's name will appear as NULL in the result set. However, all books will be listed regardless of whether they have a corresponding author.

 

Task #2: Analysis of Original Price Versus Sale Price Differences for Books.

In this task, we're delving into the pricing strategies of a bookstore by comparing the listed (original) prices of books with the prices at which they were actually sold in orders. This comparison helps in understanding the store's discounting practices or instances where books might have sold for more than their original price.

Objective: Conduct an analysis to compare the original price of books with their sale price in each order.

Expected Result: A detailed report showing the original and sale prices of books for each order, along with the calculated difference between these prices.

Data Source: dataacademykz.bookstore.{table_name}

Click To Show / Hide Solution

-- Selecting order and book details, including pricing and discounts

SELECT
  ord.order_id, -- Order ID
  bok.book_id, -- Book ID
  bok.title, -- Book title
  bok.price AS original_price, -- Original price of the book
  ord.price_per_unit AS sale_price, -- Sale price per unit
  ROUND(bok.price - ord.price_per_unit, 2) AS difference -- Difference between original and sale price
FROM
  `dataacademykz.bookstore.order_details` AS ord -- Order details table alias
LEFT JOIN
  `dataacademykz.bookstore.books` AS bok -- Books table alias
ON
  ord.book_id = bok.book_id -- Join condition on book ID

Explanation:

Imagine you're comparing the sticker price of a book with the price it was actually sold for in each order. This query is like looking at a store's sales records and matching each sale with the book's catalog information.

For every sale, we find the book involved and see what its listed price was. Then, we compare it to the price at which it was sold. The query even calculates how much less (or more) the book sold for compared to its original price. It's like a report showing where the store might be giving discounts or, in rare cases, selling at a higher price.

Technical Breakdown:

This SQL query is designed to compare the original price of books with their sale price as listed in individual orders. The query uses a LEFT JOIN to combine data from the order_details table (aliased as ord) with the books table (aliased as bok). Here’s the technical breakdown:

• Primary Table: The order_details table is the primary or "left" table in the join. The query includes all records from order_details.

• Joining Condition: The join is performed on the book_id field, which is common to both tables. The ON ord.book_id = bok.book_id clause aligns each record in order_details with the corresponding record in books, based on the book's ID.

• Result: For every order detail record, the query fetches the related book's title and original price from the books table. The query calculates the difference between the book's original price and the sale price per unit for each order. If a book referenced in order_details does not have a corresponding entry in the books table, the book-related fields (like title and original price) will appear as NULL in the output.

 

Task #3: Comprehensive Order Summary with Customer and Book Details.

This task involves compiling a comprehensive summary of book orders from a bookstore. For each order, the report should provide details about who placed the order, what book they bought, and the author of the book. This multi-dimensional view of each order is invaluable for understanding customer preferences and the popularity of different books and authors.

Objective: Create a detailed report for each book order, including information about the customer, the book, and the author.

Expected Result: A detailed summary for each order, including the customer's name, the title of the book ordered, and the name of the author.

Data Source: dataacademykz.bookstore.{table_name}

Click To Show / Hide Solution

-- Selecting order details along with customer, book, and author information

SELECT
  ord.order_id, -- Order ID
  ord.order_date, -- Date of the order
  cus.name AS customer_name, -- Customer's name
  bok.title AS book_title, -- Book title
  aut.name AS author_name -- Author's name
FROM
  `dataacademykz.bookstore.orders` AS ord -- Orders table alias
LEFT JOIN
  `dataacademykz.bookstore.customers` AS cus ON ord.customer_id = cus.customer_id -- Joining with customers table
LEFT JOIN
  `dataacademykz.bookstore.order_details` AS det ON ord.order_id = det.order_id -- Joining with order details
LEFT JOIN
  `dataacademykz.bookstore.books` AS bok ON det.book_id = bok.book_id -- Joining with books table
LEFT JOIN
  `dataacademykz.bookstore.authors` AS aut ON bok.author_id = aut.author_id -- Joining with authors table

Explanation:

Think of this query as creating a detailed report of each book order from a bookstore. For every order placed, we want to know who placed the order, what book they bought, and who wrote that book.

• We start with our list of orders.
• Then, for each order, we find out who the customer is.
• Next, we look up which book or books were in that order.
• Finally, we find out who wrote each of those books.

The result is a complete picture of each order, showing not just what was bought and when, but also who bought it and who wrote it.

Technical Breakdown:

This SQL query is designed to compile a detailed summary of each order, incorporating data about customers, books, and authors. It employs a series of LEFT JOIN operations to merge data from multiple tables. Here's the technical breakdown:

• Base Table: The query begins with the orders table (aliased as ord), which serves as the foundation for the data aggregation.

• First LEFT JOIN: Connects orders to customers (cus). This join appends customer information to each order based on the customer_id.

• Second LEFT JOIN: Links order_details (det) to the orders. This join associates the details of each order, like which books were ordered, with the order records.

• Third LEFT JOIN: Joins books (bok) to the order details. This operation brings in detailed information about each book included in the orders.

• Fourth LEFT JOIN: Connects books to authors (aut). This final join appends the author information to each book in the order details.

• Result: The query produces a comprehensive dataset that includes the order ID, order date, customer name, book title, and author name for each order.

You'll see some NULL values since our dataset is perfectly imperfect. This means that there is no corresponding book_title and author_name for some orders (like order_id 19), which indicates some issues with the quality of data we are working with.