sql

SQL Self Join

Hello. In this tutorial, we will learn the SQL SELF JOIN (represented by the inner join keyword).

1. Introduction

SQL stands for Structured Query Language and is used to extract and organize data stored in relational databases like MySQL, PostgreSQL, Oracle, etc. A relational database consists of rows and columns that allow fetching specific information from databases that can be used later for analysis. In real-time SQL manages a large amount of data that is written and read simultaneously and any query that reaches the SQL server is processed into three parts –

  • Parsing – Process to check the SQL query syntax
  • Binding – Process to check the SQL query semantics
  • Optimization – Process to generate the SQL query execution plan

1.1 Usage of SQL

Structured Query Language (popularly known as SQL) is commonly used by data analysts and data science professionals and is helpful too:

  • Execute queries against the database
  • Retrieve data from the database
  • Insert new records into the database
  • Update existing records into the database
  • Created stored procedures, functions, and materialized views in the database
  • Create users and grant permissions
  • Set permissions on tables, stored procedures, functions, and materialized views

1.2 SQL SELF JOIN

The SQL SELF JOIN is used to join a table with itself as if there were two different tables. To form a self join the same table is used twice with different aliases.

  • The result is calculated with the help of inner join keyword
  • Represented by the syntax – SELECT * FROM table_name t1 INNER JOIN table_name t2 ON join_condition

2. SQL SELF JOIN keyword

Let us dive into some practice implementation.

2.1 Postgres Setup

Usually, setting up the database is a tedious step but with the technological advancements, this process has become simple with the help of Docker. Readers can watch the video available at this link to understand the Docker installation on Windows OS. Open the terminal and trigger the following commands to get the PostgreSQL up and running on the local machine.

-- command to run postgres on docker
docker run -d -p 5433:5432 -e POSTGRES_PASSWORD= --name postgres postgres

-- command to stop the Postgres docker container
docker stop postgres

-- command to remove the Postgres docker container
docker rm postgres

Remember to enter the password of your choice. If everything goes well the PostgreSQL database server would be up and running on a port number – 5433.

sql self join - postres on docker
Fig. 1. Postgres on Docker

2.2 Creating a Sample table

To implement this tutorial I am using the sample SQL table provided below. You’re free to create the below SQL table under the database of your choice. I selected test database while working.

SQL Script

-- table setup --
drop 
  table if exists employee;
  
create table employee (
  employee_id serial primary key, 
  first_name VARCHAR (255), 
  last_name VARCHAR (255), 
  manager_id INT, 
  foreign key (manager_id) references employee (employee_id) on delete cascade
);

insert into employee (
  first_name, last_name, manager_id
) 
values 
  ('Elfreda', 'Smitton', NULL), 
  ('Newton', 'Santora', 1), 
  ('Jess ', 'Hurich', 1), 
  ('Brittney', 'Rebbeck', 2), 
  ('Jan', 'Sobczak', 2), 
  ('Haslett', 'Tynnan', 2), 
  ('Gavan', 'Pidwell', 3), 
  ('Kirbie', 'Elcomb', 3), 
  ('Brant', 'Benson', 4), 
  ('Sandor', 'Bodicam', 4),
  ('John', 'Doe', NULL);

select 
  * 
from 
  employee;

2.3 Executing basic SQL command

You can use the following SQL command to practice the SQL self-join. The query will reference the employee table twice (i.e. one time as an employee and another time as manager) to fetch the data from the table.

SQL Script

-- self join --
select 
  e.first_name || ' ' || e.last_name employee, 
  m.first_name || ' ' || m.last_name manager 
from 
  employee e 
  inner join employee m ON m.employee_id = e.manager_id 
order by 
  manager;

3. Summary

In this tutorial, we learned the basics of SQL SELF JOIN and basic query implementation. You can download the sql scripts from the Downloads section.

4. Download the Scripts

This was a tutorial on learning the SQL SELF JOIN.

Download
You can download the full source code of this example here: SQL Self Join

Yatin

An experience full-stack engineer well versed with Core Java, Spring/Springboot, MVC, Security, AOP, Frontend (Angular & React), and cloud technologies (such as AWS, GCP, Jenkins, Docker, K8).
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments
Back to top button