JUL 06 2013

Introduction to JOINs in SQL Server

SQL Server

The nature of relational database design shows that we will have related data that is stored in different tables. To retrieve data from two or more tables we use JOINS.

The JOIN clause tells the database how the data in the two tables is related so that it can return a correct representation of the related data.

Before entering into JOINs concept in SQL Server first design two tables in database, which I will be using to give you a better understanding of JOINs.


Table1Table2


In this blog I am going to discuss about two JOIN type:

  1. INNER JOIN
  2. OUTER JOIN

INNER JOIN

The INNER JOIN keyword return rows when there is at least one match in both tables.

inner join

Examlpe of Inner Join in SQL Server

SELECT * FROM Table1 t1
INNER JOIN Table2 t2
ON t1.ID = t2.ID

Output of Inner Join in SQL Server
Inner Join

OUTER JOIN

There are three types of OUTER JOIN’s in SQL Server

  1. LEFT OUTER JOIN
  2. RIGHT OUTER JOIN
  3. FULL OUTER JOIN

LEFT OUTER JOIN

LEFT OUTER JOIN in SQL Server returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.

left join

Example of Left Outer Join in SQL Server

SELECT * FROM Table1 t1
LEFT OUTER JOIN Table2 t2
ON t1.ID = t2.ID

Output of Left Outer Join in SQL Server
Left Outer Join

RIGHT OUTER JOIN

RIGHT OUTER JOIN returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.

right join

Example of Right Outer Join in SQL Server

SELECT * FROM Table1 t1
RIGHT OUTER JOIN Table2 t2
ON t1.ID = t2.ID

Output of Right Outer Join in SQL Server
RIGHT OUTER JOIN

FULL OUTER JOIN

FULL OUTER JOIN returns rows from either table when the conditions are met and returns null value when there is no match.

outer join

Example of Full Outer Join in SQL Server

SELECT * FROM Table1 t1
FULL OUTER JOIN Table2 t2
ON t1.ID = t2.ID

Output of Full Outer Join in SQL Server
FULL OUTER JOIN

  • callum on said:

    Looking for ahead of time to reading through extra of your stuff in a while!? I