sql

SQL LEFT JOIN Keyword

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

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