Home / SQL Server

SQL Server Joins

Rating: 5
Views: 12013
by Arogyalokesh
Last modified: May 26th 2021

Joins in SQL server is used to integrate rows from multiple datasets, based on a common field between them. In SQL Server Joins are primarily used to fetch reference details. For example, the employee table is having data for employee details like employee name, number, and in which department number he/she is working. The department's table is having data for department numbers, names, and locations. Now if we need to find the name of the department in which the employee is working then we need to join both the tables and fetch the filtered records.

Different Types Of SQL Server Joins

We use join majorly to reduce duplication in the result and improve the query performance when multiple data sets are involved in the query.

SQL server joins with examples

There are various types of joins available in SQL servers. They are as follows.

  1. Inner Join
  2. Outer Join
    • Full Outer Join: also known as Full Join
    • Left Outer Join: also known as Left Join
    • Right Outer Join: also known as Right Join
  3. Self Join
  4. Cross Join

sql server joins

self join & cross join

To understand them more, we will go through each join with examples.

want to build your career with a SQL Server certified professional, Then enroll in our "Best SQL Server Training" This will help you to achieve excellence in this domain.

Before going into much detail, let us first have 2 tables with data which we are going to use in joins examples.

Table 1: Emp 

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7839 KING PRESIDENT null 11/17/1981 5000 100 10
7698 BLAKE MANAGER 7839 5/1/1981 2850 null 30
7782 CLARK MANAGER 7839 5/1/1981 2450 100 10
7566 JONES MANAGER 7839 5/1/1981 2975 120 20
7788 SCOTT ANALYST 7566 4/19/1987 3000 120 20
7902 FORD ANALYST 7566 12/3/1981 3000 120 20
7369 SMITH CLERK 7902 12/17/1980 800 120 20
7499 ALLEN SALESMAN 7698 9/28/1981 1600 300 30
7521 WARD SALESMAN 7698 9/28/1981 1250 500 30
7654 MARTIN SALESMAN 7698 9/28/1981 1250 1400 30

Table 2: Dept

DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
70 MARKETING ATLANTA

1. Inner Join

Inner join is used to extract the records which are common between both the tables. In SQL terms, inner join returns all records where join condition is met.

General form of the inner join SQL statement is:

SELECT column-names
FROM table1 INNER JOIN table2 
ON table1.columnname = table2.columnname

For above 2 tables, we can write the inner join statement like below:

SELECT *
FROM emp e INNER JOIN dept d
ON e.deptno = d.deptno

Query Output:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
7839 KING PRESIDENT null ######## 5000 100 10 10 ACCOUNTING NEW YORK
7782 CLARK MANAGER 7839 6/9/1981 2450 100 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 ######## 3000 120 20 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 ######## 800 120 20 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 ######## 3000 120 20 20 RESEARCH DALLAS
7566 JONES MANAGER 7839 4/2/1981 2975 120 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 ######## 1600 300 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 5/1/1981 2850 null 30 30 SALES CHICAGO

Here, in the above query output, we can see employees having empno as 7521 and 7654 were not displayed because they have deptno as null. So, they are failing while joining with the dept table.

2. Outer Join

Left Outer Join

As the name implies, the left outer join extracts all the records from the left table, whereas from the right table, only common records whichever are meeting the join conditions will be fetched.

The general form of the left outer join SQL statement is:

SELECT column-names
FROM table1 LEFT OUTER JOIN table2 
ON table1.columnname = table2.columnname

For above 2 tables, we can write the left outer join statement like below:

SELECT *
FROM emp e LEFT OUTER JOIN dept d
ON e.deptno = d.deptno

Query Output:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
7782 CLARK MANAGER 7839 6/9/1981 2450 100 10 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT null ######## 5000 100 10 10 ACCOUNTING NEW YORK
7369 SMITH CLERK 7902 ######## 800 120 20 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 ######## 3000 120 20 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 ######## 3000 120 20 20 RESEARCH DALLAS
7566 JONES MANAGER 7839 4/2/1981 2975 120 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 ######## 1600 300 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 5/1/1981 2850 null 30 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 ######## 1250 1400 null null null null
7521 WARD SALESMAN 7698 ######## 1250 500 null null null null

Here, in above query output, we can see all the records from left (emp) table were selected irrespective of their deptno values.

Explore: Frequently asked SQL Server Interview Questions & Answers

Right Outer Join

As the name implies, right outer join extracts all the records from the right table whereas, from the left table, only common records whichever are meeting the join conditions will be fetched.

General form of the right outer join SQL statement is:

