Thursday, November 15, 2012

SQL Joins

In this article, we’ll see the basic concepts of SQL JOINs.The JOIN keyword is used in a SQL statement to query data from two or more tables based on a relationship between certain columns in these tables.
Inner JOIN
A JOIN that displays only rows that have a match in both the JOINed tables is known as inner JOIN. This
is the default type of JOIN in the Query and View Designer.


 
Outer JOIN
A JOIN that includes rows even if they do not have related rows in the joined table is an Outer JOIN. You
can create three different outer JOINs to specify the unmatched rows to be included:
Left Outer JOIN: In Left Outer JOIN, all rows in the first-named table, i.e. “left” table, which appears
leftmost in the JOIN clause, are included. Unmatched rows in the right table do not appear.


 

Right Outer JOIN: In Right Outer JOIN, all rows in the second-named table, i.e. “right” table, which
appears rightmost in the JOIN clause, are included. Unmatched rows in the left table are not included.


 
Full Outer JOIN: In Full Outer JOIN, all rows in all the joined tables are included, whether they are
matched or not.


 
Cross JOIN
A cross JOIN devoid of a WHERE clause produces the Cartesian product of the tables involved in the JOIN.
The size of a Cartesian product result set is the number of rows in the first table multiplied by the number
of rows in the second table. One common example is when a company lists all its products in a pricing
table to compare each product with others prices.


 

Self-JOIN
In this particular case, one table JOINs to itself with one or two aliases to stave off confusion. A self-JOIN
can be of any type, as long as the joined tables are the same. A self-JOIN is unique in the sense that it
involves a relationship with only one table. A common example is when a company has a hierarchal
reporting structure whereby a member of staff reports to another member. Self-JOIN can either be an
Outer JOIN or an Inner JOIN.


Note:
Before we continue further let me make it very clear that INNER JOIN should be used where it
cannot be used and simulating INNER JOIN using any other JOINs will degrade the performance. If there are scopes to convert any OUTER JOIN to INNER JOIN, it should be done with
priority.