PostgreSQL Joins are used to efficiently extract data from multiple tables. The PostgreSQL Join command can be merged with the PostgreSQL Select command into a single command. When we need to extract data from more than one table by using a single command or query, we use PostgreSQL Joins.
There are different types of joins supported by PostgreSQL, some of them are:
- Inner join
- Left join
- Right join
- Cross join
- Full outer join
- Natural join
The main focus of PostgreSQL Joins is to combine columns from one (in case of self-join) or more tables depending upon the values of the common columns between those related tables. The common columns are mostly the primary key columns of the primary table and foreign key columns of the secondary table.
To have a better comprehension, let us see each one of them in detail.
PostgreSQL Inner Join:
The default join in PostgreSQL is PostgreSQL Inner Join. PostgreSQL Inner Join is used to display only those rows from the multiple tables where the condition that we have specified in the query is satisfied and it hides the other rows.
The PostgreSQL Inner Join is used with the SELECT command is usually written after the from clause.
The PostgreSQL Inner Join clause has syntax like this:
SELECT [list_of_column|*] FROM tableONE INNER JOIN tableTWO ON tableONE.column_name=tableTWO.column.name;
We will understand the usage of the Inner join clause with the help of an example.
We have created two tables by the name student and committee where in the student table has 3 columns named studID, name, and age and in the committee table, we have two columns studID and name and there are 44 rows in each of the tables.
Now we will apply inner join on these tables and get the desired output. We want to display only name and studID of those students who are also a member of the committee so for this let us write PostgreSQL query having inner join clause within it.
As shown in the image, the output of the insert query result is only two rows that are common between the students and committee table. So, we can say that with the help of the inner join clause, we are successful to extract only those rows from these two tables which are common between both of them. In our example, the studID column of the student and the committee table is used in the condition of the join clause.
Usually, the tables that we use in PostgreSQL inner join clause have at least one common column that we use for the comparison of the rows in the condition specified in the query.
And if we directly refer to the name of the columns that are common in both of the tables, we will get an ambiguity error. To avoid this ambiguity error, we use Table-aliasing which means we use table name before referring to the column.
The syntax for the table aliasing is:
These table aliases are used to assign the names to the joined tables to make the command more readable to the programmer.
PostgreSQL Left Join:
PostgreSQL Left Join is very much similar to the PostgreSQL Inner Join clause. The main difference between them is that the PostgreSQL Left Join displays all the rows that are present in the left table of the command and displays only those rows from the right table for which the condition that is specified in the ON condition is satisfied.
The syntax for the PostgreSQL Left Join is:
SELECT columns FROM tableONE LEFT [OUTER] JOIN tableTWO ON tableONE.column = tableTWO.column;
There is also PostgreSQL Right Join that displays all the rows from the right table and will display only specific rows from the left-hand side table according to the condition.
The syntax for the PostgreSQL Right Join is:
SELECT columns FROM tableONE Right [OUTER] JOIN tableTWO ON tableONE.column = tableTWO.column;
Let us consider an example to understand it in a better way:
As shown in the image, we have used the PostgreSQL Left Join clause as a result of which all the rows from the student table are displayed because those two rows are present in the committee table.
So, this article helps us to understand PostgreSQL joins in a better way.