Types of SQL Server Joins With Examples

To understand examples of SQL joins we will create sample tables and insert some values to it.

Open your SQL server and execute below SQL statements to create EmployeeDetails and EmpSalary sample tables.

    
CREATE TABLE EmployeeDetails (
  EmpId int PRIMARY KEY,
  EmpFirstName varchar(50),
  EmpLastName varchar(50),
  Department varchar(50),
  DepartID int
)

CREATE TABLE EmpSalary (
  EmpID int,
  EmpFullName varchar(80),
  EmpSalary int,
  EmpWorkingYears int
)
        
    

Execute below SQL to insert sample data to EmployeeDetails table

    
    INSERT INTO EmployeeDetails
      VALUES (1001, 'Bhavana', 'Sharma', 'IT', 2)
    INSERT INTO EmployeeDetails
      VALUES (1002, 'Varun', 'Sharma', 'IT', 2)
    INSERT INTO EmployeeDetails
      VALUES (1003, 'Jaspreet', 'Kaur', 'Accounts', 3)
    INSERT INTO EmployeeDetails
      VALUES (1004, 'Shruti', 'Kalia', 'HR', 1)
    INSERT INTO EmployeeDetails
      VALUES (1005, 'Shaili', 'Verghese', 'IT', 2)
    INSERT INTO EmployeeDetails
      VALUES (1006, 'Rakesh', 'Dubey', 'Accounts', 3)
        
    

EmpSalary

    
    INSERT INTO EmpSalary
        VALUES (1001, 'Bhavana Sharma', 35000, 3)
    INSERT INTO EmpSalary
      VALUES (1002, 'Varun Sharma', 25000, 2)
    INSERT INTO EmpSalary
      VALUES (1003, 'Jaspreet Kaur', 20000, 2)
    INSERT INTO EmpSalary
      VALUES (1004, 'Shruti Kalia', 18000, 1)
    INSERT INTO EmpSalary
      VALUES (1005, 'Shaili Verghese', 25000, 2)
    INSERT INTO EmpSalary (EmpFullName, EmpSalary, EmpWorkingYears)
      VALUES ('Ramesh Kumar', 6000, 1)
        
    

If you make a select query on those tables you will get all rows from one table.

         SELECT  * FROM EmployeeDetails
    

         SELECT  * FROM EmpSalary        
    

1. INNER JOIN

   The inner join returns the record from multiple tables by applying a comparison operator on a common column. Only rows with values satisfying the join conditions are displayed as resultset. You can use joins with an output of table valued user defined functions.

   Syntax of INNER JOIN

               SELECT Column1, [ column2]
               From Table_name1 INNER JOIN Table_name2
               On Table_name1.column =Table_name2.column
           

   With the example of EmployeeDetails and EmpSalary, if you make a join on the EmpID column, you will get rows where EMPID exists in both tables.

   The below query shows how to use INNER JOIN to get matching rows from EmployeeDetails and EmpSalary.

       SELECT
           EmployeeDetails.EmpId,
           EmployeeDetails.EmpFirstName,
           EmpSalary.EmpSalary
       FROM EmployeeDetails
       JOIN EmpSalary
           ON EmployeeDetails.EmpId = EmpSalary.EmpID
   

   It returns the below output

   

   You can also get some variation in result using SQL Server Cross Apply and Outer apply.

