A Lap Around SQL Joins

• 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: