Concept of Join in SQL Server

Introduction

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. In SQL Joins are used to get the data of two or more table 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 table. 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 be used for the Join from each table. In joining foreign keys in a table and its 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

These are three types.

• Left outer Join
• Right Outer Join
• Full Outer Join

2. Inner Join

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 not a matching value for a row then the Null value is used in each column.

Syntax

SELECT * 
FROM Table A
LEFT OUTER JOIN Table B ON (condition );

Example

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 and that match from 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.

Syntax

SELECT * 
FROM Table A
RIGHT OUTER JOIN Table B ON (condition ) ;

Full Outer Join

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

Syntax  

SELECT * 
FROM Table A
FULL OUTER JOIN Table B ON (condition ) ;

Inner Join

In the concept of Inner join we take the two tables Table A and Table B. First it finds all possible pairs between table A and table B. Matching rows between the two tables specified in the INNER JOIN statement based on one or more columns having matching data. 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 these table. Then it implements the given condition on it. Find the desired output or result.

Syntax

SELECT *
FROM Table A
inner  JOIN Table B ON (condition ) ;

Resources

• Introduction to Joins in Sql Server
• Linked Server Concept in SQL Server
• Joins in SQL Server 2005
• SQL Server Integration Services (SSIS) - Merge Join Transformation in SSIS
• Difference Between On Clause And Where Clause When Used With Left Join in SQL Server