A Lap Around SQL Joins

Many people have been published their articles on SQL Joins, but this time I just want to provide a simple and straight forward explanation so that it would be very easy to understand.

JOIN: We use a JOIN clause to combine records from two or more table in a database. In other words, whenever we want to combine fields from two tables using common values in each table.

Types of Joins

The following figure depicts various types of JOINS.


Let's have a quick look at the following Venn diagrams to understand the concepts of “Inner Join & Outer Joins”.

(We can see here “Left” represents a Left table and “Right” represents a Right table.)

How to identify the left and right tables

"The Left table is the first table in the select.“


Result:

• Inner Join {D, E}
• Left Join {A, B, C, D, E}
• Left Join with NULL {A, B,C}
• Right Join {F, G, H, D, E}
• Right Join with NULL {F,G,H}
• Full Outer Join {A, B, C, D, E, F, G, H}
  

Let us consider the following two tables to understand the preceding concepts.

Table 1 name: tblRankings



Table 2 name: tblMVPDetails

• Inner Join: Inner join returns only those records that match in both of the tables.
  

Example:



Output:

• Outer Join

Left outer join: It returns all the rows from left table (Table 1) and matching rows from right table (Table 2). It returns null when there is no match in the right side.

Example:



Syntax:


Output:



Right outer join: It returns all the rows from right table () and matching rows from left able (Table 1). It returns NULL when there is no match in the left side.

Example:



Syntax:



Output:



Full outer join: It returns all the rows from the left table (Table 1) and the right table (Table 2), it is the result of the combination of left and right joins.

Example:

Syntax:


Output:

• Cross Join: It returns a result set that is the number of records in left table (Table 1) is multiplied by the number of records in the right table (Table 2).
  
  This result is also called a Cartesian product.
  
  
  

The result of this cross join is: {(A,D),(A,E),(A,F),(B,D),(B,E),(B,F),(C,D),(C,E),(C,F)};

Example: