Suggested Pages

Monday, October 8, 2012

Join Types - Tutorial

The most important join types are:
  • Cross Join
  • Inner Join
  • Outer Join
In this page let's consider the following tables:
  1. Player ( id, firstname , surname , salary ,  team) 
  2. Team  ( id , name , city)

Cross Join

The result of a Cross Join is a carthesian products of all table rows. Examples of cross join are the followings:
  • SELECT * FROM Player CROSS JOIN Team (Explicit Cross Join)
  • SELECT * FROM Player,Team (Implicit Cross Join)
As we can see It's not specified any join predicate for matching.

Inner Join

The inner join compares each row of the Employee table with a row from Department table and verifies if the join predicate is satisfied. If it is satisfied the two table rows are combined in one result row. From another point of view the result of an inner join can be seen as the result of a Cartesian product that is filtered through the join predicate.
Examples of inner join are the followings:
  • SELECT * FROM Player player INNER join Team team ON player.team=team.id (Explicit Inner Join)
  • SELECT * FROM Player player, Team team WHERE player.team=team.id (Implicit Inner Join)

Outer Join

An Outer Join produces a row in the result table even if a matching is not found between two rows compared. It acts as a union of the two tables. You have to distinguish from three types of Outer Join: left,right and full. Examples of outer join are the followings:
  • SELECT * FROM Player player LEFT OUTER JOIN Team team ON player.team = team.id
  • SELECT * FROM Player player RIGHT OUTER JOIN Team team ON player.team = team.id
  • SELECT * FROM Player player FULL OUTER JOIN Team team ON player.team = team.id

No comments :

Post a Comment

Suggested Pages