Introduction to SQL Joins

SQL Tutorials 5 min read
sql joins
Blog / SQL Tutorials / Introduction to SQL Joins

Understanding how to construct relationships between objects using SQL joins is a turning point in the journey of learning SQL. You will also see how to relate SQL tables in practice.

We will be working with the “employees” database, so if you haven’t downloaded it, you can here.

Now, think about strategies that would allow us to link some of the tables in our “employees” database.

Linking tables in the employees database, sql joins

How can we know what information can be retrieved from the different tables?

And what should the criteria be when creating a relationship between two tables?

Relational Schemas

Relational schemas are the perfect tool that will help you find a strategy for linking tables. Let’s examine the representation of the “employees” database. Specifically, we’ll focus on the “employees” and the “dept_emp” tables.

Relational schema of employees and dept_emp tables, sql joins

Technically, SQL joins show result sets containing fields derived from two or more tables. Therefore, we can use a join to relate the “employees” and the “dept_emp” table then extract information from the “employees” table like employee number, first and last name. While from the “dept_emp” table extract: department number and start date of the labor contract.

Each table with its extractions circled, sql joins

How to Use SQL Joins?

The idea of using joins is twofold. First, we must find a related column from the two tables that contain the same type of data. In our case, this can be the “emp_no” column. This technique allows us to extract information from the two tables under conditions based on this column.

related column - emp_no, sql joins

Second, we will be free to add columns from these two tables to our output. In our example, we could obtain the fields we just mentioned – employee number and name, department number, and contract start date.

The two tables with columns emp_no, first name, last name, dept no and from date added, sql joins

What to Look out for

When thinking about a strategy for retrieving data from different tables – please, be very careful about the following:

The columns you use to relate tables must represent the same object, such as id. But the tables you are considering need not be logically adjacent in the relational schema.

data science training

Logically Adjacent Tables

Examples of two logically adjacent tables are the “employees” and “dept_emp” tables.

employees and dept manager are logically adjacent, sql joins

The same goes for the “employees” and “titles” tables.

employees and titles are logically adjacent, sql joins

The “dept_manager” table and the “dept_emp” table are not logically adjacent. This means they can be joined, too!

Dept manager and dempt emp tables are not logically adjacent

Moreover, you won’t have to indicate the relationship that goes through the “employees” table and the “emp_no” key or through “departments” and the “dept_no” key specifically.

No relationship between emp no and dept no keys

What is important is you have found a related column connecting the two tables.

How to Create Joins?

In the tutorial that follows, we will show you one of the most notable types of SQL joins– the inner join. To do this, we will use two duplicate tables. You can see how to create one of them, the “departments_dup”, in the following article. Furthermore, you will create a new table that will be a duplicate of the “dept_manager” table. Hence, assume this exercise is a refresher on the data definition language part of the SQL syntax.

***

Eager to hone your SQL skills? Learn how to put theory into practice with our hands-on tutorials!

Next Tutorial: How to Create an Inner Join

Earn your Data Science Degree

Expert instructions, unmatched support and a verified certificate upon completion!

Leave a Reply

Your email address will not be published.