2. OUTER JOIN

   An Outer Join returns a record containing all the rows from one table and the matching rows from the other.

   Syntax for Outer Join

       SELECT Column1, [Column2]
       FROM Table_name1 [LEFT | RIGHT | FULL] OUTER JOIN Table_name2
       ON Table_name1.column = Table_name2.column
               

   • LEFT OUTER JOIN

     The Left Outer Join returns all the rows from the table specified on the LEFT and the matching rows from the table specified on the RIGHT side of the LEFT OUTER JOIN keyword. NULL values are displayed in the columns of the right side table where matching rows are not found with the left side table.

     Example: The below given query returns all the rows from the left table (EmployeeDetails) and the matching data from the right table (EmpSalary), where the data does not match the NULL value is displayed.

         SELECT
             e.EmpId,
             e.EmpFirstName,
             e.DepartID,
             s.EmpSalary
         FROM EmployeeDetails e
         LEFT OUTER JOIN EmpSalary s
             ON e.EmpId = s.EmpID
         

     It returns below output

     

   • RIGHT OUTER JOIN

     The Right Outer Join returns all the rows from the table specified on the RIGHT and the matching rows from the table specified on the LEFT side of the RIGHT OUTER JOIN keyword.

     NULL values are displayed in the columns of the left side table where matching rows are not found with the right side table.

     Example: The below given query returns all the rows from the right table (EmpSalary) and the matching data from the left table (EmployeeDetails), where the data does not match NULL values are displayed.

         SELECT
             e.EmpId,
             e.EmpFirstName,
             e.DepartID,
             s.EmpSalary
         FROM EmployeeDetails e
         RIGHT OUTER JOIN EmpSalary s
             ON e.EmpId = s.EmpID
         

     It returns below output.

     

   • FULL OUTER JOIN

     A Full Outer Join is a combination of the left and right outer join. This join returns all the matching and non-matching values from both the tables. However, in the case of non-matching values a NULL value is displayed.

     Example Here in this case all the data from both the tables are displayed, however, non-matching values are displayed as NULL

         SELECT
           e.EmpId,
           e.EmpFirstName,
           e.DepartID,
           s.EmpSalary
         FROM EmployeeDetails e
         FULL OUTER JOIN EmpSalary s
           ON e.EmpId = s.EmpID
         

     It returns below output

     

3. CROSS JOIN

   The Cross Join can also be called as a Cartesian Product of the two tables. The result would be the number of rows in the first table multiplied by the number of rows in the second table.

   If you have 4 rows in first the table and 3 rows in the second table, you will get 12 rows when you have cross join on both tables.

   Syntax: SELECT Column1, [column2] FROM Table_name1 CROSS JOIN Table_name2

   Example: The below example table Computer has computer details and the table Addon have other device details. If we want to know the cost of a computer with all the addons, Cross join can be used for the results.

     CREATE TABLE Computer (
         CompID int,
         computerDes varchar(100),
         Price int
     )
   
     INSERT INTO computer
       VALUES (1, 'Pentium 4,1GB RAM', 25000)
     INSERT INTO computer
       VALUES (2, 'Dual Core,2GB RAM', 35000) 
               

   Addon Table

      CREATE TABLE Addon (
         ID INT,
         Description VARCHAR(100),
         Price INT
      )     
   
      INSERT INTO Addon
         VALUES (1, 'Speakers', 5000)
      INSERT INTO Addon
         VALUES (2, 'printer', 15000)
               

   You will use CROSS JOIN which to get computer and additional product price.

      SELECT
       computer.computerDes,
       Addon.description,
       computer.Price + Addon.price AS totalprice
     FROM Computer
       CROSS JOIN Addon
               

   Output of CROSS JOIN

   

4. EQUI JOIN

   An Equi Join is the same as an inner join, it joins the tables with the help of a foreign key. However an equi join differs from inner join in only one way, an equi join display all the column from both the tables.

   Syntax

     SELECT *
       FROM EmployeeDetails JOIN EmpSalary
       ON EmployeeDetails.EmpId = EmpSalary.EmpID
               

   Output of Equi Join

   

5. SELF JOIN

   In a self join a table is joined with itself. In this join, one row in a table correlates with other rows in the same table. It is important to give different alias names to the tables as the same table is going to be used twice.

   Syntax

       SELECT a.Column1, [b.column2]
       FROM Table_name1 a JOIN Table_name1 b
       WHERE a.column =b.column
               

   Example Considering EmpDetails table below, we can apply self join on EmpID and ManagerID

   Below is select all output of EmployeeDetails table

   

   The below query makes self join to get manager details saved in same table.

       SELECT a.EmpId, a.EmpFirstName AS ManagerName,
       b.EmpId AS ManagerID,b.EmpFirstName AS EmployeeName
       FROM EmployeeDetails a , EmployeeDetails b
       WHERE a.EmpId = b.ManagerID
               

   Below is the output of SELF JOIN