Types of joins in Universe design
Let’s have a look at the types of joins in Universe design:
Equi or Inner or Natural or Simple joins:
This is based on the equality between the values in the column of one table and the values in the column of another. Because the same column is present in both tables, the join synchronizes the two tables.
Self-join is joining a table to itself i.e. create a self-join to find rows in a table that have values in common with other rows in the same table.
Theta or Non-Equi joins:
This join links tables based on a relationship other than equality between two columns. A join is a relational operation that causes two or more tables with a common domain to be combined into a single table. The purpose of joins is to restrict the result set of a query run against multiple tables.
Outer join links two tables, one of which has rows that do not match those in the common column of the other table.
Left Outer Join:
A left outer join retains all of the rows of the “left” table, regardless of whether there is a row that matches on the “right” table.
Right Outer Join:
A left outer join retains all of the rows of the “right” table, regardless of whether there is a row that matches on the “left” table.
Full outer join:
All rows in all joined tables are included, whether they are matched or not.
Shortcut join can be used in schemas containing redundant join paths leading to the same result, regardless of direction. It allows to define an alternative, faster join path between two tables.
Suppose if we have 4 tables A,B,C and D. A—>B—->C—->D and there is a chance to link A—>D but we should not link (causes loop) and we need data from the tables A and D then it will get the data in the path A—>B—->C—->D , it consumes lot of time and resources.
In this case instead of linking A—->D using normal join we should link A and D by using short cut join and this will not create any loop and get the data in the path A—>D, it consumes less time and resources.
This improves SQL performance.