PostgreSQL LEFT JOIN/ LEFT OUTER JOIN: Syntax, Working & Examples

Have you ever considered using multiple tables simultaneously or accessing a single table so that several rows are handled simultaneously? If so, you’re headed in the right direction!

JOIN queries are queries that simultaneously query several tables (or many instances of the same table). 

In this article, we will quickly learn about JOIN Queries, including their types, syntax, and usage, before diving deep to gain fascinating insights about PostgreSQL LEFT JOIN/LEFT OUTER JOIN. To learn more about PostgreSQL, click here.

Now, let’s get started!

What are JOINS in PostgreSQL?

In PostgreSQL, when there is a need to extract the data from one or more tables, JOIN Queries are used to access data from multiple tables.

JOIN Queries are queries that simultaneously query several tables (or many instances of the same table). They combine rows from multiple tables together, with an expression indicating which rows should be paired.

We can derive a Joined Table from two other tables specifying the JOIN TYPE(Inner, Outer, Cross). The syntax of generating a Joined Table is as follows, where T1 is Table 1 & T2 is Table 2:

T1 join_type T2 [ join_condition ]

We may extract data from multiple tables using the SELECT command, and PostgreSQL JOINS. Additionally, we can combine the SELECT and JOINS statements into a single command. We will run the JOINS commands whenever we need records from two or more tables.

For Instance: T1: Climate & T2: States
To retrieve the Climatic records along with the location of the associated State, the database will compare the state column of each row of the climate table with the name column of all rows in the States table and only select those rows where these values match. 

SELECT * FROM climate JOIN states ON state= name;

Note: The common column is usually a primary key in T1 and a foreign key in T2.

PostgreSQL JOIN with its Types

Records from two or more tables are combined in a database using the PostgreSQL JOINS clause. By leveraging data shared in both the tables, a JOIN allows you to retrieve data from two tables. This section will briefly discuss how several PostgreSQL JOIN types, including CROSS JOIN, INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL OUTER JOIN.

JOIN Types in PostgreSQL are −

• The CROSS JOIN
• The INNER JOIN
• The LEFT OUTER JOIN
• The RIGHT OUTER JOIN
• The FULL OUTER JOIN

The CROSS JOIN

PostgreSQL CROSS JOIN matches each row of the first table and each row of the second table & showcases all the columns of both tables. If T1 has n1 columns and T2 has n2 columns, then the resultant Joined table will have (n1+n2) columns.

Syntax:

SELECT column1, column2, …  FROM T1 CROSS JOIN T2
or
Select (*) from T1 CROSS JOIN T2

The INNER JOIN

PostgreSQL INNER JOIN is also termed SELF-JOIN. It is the most common & widely type of JOIN used in PostgreSQL. It retrieves & returns all the matching rows from multiple tables when the JOIN condition is met.

Syntax:

SELECT T1.column1, T2.column2...
FROM T1
INNER JOIN T2
ON T1.common_filed = T2.common_field;

The LEFT OUTER JOIN or LEFT JOIN

PostgreSQL LEFT JOIN performs a regular JOIN before it starts to scan the left Table(T1). PostgreSQL LEFT JOIN extracts all the rows from the left Table & all the matching rows from the right Table(T2). In case there are no matching rows, null values will be generated.

Syntax:

Select columns from table_name1 LEFT OUTER JOIN table_name2 on table_name1.column = table_name2.column;
or
SELECT (*) FROM table_name1 LEFT OUTER JOIN table_name2 on table_name1.column = table_name2.column;

The RIGHT OUTER JOIN or RIGHT JOIN

PostgreSQL RIGHT JOIN performs a regular JOIN before it starts to scan the right Table(T1). PostgreSQL RIGHT JOIN extracts all the rows from the left Table & all the matching rows from the left table(T2). In case there are no matching rows, null values will be generated.

Syntax:

Select columns from table_name1 RIGHT OUTER JOIN table_name2 on table_name1.column = table_name2.column;
or
SELECT (*) FROM table_name1 RIGHT OUTER JOIN table_name2 on table_name1.column = table_name2.column;

The FULL OUTER JOIN

PostgreSQL FULL OUTER JOIN retrieves all rows from both the left & the right table. It will return null values when the condition is not met. PostgreSQL FULL OUTER JOIN performs a regular JOIN before combining left and right. 

Syntax:

Select columns from table_name1 FULL JOIN table_name2 on table_name1.column = table_name2.column;
or
SELECT (*) FROM table_name1 FULL JOIN table_name2 on table_name1.column = table_name2.column;

What is PostgreSQL LEFT JOIN & LEFT OUTER JOIN?

The LEFT JOIN and LEFT OUTER JOIN are used interchangeably. PostgreSQL LEFT JOIN retrieves all rows from the left table(T1) and only matched rows from the right table where the ON clause condition is satisfied. In case there are no matching rows, null values will be generated. Read along to learn more about PostgreSQL LEFT JOIN.

Syntax

• Syntax(PostgreSQL LEFT JOIN):

Select *
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

• Syntax(PostgreSQL LEFT OUTER JOIN):

Select *
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

Explanation:
Step 1: Select the columns of Table1 from where the data is to be retrieved.
Step 2: Define Table1 in the from Clause.
Step 3: Define Table2 in the LEFT[OUTER] JOIN clause.
Step 4: Define the condition to perform the LEFT OUTER JOIN.