SELECT column-names
FROM table1 RIGHT OUTER JOIN table2 
ON table1.columnname = table2.columnname

For above 2 tables, we can write the right outer join statement like below:

SELECT *
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno = d.deptno

Query Output:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
7839 KING PRESIDENT null ######## 5000 100 10 10 ACCOUNTING NEW YORK
7782 CLARK MANAGER 7839 6/9/1981 2450 100 10 10 ACCOUNTING NEW YORK
7369 SMITH CLERK 7902 ######## 800 120 20 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 ######## 3000 120 20 20 RESEARCH DALLAS
7566 JONES MANAGER 7839 4/2/1981 2975 120 20 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 ######## 3000 120 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 ######## 1600 300 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 5/1/1981 2850 null 30 30 SALES CHICAGO
null null null null null null null null 40 OPERATIONS BOSTON
null null null null null null null null 70 MARKETING ATLANTA

Here, in above query output, we can see that all the records from the right (dept) table are fetched and only matched records from left(emp) table are fetched.

Full Outer Join

Full outer join extracts all the records from both the tables irrespective of any condition.

General form of full outer join SQL statement is:

SELECT column-names
FROM table1 FULL OUTER JOIN table2 
ON table1.columnname = table2.columnname

For above 2 tables, we can write the right outer join statement like below:

SELECT *
FROM emp e FULL OUTER JOIN dept d
ON e.deptno = d.deptno

Query Output:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
7839 KING PRESIDENT null null 5000 100 10 10 ACCOUNTING NEW YORK
7698 BLAKE MANAGER 7839 5/1/1981 5000 null 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 6/9/1981 2850 100 10 10 ACCOUNTING NEW YORK
7566 JONES MANAGER 7839 4/2/1981 2450 120 20 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 ######## 3000 120 20 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 ######## 3000 120 20 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 ######## 800 120 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 ######## 1600 300 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 ######## 1250 500 null null null null
7654 MARTIN SALESMAN 7698 ######## 1250 1400 null null null null
null null null null null null null null 70 MARKETING ATLANTA
null null null null null null null null 40 OPERATIONS BOSTON

Here, in above query output, we can see all the records from left(emp) and right(dept) table are fetched. Full outer join is also termed as join of left and right outer join.

3. Self Join

Self join is a simple join with the same table itself. It is mainly used when the hierarchy is involved, or

some relationships are there between records in the same table. For example, each employee will have one manager, and each manager is an employee as well. So for each manager, there will be a record on the employee table.

General form of self join SQL statement is:

SELECT column-names
FROM table1 t1 JOIN table1 t2 
ON t1.columnname = t2.columnname

For above 2 tables, we can write the self join statement like below:

SELECT e1.*, e2.empno "MGR EMPNO", e2.ename "MGR ENAME"
FROM emp e1 JOIN emp e2
ON e1.mgr = e2.empno

Query Output:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO MGR EMPNO MGR ENAME
7902 FORD ANALYST 7566 12/3/1981 3000 120 20 7566 JONES
7788 SCOTT ANALYST 7566 4/19/1987 3000 120 20 7566 JONES
7521 WARD SALESMAN 7698 2/22/1981 1250 500 null 7698 BLAKE
7654 MARTIN SALESMAN 7698 9/28/1981 1250 1400 null 7698 BLAKE
7499 ALLEN SALESMAN 7698 2/20/1981 1600 300 30 7698 BLAKE
7566 JONES SALESMAN 7839 4/2/1981 2975 120 20 7839 KING
7782 CLARK MANAGER 7839 6/9/1981 2450 100 10 7839 KING
7698 BLAKE MANAGER 7839 5/1/1981 2850 null 30 7839 KING
7369 SMITH CLERK 7902 12/17/1980 800 120 20 7902 FORD

Here, in query output, we can see employee details with his manager details by joining the emp table with 

4. Cross Join

Cross join is a cartesian product of two tables. It will connect all rows of the left table to each row of the right table. So, the query result of a cross join is a number of rows in the left table multiplied by the number of rows in the right table.

General form of cross join SQL statement is:

SELECT column-names
FROM table1 t1 CROSS JOIN table1 t2 

For above 2 tables, we can write the cross join statement like below:

SELECT count(*) 
FROM emp CROSS JOIN dept;

Query Output:

50
50 = (no. of rows in emp table) * (no. of rows in dept table)

Explore SQL Server Sample Resumes! Download & Edit, Get Noticed by Top Employers!

Conclusion

Joins are the backbone of any database. Hope this article has provided the required insights on various types of joins and when and how to use them.