PostgreSQL JOINIn this section, we are going to understand the working of several types of PostgreSQL joins, such as Inner join, Left join, Right join, and Full Outer join in brief. PostgreSQL JOINS are used with SELECT command, which helps us to retrieve data from various tables. And we can merge the Select and Joins statements together into a single command. Whenever we want to get records from two or more tables, we will execute the joins commands. It is used to merge columns from one or more tables according to the data of the standard columns between connected tables. Usually, the standard columns of the first table are primary key columns and the second table columns are foreign key columns. In PostgreSQL, we have various types of joins which are as follows:
The below image displays most importantly used PostgreSQL joins, which we are going to explain in this section of the PostgreSQL tutorial. Example of PostgreSQL JoinsLet us see some examples of different types of PostgreSQL joins: Here, we will be creating and inserting the two different tables where we perform actions on several types of joins: In the below example, we will use the Create command to create a Luxury_cars table. Output Once we execute the above command, we will get the below message, which displays that the Luxury_cars table has been created successfully. Here again, we will use the Create command to create a Sports_cars table as follows: Output Once we executed the above command, we will get the below message, which displays that the Sports_cars table has been created successfully. After that, we will insert some values in the Luxury_cars table by using the INSERT command: Output After executing the above command, we will get the below message that the values have been inserted successfully into the Luxury_cars table. Just like we inserted the value in the Luxury_cars table, we will insert the values into the Sports_cars table as well with the help of Insert command: Output After executing the above command, we will get the below message that the values have been inserted successfully into the Sports_cars table. The above tables have some similar cars, for example, Chevrolet Corvette and Mercedes Benz SL Class. Now we will use the SELECT command to get the following data from the Luxury_cars table: Output After executing the above command, we will get the below output: We will get the following data from the Sports_cars table by using the SELECT command: Output After executing the above command, we will get the below result: Now, let us see the working of different types of PostgreSQL Joins in real-time: PostgreSQL Inner joinThe PostgreSQL INNER JOIN is used to return all rows from various tables where the join condition is fulfilled. Syntax of PostgreSQL Inner Join The below visual representation shows the working of PostgreSQL inner join: For Example We will take the above tables (Luxury_cars and Sports_cars) to understand the PostgreSQL inner join. The below command will join the first table (Luxury_cars) with the second table (Sports_carsv) by matching the values in the luxury_car_name and sports_car_name columns: Output Once we implemented the above command, we will get the below result where we can see the matched rows data from Luxury_cars and Sports_cars tables.
PostgreSQL Left joinThe PostgreSQL LEFT JOIN is used to return all rows from the left table, which can define in the ON condition and only those rows from the other table where the join condition is satisfied. Syntax of PostgreSQL Left join The below visual representation displays the working of PostgreSQL Left join: For Example In the below command, we are going to use the Left Join condition to join the Luxury_cars table with the Sports_cars table. In the Left join clause, Table A or the first table is known as the Left table, and Table B or the second table is known as the Right table. Output Once we implemented the above command, we will get the below result. Working of PostgreSQL Left Join
PostgreSQL Left Join with Where clauseWe can also use the left join with a WHERE condition. In the below example, we will select rows from the left table (Luxury_cars), which does not contain similar rows in the right table (Sports_cars): Output Once we implemented the above command, we will get the below output, which displays those records whose S_ID is NULL. Note: We can use both the LEFT JOIN and LEFT OUTER JOIN equivalently because the working of Left join and Left outer join are similar.The below visual representation explains the Left join that returns rows from the Left table, which does not contain the similar rows in the right table: PostgreSQL RIGHT JOINThe PostgreSQL RIGHT JOIN is used to return all rows from the Right table, which can define in the ON condition and only those rows from another table where the join condition is fulfilled. The RIGHT JOIN will get the data from the right table as it is the opposite of the LEFT JOIN. Syntax of PostgreSQL Right Join The below Venn diagram displays the working of PostgreSQL Right join: For Example The below command is used to represent the working of Right join where we join the Luxury_cars table with the Sports_cars table: Output After executing the above command, we will get the below output: Working of PostgreSQL Right join
PostgreSQL Right Join with Where clauseIn the same way, we can also use the right join with a WHERE condition. For example, we will use the where clause to select rows from the right table (Sports_cars), which does not contain similar rows in the left table (Luxury_cars): Output After executing the above command, we will get the below output, which displays those records whose L_ID is NULL. Note: We can use both the RIGHT JOIN and RIGHT OUTER JOIN equivalently because the working of Right Join and Right outer join are similar to each other.The below visual representation explains the Right join that returns rows from the Right table, which does not contain the similar rows in the left table: PostgreSQL Full Outer JoinThe FULL OUTER JOIN is used to return all records when there is a match in the left table or right table records. Syntax of PostgreSQL Full Outer Join The below Venn diagram displays the working of PostgreSQL Full Outer join: For Example The below command is used to represent the working of the Full Outer join to join the Luxury_cars table with the Sports_cars table. Output After executing the above command, we will get the below result: PostgreSQL Full Outer Join using where clauseThe below Venn diagram displays the full outer join that returns rows from a table, which does not contain the matching rows in the other table: To return rows in a table that do not have matching rows in the other, we will use the full outer join with a WHERE clause like this: Output Once we execute the above command, we will get the below result: OverviewIn this section, we have learned the working of several kinds of PostgreSQL joins, which combine data from various connected tables. Next TopicPostgreSQL INNER JOIN |