INNER JOIN vs FULL OUTER JOIN vs LEFT JOIN vs RIGHT JOIN in PostgreSQL?

PostgreSQLDatabaseData Storage

For understanding these concepts with examples, we will consider two tables, marks, and student_info, defined respectively below −

marks −

nameroll_noperc_marks
Siddhi4565
Yash2642
Isha5687

student_info −

nameroll_noagegender
Aniket1226M
Isha5625F
Yash2625M

As you can see, the marks table doesn’t have an entry for Aniket, while the student_info table doesn’t have an entry for Siddhi. In other words, the marks table doesn’t have an entry for roll_no 12, while the student_info table doesn’t have an entry for roll_no 45. Now, let us understand the different JOINS one by one.

INNER JOIN

It returns only those rows for which entries are present in both the tables.

SELECT marks.name, marks.roll_no, student_info.age
FROM marks
INNER JOIN student_info on marks.roll_no = student_info.roll_no

The output is −

nameroll_noage
Yash2625
Isha5625

LEFT JOIN

This will include add entries of the left table, irrespective of whether a corresponding entry exists in the right table or not. In the query below, the marks table is the left table.

SELECT marks.name, marks.roll_no, student_info.age
FROM marks
LEFT JOIN student_info on marks.roll_no = student_info.roll_no

Thus, the output will contain a row for Siddhi, but not a row for Aniket, as you can see below. Since age is not present for Siddhi, its value will be null.

nameroll_noage
Yash2625
Siddhi45[null]
Isha5625

RIGHT JOIN

This will include add entries of the right table, irrespective of whether a corresponding entry exists in the left table or not. In the query below, the student_info table is the left table.

SELECT marks.name, marks.roll_no, student_info.age
FROM marks
RIGHT JOIN student_info on marks.roll_no = student_info.roll_no

Thus, the output will contain a row for Aniket, but not a row for Siddhi. The output of this query will be interesting. Since we are querying the name and roll_no from marks, these values won’t be shown for Aniket. Only the age is being queried from student_info and therefore, only that field will be shown for Aniket’s row. The output is shown below −

nameroll_noage
[null][null]26
Yash2625
Isha5625

FULL OUTER JOIN

You can think of full outer joins like a union of LEFT JOIN and RIGHT JOIN. This query will return one row for each unique entry that exists in either the right table or the left table.

SELECT marks.name, marks.roll_no, student_info.age
FROM marks
FULL OUTER JOIN student_info on marks.roll_no = student_info.roll_no

The output, as expected, contains rows for both Siddhi and Aniket.

nameroll_noage
[null][null]26
Yash2625
Siddhi45[null]
Isha5625



raja
Published on 02-Feb-2021 16:48:49
Advertisements