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.
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.
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.
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.
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.
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.
The same goes for the “employees” and “titles” tables.
The “dept_manager” table and the “dept_emp” table are not logically adjacent. This means they can be joined, too!
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.
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
