Skip to Content

Hi All,

In this blog post Getting to know about different types of joins supported in Universe Designer / Design Tool. Actually this topic is being posted as many people have asked since long time, because every one know about types of joins in SQL, but the questions arise here; how many different types of joins are available in Universe design tool.

Let’s see below following types of joins in Universe Designer/ Design Tool:

Join Types Description
Equi-Joins / Inner Join This Join based on the equality or matching between the values in the column of one table and the values in the column of another. (Syntax:
SELECT column_list
FROM table1, table2
WHERE table1.column_name =
table2.column_name;).
Outer Joins Link two tables, one of which has rows that do not match those in the common column of the other table. (Syntax: Select *FROM table1, table2 WHERE conditions [+];)
Left Outer Join All records fetches from first table with matching rows from second. (Syntax: Select *
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;)
Right Outer Join All records fetches from second-named table with matching rows from left. (Syntax: Select *
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;)
Full outer join combines the results of both left and right outer joins and returns all (matched or unmatched) rows. (Syntax: SELECT *
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;).
Theta Joins (conditional joins) A theta join is a join that links tables based on a relationship other than equality between two columns. A theta join could use any operator other than the “equal” operator. (For Example: By using the operator “Between”, you can create a theta join for maximum age range and minimum age ranges.)
Shortcut join This type of Join provides an alternative path between two tables. shortcut joins improve the performance of a query by not taking into account intermediate tables.
Self restricting joins join 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. (Syntax: SELECT a.column_name, b.column_name…
FROM table1 a, table1 b
WHERE a.common_filed = b.common_field;)

Cheers,

Amrendra

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply