Inner, Left, Right and Full Outer Join of Two Entities (Tables)
There are multiple options on how to join rows of two entities into a single result set. It depends on whether you want to have all data from the left entity or right entity, both entities, or you would like to get the data that are present in both entities.
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).
Inner Join vs Outer Join, Left Join vs Right Join
The diagram below shows the various join options for two tables represented by rectangles. The join is highlighted with a colored rectangle. The picture shows inner join, full outer join, left join and right join.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
New Comment