SQL Server joins tutorial

This sql server tutorial, we will discuss types of joins in SQL Server. SQL Server joins are like Equi joins, Non Equi joins, Self joins, Cartesian joins, Outer joins etc.

SQL Server joins

In order to retrieve data from two or more tables based on the logical relationship between the two tables, we require joins. Joins indicate how the database should use data from one table to select the rows in another table in SQL Server.

There are different types of joins are there. These are like:

  • Equi joins
  • Non Equi joins
  • Self joins
  • Cartesian joins
  • Outer joins
  • Left Outer Joins
  • Right Outer Joins

Equi joins in SQL Server

It returns the specified columns from both of the sql server tables and returns only the rows for which an equal value in the join column.

For Example:

SELECT E.EMPNO,E.ENAME,E.SAL,E.DEPTNO,
D.DEPTNO,D.DNAME,D.LOC,FROM EMP E
INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO

Non Equi joins in SQL Server

Here you can join values in two columns that are not equal. The same operators can be used for equijoins can be used herein a case of non-equijoins.

For Example:

SELECT E.EMPNO,E.ENAME,E.SAL,
S.SALGRADE,S.LOWSAL,S.HISAL
FROM EMP E INNER JOIN SALGRADE S
ON E.SAL BETWEEN S.LOSAL AND S.HISAL

Self joins in SQL Server

If a table has a reflexive relationship in the database, you can join it to itself automatically, This is also known as self joins.

For Example:

SELECT DISTINCT E.EMPNO,E.ENAME,E.SAL,E.DEPTNO,
FROM EMP E INNER JOIN EMP M
ON E.EMPNO=M.MGR

Cartesian joins in SQL Server

If the cartesian join have a where clause ,its behaves as a n innerjoin. If the cartesian join that doesnot have a where clause,it produces the cartesian product of the tables involved in the joins. The size of the cartesian product result set is the number of rows in the first tables multiplied by the no of rows in the second table. This is also known as cross join.

For Example:

SELECT E.EMPNO,E.ENAME,E.SAL,E.DEPTNO,
D.DEPTNO,D.DNAME,D.LOC FROM EMP E CROSS JOIN DEPT D

Outer joins in SQL Server

By default, when we join multiple tables using innerjoin what we get is the matching data from the two tables, if we want to include the data rows in the resultset that donot have a match in the joined tables, we can use outer join.

The outer join is basically devided into 3 types like

  • Left Outer join
  • Right Outer join
  • Full Outer join

Left Outer join in SQL Server

We use the Left Outer join to get the matching information plus the unmatched information from the Left-hand side table.

For Example:

SELECT E.EMPNO,E.ENAME,E.SAL,E.DEPTNO,
D.DEPTNO,D.DNAME,D.LOC,FROM EMP E
LEFT OUTER JOIN DEPT D ON E.DEPTNO=D.DEPTNO

Right Outer join in SQL Server

we use the Right Outer join to get the matching information plus the unmatched information from the Right-hand side table.

For Example:

SELECT E.EMPNO,E.ENAME,E.SAL,E.DEPTNO,
D.DEPTNO,D.DNAME,D.LOC,FROM EMP E
RIGHT OUTER JOIN DEPT D ON E.DEPTNO=D.DEPTNO

Full Outer join in SQL Server

Suppose we have unmatched information on both the sides we cannot retrieve it at the same time to overcome this in the ANSI style of joint statement they have introduced Full Outer join.

For Example:

SELECT E.EMPNO,E.ENAME,E.SAL,E.DEPTNO,
D.DEPTNO,D.DNAME,D.LOC,FROM EMP E
Full OUTER JOIN DEPT D ON E.DEPTNO=D.DEPTNO

Left outer join and Right outer join in SQL Server

In this article we will discuss about Left outer join and Right outer join in SQL Server. SQL joins are very much useful whenever we are retrieving data from multiple tables.

Left outer join example

First remember Left outer join and left join both are same, there is no difference between left outer join and left join. Left outer join gives all records from the left table as well as match records in two tables and corresponding null value for the right table.

Right outer join example

Similarly right join and right outer join are absolutely same, there is no difference between a right join and right outer join. Right outer join returns all the matching records from both tables, as well as all records from the right table and corresponding null values for the left table.

Example:

Suppose we have 2 tables name as Employees and Salary as shown in the figure below:

Employees table has 3 columns: ID, Name and Age

Left outer join in sql server

Salary table has 2 columns ID and Salary

Left outer join in sql server example

Now lets try to query left outer join like below:

SELECT e.ID,e.Age,e.Name,e.ID,s.ID FROM employees e
left join Salary s on e.ID=s.ID

The output will come as shown in the figure below:

Right outer join in SQL Server

Since its a left outer join, so its returning all records from the left table (Employees) and corresponding NULL values for Unmatched record.

Now lets try to query right outer join like below:

SELECT e.ID,e.Age,e.Name,e.ID,s.Salary FROM employees e
right join Salary s on e.ID=s.ID

Now check the out put in the figure below:

Right outer join in SQL Server example

Since its a right outer join so it is showing all records from the right table (salary) and corresponding NULL values for unmatched record.

You may like following SQL Server tutorials:

Here, we learned types of joins in SQL Server.

Donwload Hub site pdf

Download SharePoint Online Tutorial PDF FREE!

Get update on Webinars, video tutorials, training courses etc.

>