Reader Level:
ARTICLE

Concept of Join in SQL Server

On March 13, 2012 Articles | SQL Language
In this article we are show different 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.
  • 0
  • 0
  • 16353

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

emp1.gif

Department Table

dep2.gif

left3.gif

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 ) ;

right4.gif

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 ) ;

full5.gif

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 ) ;

inner6.gif

Resources

 

Arun Choudhary

Arun is very much interested in Microsoft & Life Style Accessory Design and Working with Microsoft Technologies. His expert areas are C# .NET, ASP.NET, ADO.NET, SQL Server, HTML, Window 7 etc....I...

  • 49rank
  • 2M readers
COMMENT USING

Trending up