Note: The combination of Table1 & Table2 for the rows that satisfy the described condition is retrieved. In the following pictorial representation, the highlighted area is obtained as the resultant of PostgreSQL LEFT JOIN in the following visual representation.

How does PostgreSQL LEFT JOIN or LEFT OUTER JOIN work?

PostgreSQL LEFT JOIN or LEFT OUTER JOIN works in the following manner:

• Takes the selected values from the left table(T1)
• Combine the selected data with the column names in the right table(T2) specified in the condition.
• Retrieves the pair of matching rows from both tables.
• Null values are assigned for every column in the right table which does not match the left table.

PostgreSQL LEFT JOIN or LEFT OUTER JOIN: Examples

To get hands-on experience working with PostgreSQL LEFT JOIN or LEFT OUTER JOIN, you must create a table to run queries on. Here’s a detailed guide that you can refer to for creating PostgreSQL Tables. 

Example #1

Let’s consider the Item Table & Invoice Table as the sample tables on which we’ll perform PostgreSQL LEFT JOIN.

Sample Table 1: Item

item_no	item_descrip	rate
I1	Pepsi	10
I2	Butter	25
I4	Bread	9
I8	Biscuit	18

Sample Table 2: Invoice

invoice_no	cust_no	item_no	sold_qty	disc_per
110	C1	I2	2	0.25
111	C2	I4	3	0.00
112	C3	I1	4	NULL
113	C4	I1	2	0.40

Code:

• Syntax #1

SELECT item.item_no,item_descrip,
invoice.invoice_no,invoice.sold_qty
FROM item
LEFT JOIN invoice
ON item.item_no=invoice.item_no;

• Syntax #2

SELECT item.item_no,item_descrip,
invoice.invoice_no,invoice.sold_qty
FROM item
LEFT OUTER JOIN invoice
ON item.item_no=invoice.item_no;

Output:

item_no	item_descrip	invoice_no	sold_qty
I1	Pepsi	113	2
I1	Pepsi	112	4
I2	Butter	111	2
I4	Bread	110	3
I8	Biscuit	NULL	NULL

Explanation:

Note: In the example above, the item_no I8 from the item table does not exist in the invoice table. Hence a new row has been constructed in the invoice table and set to NULL(as explained in the working of PostgreSQL LEFT JOIN).

Example #2:

Let’s consider the Transaction Table & Invoice Table as the sample tables on which we’ll perform PostgreSQL LEFT JOIN.

Sample Table 1: Transaction

	transaction_id	transaction_data
1	1	Purchase of Mobile
2	2	Purchase of PC
3	3	Purchase of Headphone

Sample Table 2: Invoices

	invoice_id	transaction_id	invoice_data
1	1	1	Purchase of Mobile
2	2	1	Purchase of Mobile
3	3	2	Purchase of PC
4	4	2	Purchase of PC
5	5	3	Purchase of Headphone
6	6	3	Purchase of Headphone

Code:

• Syntax#1

SELECT
transaction.transaction_id,
transaction.transaction_data,
invoice_data
FROM
transaction
LEFT JOIN invoices ON invoices.transaction_id = transaction.transaction_id;

• Syntax#2

SELECT
transaction.transaction_id,
transaction.transaction_data,
invoice_data
FROM
transaction
LEFT OUTER JOIN invoices ON invoices.transaction_id = transaction.transaction_id;

Output:

	transaction_id	transaction_data	invoice_data
1	1	Purchase of Mobile	Purchase of Mobile
2	1	Purchase of Mobile	Purchase of Mobile
3	2	Purchase of PC	Purchase of PC
4	2	Purchase of PC	Purchase of PC
5	3	Purchase of Headphone	Purchase of Headphone
6	4	Purchase of Headphone	Purchase of Headphone

Explanation:

The output obtained after performing the PostgreSQL LEFT JOIN or LEFT OUTER JOIN is shown above. Here we can see that the combined data which matches the discussed criteria(explained in the working of PostgreSQL LEFT JOIN or LEFT OUTER JOIN) is retrieved.

Conclusion

This article has successfully taken you through the detailed guide of PostgreSQL LEFT JOIN. You have learned about Postgres left JOINS, including its types and their syntax. Practical examples have also been provided to make your learning experience enriching.
This guide is undoubtedly a one-stop-destination for those seeking to begin their hands-on experiment with PostgreSQL. 

Are you ready for another enriching deep dive? Check out these interesting articles at Hevo:

• PostgreSQL Cast Function 101: How to Convert One Data Type to Another? 
• Creating PostgreSQL Tables: A Comprehensive Guide 101
  

If you’re an avid user of PostgreSQL, copying data into a warehouse using ETL for Data Analysis might be an extensive task. Hevo, A No-Code Data Pipeline, is at your service for your rescue. You can save your engineering efforts by setting up a Data Pipeline and start replicating your data from PostgreSQL to your desired warehouse in a matter of minutes using Hevo. 

Hevo, a No-code Data Pipeline, provides you with a consistent and reliable solution for seamless data replication from a wide variety of Sources & Destinations — that, too, in just a few clicks!

VISIT OUR WEBSITE TO EXPLORE HEVO

Want to give Hevo a try? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You may also look at the amazing price, which will assist you while selecting the best plan for your requirements.

Share your experience understanding the PostgreSQL LEFT JOIN in the comment section below! We would love to hear your thoughts.