Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

What is Join and Join types in SQL Server

Author: Richard Wand

Join is used when we have to get data from two are more tables. I will explain Join and Join types in this article.

 

We store related data in different tables when we store data in databases. SQL Joins are used to retrieve this related data from two or more tables based on any relationship between tables. We can store related data in one table but because of duplication and normalization we store it in different tables. Although Joins affect performance but duplication of data required more space than storing related data in different tables. Also, normalization is necessary when we have large tables.  Join is a basic feature of SQL Server as it combines data into a table from two or more tables.

SQL Joins can be used in WHERE Clause but preferably JOIN Keyword is used to join two tables. WE have to list tables in FROM Clause when we want to join these tables. Tables are often related to each other using Key or ID of the table but it can be related with other columns. 

 

SQL Join has following six types. We will use following two tables for this example .
 
Employees
EmpID
FirstName
LastName
JobID
1
Pedro
Afonso
1
2
Philip
Cramer
2
3
Francisco
Chang
3
4
Janine
Labrune
2
5
Ann
Devon
3
 
Jobs
JobID
Description
1
Chief Executive Officer
2
Marketing Manager
3
Sales Representative
  1. Cross Join

    Cross Join is actually the Cartesian product of two tables. It combines all rows from one table to all rows from other table. If a table has 5 rows and other has 4 rows than resulting Cross Join table for these tables will have 5x4 = 20 rows. There will be now join condition in Cross Join or if there is any, it will always be true for cross join. Cross join is used very rarely. 

    Query: SELECT * FROM Employees CROSS JOIN Jobs
     
    Result:
    EmpID
    FirstName
    LastName
    JobID.Employees
    JobID.Jobs
    Description
    1
    Pedro
    Afonso
    1
    1
    Chief Executive Officer
    2
    Philip
    Cramer
    2
    1
    Chief Executive Officer
    3
    Francisco
    Chang
    3
    1
    Chief Executive Officer
    4
    Janine
    Labrune
    2
    1
    Chief Executive Officer
    5
    Ann
    Devon
    3
    1
    Chief Executive Officer
    1
    Pedro
    Afonso
    1
    2
    Marketing Manager
    2
    Philip
    Cramer
    2
    2
    Marketing Manager
    3
    Francisco
    Chang
    3
    2
    Marketing Manager
    4
    Janine
    Labrune
    2
    2
    Marketing Manager
    5
    Ann
    Devon
    3
    2
    Marketing Manager
    1
    Pedro
    Afonso
    1
    3
    Sales Representative
    2
    Philip
    Cramer
    2
    3
    Sales Representative
    3
    Francisco
    Chang
    3
    3
    Sales Representative
    4
    Janine
    Labrune
    2
    3
    Sales Representative
    5
    Ann
    Devon
    3
    3
    Sales Representative
     
  2. Inner Join

    Inner Join is the most common and widely used SQL Join. Inner Join returns only those rows that are satisfied to the condition mentioned in JOIN clause or WHERE clause. Mostly the equality condition is used between two columns of different tables but other operators are also used for condition. Inner Join is said to be as default Join in SQL Server.

    Query: SELECT * FROM Employees INNER JOIN Jobs ON Employees.JobID = Jobs.JobID
     
    Result:
    EmpID
    FirstName
    LastName
    JobID.Employees
    JobID.Jobs
    Description
    1
    Pedro
    Afonso
    1
    1
    Chief Executive Officer
    2
    Philip
    Cramer
    2
    2
    Marketing Manager
    4
    Janine
    Labrune
    2
    2
    Marketing Manager
    3
    Francisco
    Chang
    3
    3
    Sales Representative
    5
    Ann
    Devon
    3
    3
    Sales Representative
     
  3. Outer Join

    Outer Join returns all rows from both tables that met condition and additionally it also returns rows from one or both table that do not met condition or the rows have null values.
     
    1. Left Outer Join

      Left Outer Join returns matching rows from both tables plus all rows from table specified on left side and returns null value for no match in right table. The OUTER Keyword can be omitted from the syntax.

      Query: SELECT * FROM Employees LEFT OUTER JOIN Jobs ON Employees.JobID = Jobs.JobID
       
      Result: 
      EmpID
      FirstName
      LastName
      JobID.Employees
      JobID.Jobs
      Description
      1
      Pedro
      Afonso
      1
      1
      Chief Executive Officer
      2
      Philip
      Cramer
      2
      2
      Marketing Manager
      3
      Francisco
      Chang
      3
      3
      Sales Representative
      4
      Janine
      Labrune
      2
      2
      Marketing Manager
      5
      Ann
      Devon
      3
      3
      Sales Representative

       

    2. Right Outer Join

      Right Outer Join returns matching rows from both tables plus all rows from table specified on right side and returns null value for no match in right table. The OUTER Keyword can be omitted from the syntax.
       

      Query: SELECT * FROM Employees RIGHT OUTER JOIN Jobs ON Employees.JobID = Jobs.JobID
       
      Result:
      EmpID
      FirstName
      LastName
      JobID.Employees
      JobID.Jobs
      Description
      1
      Pedro
      Afonso
      1
      1
      Chief Executive Officer
      2
      Philip
      Cramer
      2
      2
      Marketing Manager
      4
      Janine
      Labrune
      2
      2
      Marketing Manager
      3
      Francisco
      Chang
      3
      3
      Sales Representative
      5
      Ann
      Devon
      3
      3
      Sales Representative

       
    3. Full Outer Join

      Full Outer joins returns all the rows from both tables with null values in non-matching fields.

      Query: SELECT * FROM Employees FULL OUTER JOIN Jobs ON Employees.JobID = Jobs.JobID
       
      Result:
      EmpID
      FirstName
      LastName
      JobID.Employees
      JobID.Jobs
      Description
      1
      Pedro
      Afonso
      1
      1
      Chief Executive Officer
      2
      Philip
      Cramer
      2
      2
      Marketing Manager
      3
      Francisco
      Chang
      3
      3
      Sales Representative
      4
      Janine
      Labrune
      2
      2
      Marketing Manager
      5
      Ann
      Devon
      3
      3
      Sales Representative

       

  4. Equi-Join

    Equi-Join specifies special kind of join that only have equality sign in condition. If there is any other sign in Join condition than that will not be an Equi-Join. Inner Join is Equi-Join when it has equality operator in Join condition. Outer, Left Outer, Right Outer and Full Outer Join are also considered as Equi-Join if they have equal sign in Join condition. Natural Join, which will be discussed next, is always Equi-Join.

    Query: SELECT * FROM Employees EQUI JOIN Jobs ON Employees.JobID = Jobs.JobID
     
    Result:
    EmpID
    FirstName
    LastName
    JobID.Employees
    JobID.Jobs
    Description
    1
    Pedro
    Afonso
    1
    1
    Chief Executive Officer
    2
    Philip
    Cramer
    2
    2
    Marketing Manager
    4
    Janine
    Labrune
    2
    2
    Marketing Manager
    3
    Francisco
    Chang
    3
    3
    Sales Representative
    5
    Ann
    Devon
    3
    3
    Sales Representative

     
  5. Natural Join

    Natural Join combines two tables based on columns that have same name in both tables. We do not need to explicitly write condition for Natural Join because it implicitly joins two mentioned tables on equality bases. So Natural Join is always considered as Equi-Join. If two tables have no common column than Natural Join becomes Cross Join.

    Query: SELECT * FROM Employees, Jobs
     
    Result:
    EmpID
    FirstName
    LastName
    JobID.Employees
    JobID.Jobs
    Description
    1
    Pedro
    Afonso
    1
    1
    Chief Executive Officer
    2
    Philip
    Cramer
    2
    2
    Marketing Manager
    3
    Francisco
    Chang
    3
    3
    Sales Representative
    4
    Janine
    Labrune
    2
    2
    Marketing Manager
    5
    Ann
    Devon
    3
    3
    Sales Representative

  6. Self Join

    Sometimes we need to join a table to itself. Self Join satisfies this need. This is not very common type of Join but we can have some situation where we need to use Self Join.

    Query: SELECT E1.EmpID, E1.FirstName, E2.LastName, E2.JobID FROM Employees E1 JOIN Employees E2 ON E1.EmpID = E2.EmpID
     
    Result:
    EmpID
    FirstName
    LastName
    JobID.Employees
    1
    Pedro
    Afonso
    1
    2
    Philip
    Cramer
    2
    3
    Francisco
    Chang
    3
    4
    Janine
    Labrune
    2
    5
    Ann
    Devon
    3
     

 

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< Working with QUOTENAME and ASCII Common String functions in SQL server 2008.

Disclaimer - Privacy
© 2002-2017 DevASP.net