Monday 12 September 2016

SQL Joins

  • Join is useful to fetching records from multiple tables with reference to common column between them.
  • SQL Joins are used to retrieve data from multiple tables.
  • Join keyword is used in SQL queries for joining two or more tables.
Types of Joins:





Inner Join:
  • Select the records that have matching values from the both tables.
  • Whenever we use Inner Join clause, we normally think about intersection. 
Syntax:
Select table1.column1, table2.column2...
From table1 
Inner Join table2 
On table1.common_field = table2.common_field;



Left Outer Join:
  • It returns a  result table with all records from the left table with only matching records from the right table.
Syntax:
Select table1.column1, table2.column2...
From table1 
Left Join table2 
On table1.common_field = table2.common_field;



Right Outer Join:
  • It returns a result table with only matched records from the left table and all records from the right table.
Syntax:
Select table1.column1, table2.column2...
From table1 
Right Join table2 
On table1.common_field = table2.common_field;



Full Outer Join:
  • The Full Outer Join returns a result table with the matched data of two tables then remaining rows of both left table and right table.
  • Whenever we use Full Outer Join clause, we normally think about Union. 
Syntax:
Select table1.column1, table2.column2...
From table1 
Full Join table2 
On table1.common_field = table2.common_field;



Cross Join:
  • A Cross Join that produces Cartesian product of the tables that are involved in the join.
  • The size of a Cartesian is the number of the rows in the first table multiplied by the  number or rows in the second table.
Syntax:
Select * from table1 Cross Join table2;
or
Select * from table1, table2;




Self Join:
  • Joining the table itself is called Self Join.
  • Self Join is used to retrieve the records having some relation or similarity with other records in the same table.
  • Here, we need to use aliases for the same table to set a self join between single table and retrieve records satisfying the condition in where clause.
Syntax:
Select a.cloumn_name, b.column_name...
From table1 a, table1 b
Where a.common_field = b.common_field;