Home  >  Blog  >   SQL Server

SQL Server Joins

Rating: 5
  
 
12972

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 

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7839KINGPRESIDENTnull11/17/1981500010010
7698BLAKEMANAGER78395/1/19812850null30
7782CLARKMANAGER78395/1/1981245010010
7566JONESMANAGER78395/1/1981297512020
7788SCOTTANALYST75664/19/1987300012020
7902FORDANALYST756612/3/1981300012020
7369SMITHCLERK790212/17/198080012020
7499ALLENSALESMAN76989/28/1981160030030
7521WARDSALESMAN76989/28/1981125050030
7654MARTINSALESMAN76989/28/19811250140030

 MindMajix YouTube Channel

Table 2: Dept

DEPTNODNAMELOC
10ACCOUNTINGNEW YORK
20RESEARCHDALLAS
30SALESCHICAGO
40OPERATIONSBOSTON
70MARKETINGATLANTA

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:

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNODEPTNODNAMELOC
7839KINGPRESIDENTnull########50001001010ACCOUNTINGNEW YORK
7782CLARKMANAGER78396/9/198124501001010ACCOUNTINGNEW YORK
7788SCOTTANALYST7566########30001202020RESEARCHDALLAS
7369SMITHCLERK7902########8001202020RESEARCHDALLAS
7902FORDANALYST7566########30001202020RESEARCHDALLAS
7566JONESMANAGER78394/2/198129751202020RESEARCHDALLAS
7499ALLENSALESMAN7698########16003003030SALESCHICAGO
7698BLAKEMANAGER78395/1/19812850null3030SALESCHICAGO

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:

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNODEPTNODNAMELOC
7782CLARKMANAGER78396/9/198124501001010ACCOUNTINGNEW YORK
7839KINGPRESIDENTnull########50001001010ACCOUNTINGNEW YORK
7369SMITHCLERK7902########8001202020RESEARCHDALLAS
7902FORDANALYST7566########30001202020RESEARCHDALLAS
7788SCOTTANALYST7566########30001202020RESEARCHDALLAS
7566JONESMANAGER78394/2/198129751202020RESEARCHDALLAS
7499ALLENSALESMAN7698########16003003030SALESCHICAGO
7698BLAKEMANAGER78395/1/19812850null3030SALESCHICAGO
7654MARTINSALESMAN7698########12501400nullnullnullnull
7521WARDSALESMAN7698########1250500nullnullnullnull

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:

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNODEPTNODNAMELOC
7839KINGPRESIDENTnull########50001001010ACCOUNTINGNEW YORK
7782CLARKMANAGER78396/9/198124501001010ACCOUNTINGNEW YORK
7369SMITHCLERK7902########8001202020RESEARCHDALLAS
7788SCOTTANALYST7566########30001202020RESEARCHDALLAS
7566JONESMANAGER78394/2/198129751202020RESEARCHDALLAS
7902FORDANALYST7566########30001202020RESEARCHDALLAS
7499ALLENSALESMAN7698########16003003030SALESCHICAGO
7698BLAKEMANAGER78395/1/19812850null3030SALESCHICAGO
nullnullnullnullnullnullnullnull40OPERATIONSBOSTON
nullnullnullnullnullnullnullnull70MARKETINGATLANTA

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:

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNODEPTNODNAMELOC
7839KINGPRESIDENTnullnull50001001010ACCOUNTINGNEW YORK
7698BLAKEMANAGER78395/1/19815000null3030SALESCHICAGO
7782CLARKMANAGER78396/9/198128501001010ACCOUNTINGNEW YORK
7566JONESMANAGER78394/2/198124501202020RESEARCHDALLAS
7788SCOTTANALYST7566########30001202020RESEARCHDALLAS
7902FORDANALYST7566########30001202020RESEARCHDALLAS
7369SMITHCLERK7902########8001202020RESEARCHDALLAS
7499ALLENSALESMAN7698########16003003030SALESCHICAGO
7521WARDSALESMAN7698########1250500nullnullnullnull
7654MARTINSALESMAN7698########12501400nullnullnullnull
nullnullnullnullnullnullnullnull70MARKETINGATLANTA
nullnullnullnullnullnullnullnull40OPERATIONSBOSTON

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:

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNOMGR EMPNOMGR ENAME
7902FORDANALYST756612/3/19813000120207566JONES
7788SCOTTANALYST75664/19/19873000120207566JONES
7521WARDSALESMAN76982/22/19811250500null7698BLAKE
7654MARTINSALESMAN76989/28/198112501400null7698BLAKE
7499ALLENSALESMAN76982/20/19811600300307698BLAKE
7566JONESSALESMAN78394/2/19812975120207839KING
7782CLARKMANAGER78396/9/19812450100107839KING
7698BLAKEMANAGER78395/1/19812850null307839KING
7369SMITHCLERK790212/17/1980800120207902FORD

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.

Join our newsletter
inbox

Stay updated with our newsletter, packed with Tutorials, Interview Questions, How-to's, Tips & Tricks, Latest Trends & Updates, and more ➤ Straight to your inbox!

Course Schedule
NameDates
SQL Server TrainingMar 05 to Mar 20View Details
SQL Server TrainingMar 09 to Mar 24View Details
SQL Server TrainingMar 12 to Mar 27View Details
SQL Server TrainingMar 16 to Mar 31View Details
Last updated: 04 Apr 2023
About Author

Arogyalokesh is a Technical Content Writer and manages content creation on various IT platforms at Mindmajix. He is dedicated to creating useful and engaging content on Salesforce, Blockchain, Docker, SQL Server, Tangle, Jira, and few other technologies. Get in touch with him on LinkedIn and Twitter.

read more