Join Conditions – Natural Join, On Condition, Using Attribute

Join Conditions:

Signifies the following points

  • The predicate that shall get applied while joining the tables. The predicate shall get used in evaluating whether a row is matched or not.
  • Join condition also signify list of columns that are allowed in the result set.

Natural Conditions:

  • In order to apply this join condition on any two given relations, the pre-requisite is the relation should have at least one common column.
  • When this condition is applied, always the predicate would be generated based on the common columns with equal to comparison operator only and hence this is also called Equi join.
  • when this join condition gets applied always the duplicates of the common columns get eliminated from the result.

ON Conditions:

  • When this join condition gets applied, the predicate would get generated by making use of any of the possible comparison operator . Hence it is also called as theta θ Join.
  • When this join condition gets applied none of the columns of the relation will get eliminated in the result and fully qualified naming techniques gets used.
  • In order to apply this join condition, on any two tables they need not to have any common column.

Using Attribute Conditions:

  • This join condition is special case of natural join condition. In case of using attribute condition we could either use one or some or all common column in the join predicate.
  • When all the common columns are used in the join predicate then the result would be same as Natural join.
  • In the result of the join the duplicates of the columns used in the predicate gets eliminated.