Concept of Join in SQL Server


In this article, we show various types of joins in SQL Server. A Join in SQL is used to combine two or more tables. We use the Join operator to gather the data in a single query.

Joins in SQL 

SQL, Joins are used to get the data of two or more tables based on a relationship. In most cases of joining, we will use a primary key in the first table and a foreign key in the second. By using joins, you can retrieve data from two or more tables based on logical relationships between the tables.

A Join condition defines a way two tables are related in a query by

  • Specifying the column to bejoind for the Join from each table. In joining foreign keys in a table and their associated key in the other table.
  • To use the logical operator in comparing values from the columns.

There are many types of Joins. They are given below.

1. Outer Join in SQL Server

There are three types of Outer join in SQL Servejoineft outer Join

  • Right Outer Join
  • Full Outer Join

2. Inner Join in SQL Server

These are two types.

  • Natural Join
  • Cross Join

Left Outer Join

In this, there are two tables. We use a Left Outer Join operator on table A and table B, and it takes all records of the left table (table A) and the matching values from the right table (table B). When there is no matching value for a row,, the Null value is used in each column.


  Table A 
  LEFT OUTER JOIN Table B ON (condition);


Before joining, we have two tables, Employee and Department table.

Employee Table


Department Table



Right Outer Join

This join returns all the rows from the right table that match the left table. If there are no matching values in the left table, NULL values are returned for each column. It is the reverse of the Left Outer Join.


  Table A 
  RIGHT OUTER JOIN Table B ON (condition);


Full OujoinJoin

This join combines tjoineft outer join and thjoinght outer join. In this concept, the Full Outer Join joins both tables. Then the resultant table contains a null value for every column that lacks the matching row.


  Table A FULL 
  OUTER JOIN Table B ON (condition);


Inner Join

In the concept of Inner join,, we take the tables, Table A and Table B. First, it finds all possible pairs between table A and table B. Matching rows between the tables specified in the INNER JOIN statement based on one or more columns having matchjoindata. The join that displays only the rows that have a match in both the joined tables is known as an inner join. For this, it finds the Cartesian product of this table. Then it implements the given condition on it. Find the desired output or result.


  Table A inner  
  JOIN Table B ON (condition);



Similar Articles