Yogesh Chauhan's Blog

LEFT JOIN in Postgres

in Postgres on December 18, 2020

The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.

Basically, it looks like this.

LEFT JOIN in PostgreSQL

The highlighted green part from the image above is our query results when we apply LEFT JOIN between 2 tables.

Syntax:


SELECT a.column2, b.column2
FROM table a
LEFT JOIN table b
ON a.column = b.column;

I am using alias as well in the syntax. You can use name of the table instead of alias. Alias makes it better to write the query. Learn more about alias in this post:

Column And Table Alias In Postgres

LEFT JOIN Query Example 2 tables

I am using the database for all examples. It is available on my Github public repo


SELECT c.contact_name, o.order_date 
FROM customers c 
LEFT JOIN orders o ON c.customer_id = o.customer_id

//Output
contact_name.       order_date
....
....
....
"Guillermo Fernández"    "1998-05-05"
"Jytte Petersen"    "1998-05-06"
"Michael Holz"    "1998-05-06"
"Laurence Lebihan"    "1998-05-06"
"Paula Wilson"    "1998-05-06"
"Marie Bertrand"    null
"Diego Roel"        null
...
...
832 rows

As we can see there were no matches from the right table, so the query result included the data from left table with null.

What will happen if we apply LEFT join to 3 tables?

LEFT JOIN Query Example 3 tables


SELECT c.contact_name, o.order_date, od.product_id
FROM customers c 
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN order_details od ON od.order_id = o.order_id

//Output
contact_name.    order_date.   count
...
...
...
"Paula Wilson"    "1998-05-06"    73
"Paula Wilson"    "1998-05-06"    75
"Paula Wilson"    "1998-05-06"    77
"Marie Bertrand"        null    null
"Diego Roel"            null    null
...
...
2157 rows

As we can see we are getting all the data possible from LEFT tables and for right tables, if there are not any match, it simpy returns null. If we want to filter this data more, we can add an aggregate function and add GROUP BY clause.


Most Read

#1 Solution to the error “Visual Studio Code can’t be opened because Apple cannot check it for malicious software” #2 How to add Read More Read Less Button using JavaScript? #3 How to check if radio button is checked or not using JavaScript? #4 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #5 How to uninstall Cocoapods from the Mac OS? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

#Apr 8 JSON.stringify() in JavaScript #Apr 7 Middleware in NextJS #Jan 17 4 advanced ways to search Colleague #Jan 16 Colleague UI Basics: The Search Area #Jan 16 Colleague UI Basics: The Context Area #Jan 16 Colleague UI Basics: Accessing the user interface
You might also like these
@mixin and @include in SCSS (Sass)SCSSAggregate Functions Examples in Postgres for BeginnersPostgresA few HTML coding standards from WordPressHTMLWhat is DevOps?MiscellaneousHow to create a vertically rotating texts using CSS?CSSHow to create a Recent Posts function in WordPress?WordPress