Register now or log in to join your professional community.
: Joins are used in queries to explain how different tables are related.Joins also let you select data from a table depending upon data from another table.Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs.OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS
Depending on condition existence
Depending on whether we add any join condition or not there are following join types (yellow in model):
CROSS JOIN (synonyms also CARTESIAN JOIN, CARTESIAN PRODUCT) - there isn't any join condition or it is always true. All other join types degrade to CROSS JOINS as soon as join condition (-s) is (are) always true.
JOIN WITH RESTRICTION - there is applied join condition to joined tables. One can write join with restriction using different syntactic notations. As already said above every join with restriction may degrade to cross join.
NATURAL JOIN - syntactic notation joining source tables on all columns having the same name. This can be quite dangerous as explained below in the chapter for Natural join. Natural joins always are Equi joins.
QUALIFIED JOIN - user has possibility to define which columns are used in join condition.
NAMED COLUMNS JOIN - syntactic notation joining source tables on user defined columns having the same name. This is less dangerous than Natural join and just short form of writing Equi joins on some common columns joined together. Named columns joins always are Equi joins.
CONDITIONAL JOIN - fully controllable syntax by user. This is the most widespread and most useful syntactic convention. Depending on used predicates in join condition it may be Equi join as well as Non-equi join.
Depending on row selection
Depending on whether only rows satisfying join condition are selected or all rows are selected in one or both involved tables, joins are divided into (green in model):
INNER JOIN - only rows satisfying selection criteria from both joined tables are selected.
LEFT OUTER JOIN - rows satisfying selection criteria from both joined tables are selected as well as all remaining rows from left joined table are being kept along with Nulls instead of actual right joined table values.
RIGHT OUTER JOIN - rows satisfying selection criteria from both joined tables are selected as well as all remaining rows from right joined table are being kept along with Nulls instead of actual left joined table values.
FULL OUTER JOIN - rows satisfying selection criteria from both joined tables are selected as well as all remaining rows both from left joined table and right joined table are being kept along with Nulls instead of values from other table.
Depending on comparison operator
Depending on used comparison operator in join condition there are following join types (blue in model):
PREDICATE OPERATOR TYPE - based on predicate operator type (i.e. equality and everything other) joins are divided into two parts Equi joins and Nonequi joins.
EQUI JOIN - join condition uses only equality predicate "=". It can be both explicit for Conditional join and implicit for Natural join and Named columns join.
THETA (NONEQUI) JOIN - everything other than equality predicate "=", for example ">=", between. This can be used only by Conditional joins.
Depending on used tables
There is one special case for classification based on what tables are involved in join (orange in model).
SELF JOIN - table is joined to itself.
Do you need help in adding the right keywords to your CV? Let our CV writing experts help you.
