Introduction to SQL Joins

Join over 2 million students who advanced their careers with 365 Data Science. Learn from instructors who have worked at Meta, Spotify, Google, IKEA, Netflix, and Coca-Cola and master Python, SQL, Excel, machine learning, data analysis, AI fundamentals, and more.

Start for Free
Martin Ganchev Martin Ganchev 25 Apr 2023 5 min read

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.

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? Take our SQL course.

Next Tutorial: How to Create an Inner Join

Martin Ganchev

Martin Ganchev

Instructor at 365 Data Science

Martin holds an MSc degree in Economic and Social Sciences from Bocconi University. His diverse academic and research experience combined with his friendly and explanatory approach to teaching have made him one of the most beloved instructors on our team. Some of the courses he has authored include: SQL, SQL + Tableau, SQL+Tableau+Python, Introduction to Python, Introduction to Jupyter, to name a few.

Top