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

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

marks −

student_info −

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 −

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.

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 −

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.