Inner, Left, Right and Full Outer Join of Two Entities (Tables)

There are multiple options how you can join rows of two entities into a single result set. It depends on whether you want to have all data from left entity, right entity, both entities or you want to get common data.

There are 4 basic join options for data entities:

  • Inner Join - the result includes only the rows which are present in both entities (tables).
  • Left Join - the result includes all the rows from the left entity (table) and the only the rows from the right entity, which can be connected by the join condition.
  • Right Join - the result includes all the rows from the right entity (table) and the only the rows from the left entity, which can be connected by the join condition.
  • Full Outer Join - the result includes all the rows from both entities. The rows from the left and right entities are connected by the join condition. The rows which do not have their counterparts in the other entity contain empty row associated (array of nulls).
Overview of Join Options  (Diagram)
Overview of Join Options (Diagram)

Example for Inner, Left, Right and Full Outer Join

The example ER diagram contains two entities OldStudent (for the old database of students) and NewStudent (for the new database of students) and the joined result entities - one for each join type. Each entity has defined 3 rows.

Example of Joined ERD Entities
Example of Joined ERD Entities

New Comment

Comment