sql

SQL RIGHT JOIN Keyword

Hello. In this tutorial, we will learn the SQL RIGHT 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 RIGHT JOIN Keyword

The SQL RIGHT JOIN joins the two tables and fetches the rows from both tables based on a condition and the unmatched rows from the RIGHT table. This keyword –

  • Fetch all values from the RIGHT table
  • Fetch matching rows from both tables based on the condition
  • Sets the value of every column from the LEFT table to NULL if unmatched with the RIGHT table
  • Represented by the syntax – SELECT * FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;
sql right join - venn diagram
Fig. 1: SQL RIGHT JOIN Venn Diagram

2. SQL RIGHT 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 right join - postres on docker
Fig. 2. Postgres on Docker

2.2 Creating a Sample database

To implement this tutorial I am using the sample database provided below.

SQL Script

-- employee table and data --
create table employee
(
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(50) NOT NULL,
    gender VARCHAR(50)
);

insert into employee(first_name, last_name, email, gender)
values('Rice', 'Cristou', 'rcristou0@usa.gov', 'Female');
insert into employee(first_name, last_name, email, gender)
values('Delia', 'Jarrold', 'djarrold1@altervista.org', 'Male');
insert into employee(first_name, last_name, email, gender)
values('Zorine', 'Onions', 'zonions2@dagondesign.com', 'Female');
insert into employee(first_name, last_name, email, gender)
values('Kelley', 'Kleis', 'kkleis3@adobe.com', 'Female');
insert into employee(first_name, last_name, email, gender)
values('Reece', 'Petrushanko', 'rpetrushanko4@ihg.com', 'Male');

select * from employee;

-- department table and data --
create table department
(
    id SERIAL PRIMARY KEY,
    emp_id int,
    name VARCHAR(50) NOT NULL
);

insert into department(emp_id, name)values(1, 'Sales');
insert into department(emp_id, name)values(2, 'Product Management');
insert into department(emp_id, name)values(3, 'Research and Development');
insert into department(emp_id, name)values(4, 'Training');
insert into department(emp_id, name)values(5, 'Business Development');
insert into department(emp_id, name)values(NULL, 'Accounting');
insert into department(emp_id, name)values(NULL, 'Human Resources');

select * from department;

The database is available for download at this link and can be easily imported into the existing database of your choice.

2.3 Executing basic SQL commands

You can use the following SQL commands to practice the SQL joins.

  • The first query will return all the rows from the right table, rows that match a condition in both the tables. The dynamic column (named full_name) will be set to null if no value is found
  • In the second query, we will have the WHERE clause to find the department records that no employee assigned

SQL Script

-- right join --
select d.name as department_name, concat(e.first_name, ' ', e.last_name) as "full_name"
from employee e
     right join department d on e.id=d.emp_id;

-- right join with where clause --
select d.name as department_name, concat(e.first_name, ' ', e.last_name) as "full_name"
from employee e
     right join department d on e.id=d.emp_id
where e.first_name is null;

3. Summary

In this tutorial, we learned the basics of the SQL RIGHT JOIN keyword 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 RIGHT JOIN keyword.

Download
You can download the full source code of this example here: SQL RIGHT JOIN Keyword

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
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button