Joining Tables - SQL Training

This is the Joining Tables tutorial of the SQL Training course offered by Simplilearn. In this tutorial, we will learn about Table Aliases and various types of Joins.

Table Aliases

Table aliases are an important feature when it comes to joining multiple tables. It is not relevant if there is only one table, but works only with multiple tables.

Column Aliases

Column aliases have the idea of giving a column, especially a calculated column a sensible, easy, and readable name.

The table aliases have a slightly different idea, which is similar to the idea of simplifying but with a different focus. When we work with multiple tables, we will very quickly have to repeat table names because we have conflicting column names and we want to abbreviate it. So the table aliases typically help to abbreviate this.

Let's have a quick look at our diagram to understand better.

DEMO

We have a Person table and a Contact table. For person table, we have person ID and for contact table, we have contact pid as our relationship.

If we run the following code:

Select * from Person

Select * from Contact

Now, if we introduce a join below,

Select *

From person, contact

Where person.id=contact.pid

We will be introducing Table aliases now and abbreviate the names to reduce the complexity.

Also now we can be more significant in our search. For example, If we just need the first name and the last name from the results.

We understood table aliases and its usage with examples above. Now we will look into various kinds of Joins.

Inner Joins

One of the most important features in SQL is Inner Joins. We will go back to the same example to understand it better.

DEMO

We will be joining 2 tables first, and further, we will add more tables. Let us join the contact table and the person table.

Before we go ahead, here is an example of Cross Join that we always need to avoid. Cross joins always gives out mixed data or wrongly mapped data.

Also, since we have used table aliases above, we will be doing the same with the inner joins example. Not to forget, we can also be more specific in searching out results like first name and last name.

Also, if we want, we can sort out a column in the table above in ascending order. Even the column can be filtered out with only names starting with a particular alphabet.

An explicit version of Inner Joins is shown below.

Outer Joins

Just like the Inner joins, Outer joins allows us to consolidate data from multiple tables related to each other into one big report. It brings back together the related data in a way that they're actually readable for the user.

Difference between Inner Joins and Outer Joins

Inner joins show only those records where all elements are present whereas Outer joins show incomplete records as well.

DEMO

We have a person table and a project person table. We will be joining these two tables and want to look at the results that show persons involved in respective projects.

In the case of Inner joins, we can only look at people who have been assigned at least one project. But, in the case of outer joins, we can look at all the people names regardless of a project assigned or not. In such type of cases, outer joins are always helpful.

Let us look at the same example.

We will look at person table, project person table and project table below. We will be using the explicit version of outer joins below.

If we look at the explicit inner joins first, we get results where there is a missing person from the total search results.

Now, if we use the outer join below, we will get the result as shown below.

We can notice that the excluded data also shows up in the results. Also, we can customize our search results using significant codes.

So, we covered Left Join in the example above. Even if we use Right Join, the only difference that shows up is the placement of columns in the search results.

There is also Full Join that is a combination of left and right joins and only useful if there are at least 3 tables.

Self Joins

Self-joins are inner or outer joins but unlike normal inner and outer joins where we join two or more tables with each other, in a Self Join, we are joining a table with itself.

DEMO:

In cases, where we want a detailed search report from the table itself, we use self-joins. For example, if we want to display different manager names whom every single person reports to within the company, we use Self Joins.

Also, we can filter the results out by column names as an when required.

Self Joins are always helpful, especially when there are hierarchical scenarios in the database.

Complex Joins

In this section, we will look at some more complex joins. We will be joining more tables and we will be performing the inner as well as outer joins.

DEMO

We will extend the inner joins beyond 2 tables. We will look at the contact table, the person table, and the contact type table.

Looking at the same example. We will always be using table aliases in the process. Joining the first two tables looks like below.

Now after we join the third table as well, which is the contact Type.

We first joined the person and contact table and then we joined the contact and contact type table. We can add as many tables as possible and the key thing to remember is to add a ‘where’ clause whenever we add one extra table. For example, we need to add 4 ‘where’ clause to add 5 tables.

Also, we can filter out columns as we want by adding codes and removing data that we do not want. We will be adding the project person table as well as the project table to the above tables.

From the above result, we found out that we have duplicate columns that we need to distinguish and so we will be using column aliases.

Next, we look into using complex outer joins.

Next, we will extend it to more than 2 tables.

So we see that we can use all left joins or all right joins or mixed left and right joins and experiment with the results. Now you can add and built as many queries as possible.

Conclusion

With this, we come to an end to the SQL - Joining Tables tutorial.