SQL LEFT JOIN Keyword

YatinOctober 20th, 2021Last Updated: October 19th, 2021

0 34 3 minutes read

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

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

Fetch all values from the LEFT table
Fetch matching roes from both tables based on the condition
Sets the value of every column from the RIGHT table to NULL if unmatched with the LEFT table
Represented by the syntax – SELECT * FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

sql left join - venn diagram — Fig. 1: SQL LEFT JOIN Venn Diagram

2. SQL LEFT JOIN Keyword

Let us dive into some practice implementation.

2.1 Postgres Setup

Usually, set 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 left join - postgres on docker — Fig. 2. Postgres on Docker

2.2 Creating a Sample database

To implement this tutorial I am using the sample database provided by Postgres. 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 left table, rows that match a condition in both the tables. The inventory_id column for few rows will be set to null if no match is found
The second query we will have the WHERE clause to find the films that are not in the inventory

SQL Script

SELECT f.film_id, f.title, inv.inventory_id FROM film f LEFT JOIN inventory inv ON inv.film_id = f.film_id ORDER BY f.title;

SELECT f.film_id, f.title, inv.inventory_id FROM film f LEFT JOIN inventory inv ON inv.film_id = f.film_id WHERE inv.film_id is NULL ORDER BY f.title;

3. Summary

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

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

SQL LEFT JOIN Keyword