PostgreSQL

Left Join PostgreSQL

PostgreSQL Join is a feature that allows you to combine columns from one table with one or more tables based on the common columns between the related tables. There are many types of Joins, such as Inner Join, Outer Join, Cross Join, and Self Join. This article will define the Left Join into two types:

  • Left Outer Join
  • Left Inner Join

We will explain each type with specific examples.

Syntax

SELECT table_a.columna, table_a.columnb, table_b.columna
FROM table_a
LEFT JOIN table_b
ON table_a.matching_column = table_b.matching_column;

Table_a implies the first table, and table_b implies the second table. The matching_column is the column that is present in both tables could be with different column names.

  • The syntax shows that we need to specify two tables by using the select statement.
  • Second, we show the main table.
  • Third, we mention the table and the main table has formed a join with.

To accomplish the understanding of Left Join, we need to create two tables in PostgreSQL. Both tables are created by using the “Create table” command. The sample statement is given below. Let the table name “item”:

>> create table items (id integer, name varchar(10), category varchar(10), order_no integer, address varchar(10), expire_month varchar(10));

Now add the values in the table by “insert statement”:

>> insert into items values ( 1, ‘Doll’, ‘toy’, 4, ‘address’);

By using this command, the values are entered in a single row of the table.

Now, going back to the example, we have two tables here, one is “items”, and the other one is “Orders”. You can see the content of tables by using the “select” command:

Table 1-Items : 

Table 2-Orders:

Example 1:
Consider both tables. These tables are created in such a way that one column forms a link between these two tables. For instance, order_no plays the role of establishing the link. The following  command uses the clause of a Left Join to join the “items” table with “orders”:

>> select items.id, name , order_id FROM items LEFT JOIN Orders ON Orders.order_id = items.order_no ORDER BY name;

Here, we get the resultant table as we selected “id” and “name” from the items table and order_no from the Orders table, so these three columns are displayed. When a row from the items table has no matching row with the Orders table, then the values of the order_id column of that row is NULL.

 Example 2:
This example resembles the previous one but differs in having a “where” clause. This helps in finding the orders that are not present in the Orders table. This is accomplished by using an additional statement that shows the column empty by comparing the values with space. If no value is present, select the id and name. This example is related to the Outer Left Join. Outer Left Join is the one in which, while getting the resultant value after applying constraints, all the elements belong to a single table (Left table). If a single element is matched, the result is far from the Outer Left Join:

>> select items.id, items. name, order_id FROM items LEFT JOIN Orders  ON Orders.order_id = items.order_no WHERE Orders. id is NULL ORDER BY name;

The image shows that all the six rows in the “items” table are different from the “Orders” table. Or “Orders” table doesn’t contain these values. That’s why the whole column is empty.

Example 3:
This example shows the link between two tables directly. As in the select statement, we take two columns from table items and one column from the table Orders. But for this concept, we need to mention the column name with the table name by the “dot-method”, which specifies the column of a particular table. This is an Inner Left Join example because some portions of both the tables are present in the resultant table:

>> select name , category , orders.address  FROM items LEFT JOIN Orders  ON Orders.order_id = items.order_no,

Hence, the resultant table will be the combination of columns from both tables. The address column will contain only the address of the “Orders” table that matches with the address of the “items” table.

Similarly, if we change the “dot-method” statement, it makes a difference. In this example, we have replaced “orders.address” with the address column in the items table “items.address”:

>> select name , category , items.address  FROM items LEFT JOIN Orders  ON Orders.order_id = items.order_no,

Now, you can observe that the address column has all rows filled with data present in the “items” table.

Example 4:
More than two columns from each table are selected here. Sometimes, we need to reduce redundancy to avoid duplicity of data while combining two tables. This can only be done by using constraints on more than one column to retrieve precise and valuable data that is coherent in both tables. So in this example, we want to display the name, address, and order_no that is only present in the items table and not in the “orders” table:

>> select name, orders_no , order_id, items.address  FROM items LEFT JOIN Orders  ON Orders.order_id = items.order_no where Orders is NULL;

To get the entire column vacant, we have applied the constraint of NULL. In this example, we have again specified the address with the items table to allow the discrimination with the address column. But in the coming example, we have replaced the address with the ‘order_day’ from the “Orders” table. After replacing the column, both the order_id and order_day are vacant columns in the resultant table:

>> select name, orders_no , order_id, orders_day  FROM items LEFT JOIN Orders  ON Orders.order_id = items.order_no where Orders is NULL;

We have mentioned only the order_id to be null. But why is the “order_day” empty?

It is an obvious factor that if we have chosen only those rows of orders_no that are not present in the “orders” table so how it could be possible to have those “order_day” values whose order_id’s are not present.

Implementation via pgADMIN

To implement the commands on the pgAdmin side, you need to provide the password in the beginning. Now, navigate to the left bar. You will come to know about the databases in the server. Expand the Postgres database, as we have implemented our queries here. After expansion, you will get the catalogs, including table data:

Now, right-click on the table and select the query tool:

We take the same example as described above just to create the pgAdmin in use. Write the following query in the snap and in the query editor portion. To execute the command, move to the navigation toolbar mentioned above. Here is the symbol like the symbol of play in music. Select this, and the query will be executed. It is shown in the Data Output:

Conclusion 

The PostgreSQL Join combines columns from different tables with similar columns. In particular, the Left Join combines the table on the left side. It means that the right table is mentioned first in the command and then, the other one. All the conditions are applied on the Left Join either in the Inner Join or the case of Outer Join. We hope you found this article helpful in using the Left Join PostgreSQL. Please check out the other articles for additional information.

About the author

Aqsa Yasin

I am a self-motivated information technology professional with a passion for writing. I am a technical writer and love to write for all Linux flavors and Windows.