Introduction to SQL Joins

SQL Tutorials 5 min read
sql joins

Introduction to SQL Joins

5 min read
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

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

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

 

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

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

 

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-program

Logically Adjacent Tables

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

employees and dept manager are logically adjacent

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

employees and titles are logically adjacent

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.

 

Starting 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.

Next Tutorial: How to Create an Inner Join

Leave a Reply

Your email address will not be published.

A Free Data Science Career Guide?

Check your email shortly!