6. JOINS

Overview

This chapter will help you understand the functionality of the various joins and their implementation through examples. By the end of this chapter, you will be able to implement an INNER JOIN to retrieve overlapping data within multiple tables and write LEFT and RIGHT JOIN queries to filter the results that have been obtained from multiple tables. We will also be able to implement a CROSS JOIN to obtain a cartesian product of the table elements; and combine two queries using UNION.

Introduction

In the previous chapter, we saw how the WHERE clause can be used to filter elements and provide us with more control over the data we can retrieve from a table. However, in many cases, not all the data we require can be found in a single table. Having a single table to store all the data is also not feasible because its maintenance will be extremely difficult. One way of connecting tables and retrieving data from them is by using joins.

We can use the JOIN operation to extract data from multiple tables that have common columns using a single query. Based on the data that's required, there are various types of joins that are supported by SQL.

In this chapter, we will look at the following types of joins that are available in SQL:

• INNER JOIN
• RIGHT JOIN
• LEFT JOIN
• CROSS JOIN
• UNION

Each topic in this chapter will illustrate a type of JOIN. We will be working with the PACKT_ONLINE_SHOP throughout this chapter. First, we'll begin...

INNER JOIN

The INNER JOIN is the default type of join that is used to select data with matching values in both tables. It can be represented with the following Venn diagram:

Figure 6.1: INNER JOIN

The INNER JOIN represents the highlighted section, which is the intersection between these two tables. Let's have a look at the INNER JOIN syntax:

SELECT [Column List]
  FROM [Table 1] INNER JOIN [Table 2] 
    ON [Table 1 Column Name] = [Table 2 Column Name]
WHERE [Condition]

The syntax can also be written as follows:

SELECT [Column List]
  FROM [Table 1] JOIN [Table 2] 
    ON [Table 1 Column Name] = [Table 2 Column Name]
WHERE [Condition]

Note

The use of INNER in the query is optional.

The INNER JOIN is one of the most commonly used type of joins. Let's implement this in an exercise.

Exercise 6.01: Extracting Orders and Purchaser Information

You are a store manager that...

RIGHT JOIN

This type of join is used when you want to select records that are available in the second table and matching records in the first one. This can be visualized with the following Venn diagram:

Figure 6.4: RIGHT JOIN

As we can see, the RIGHT JOIN represents the highlighted section, that is, TABLE B, and the intersected section of TABLE A. Let's look at the syntax for the RIGHT JOIN:

SELECT [Column List]
  FROM [Table 1] RIGHT OUTER JOIN [Table 2] 
    ON [Table 1 Column Name] = [Table 2 Column Name]
WHERE [Condition]

The syntax can also be written as follows:

SELECT [Column List]
  FROM [Table 1] RIGHT JOIN [Table 2] 
    ON [Table 1 Column Name] = [Table 2 Column Name]
WHERE [Condition]

Note

Writing OUTER in the query is optional.

Exercise 6.02: Implementing RIGHT JOIN

The store wants the list of customers, along with their orders, and also wants to include customers...

LEFT JOIN

This type of JOIN is used when you want to select records that are available in the first table and match records in the second one. It can be represented with the following Venn diagram:

Figure 6.7: LEFT JOIN

The LEFT JOIN represents the highlighted section from TABLE A and the intersected section from TABLE B. Let's look at the syntax:

SELECT [Column List]
  FROM [Table 1] LEFT OUTER JOIN [Table 2] 
    ON [Table 1 Column Name] = [Table 2 Column Name]
WHERE [Condition]

The syntax can also be as follows:

SELECT [Column List]
  FROM [Table 1] LEFT JOIN [Table 2] 
    ON [Table 1 Column Name] = [Table 2 Column Name]
WHERE [Condition]

Note

The OUTER word in the query is optional.

This type of join is very similar to the RIGHT JOIN, with the only difference being that it executes the table on the opposite (left) side. Now that we have seen how we can implement LEFT JOIN...

CROSS JOIN

This type of join is used when you want to combine the elements of a particular column with the elements of another column. This implies that each record from the first table and each record from the second table are laid out in all possible combinations in one single table, just like in the case of a cartesian product. Here is how we can perform this task using the CROSS JOIN syntax:

SELECT [Column List]
  FROM [Table 1] CROSS JOIN [Table 2] 
WHERE [Condition]

To understand this concept well, we will perform CROSS JOIN on simple tables and see how they work in the following exercise.

Exercise 6.04: Implementing CROSS JOINS

Consider that we have a table called Facecards, with a column called suits, and a table called CardSuite with a column called cardvalue. Now, we want to cross-reference all the suits with all the card values. To do this, perform the following steps:

1. Create a table called Facecards with the following values:

   Create table Facecards...

UNION JOIN

The UNION operation is used to combine two queries. Let's look at the syntax:

SELECT [COLUMNS LIST] FROM [TABLE NAME]
UNION 
SELECT [COLUMNS LIST] FROM [TABLE NAME]

However, the most important point to remember when we use the UNION operation is to ensure the following:

• Both query columns have similar data types
• Both query columns are in the same order

Let's take a look at the following exercise to see how the UNION query functions.

Exercise 6.05: Implementing a UNION JOIN

The store manager wants a telephonic feedback survey from everyone who the store employees work with. This implies that there's a list of suppliers and customers and their full names, along with their contact numbers. To do this, perform the following steps:

1. In a new query window, write the following query:

   SELECT CONCAT(Customers.FirstName,' ',Customers.LastName) as 'FULL NAME',
           Customers...

Summary

In this chapter, we learned all about joins and how we can get data from multiple tables, and we explored the different types of joins, along with the UNION operation. These essential skills will be your base moving forward so that you can get creative with mix and match joins to pinpoint the result set you are looking for. In the next chapter, we will look at some other ways we can retrieve data from multiple tables.