Microsoft SQL Server Joins

Microsoft SQL Server Joins

Microsoft SQL Server Joins Part - 9

In this post I will explain about Microsoft SQL Server Joins, although this part is quite so long. So let’s start.

Microsoft SQL Server Joins

MICROSOFT SQL SERVER JOINS CLAUSE INTRODUCTION

The JOIN keyword produces results based on a relationship between two or more tables.

There are 4 types of Joins in Microsoft SQL Server Joins –

  1. Equi Join/ Inner Join
  2. Outer Join
  3. Self Join
  4. Non-Equi Join
  5. Cross Join

1. Equi Join/ Inner Join (Microsoft SQL Server Joins)

An inner join produces results when matching information is found in both the tables.

Inner Join

A) SELECT STATEMENT USING AN INNER JOIN

1. A Formal Select Statement

Syntax:

A Formal Select Statement

2. An Alternative Select Statement

Syntax:

An Alternative Select Statement

Result:

An Alternative Select Statement

B) AN INSERT STATEMENT USING AN INNER JOIN

1. A Formal INSERT Statement

Syntax:

An Alternative insert Statement

2. An Alternative INSERT Statement

Syntax:

An Alternative INSERT Statement

Result:

An Alternative INSERT Statement

C) AN UPDATE STATEMENT USING AN INNER JOIN

1. A Formal Update Statement

Syntax:

A Formal Update Statement

2. An Alternative Update Statement

Syntax:

An Alternative Update Statement

Result:

An Alternative Update Statement

D) A DELETE STATEMENT USING AN INNER JOIN

1. A Formal DELETE Statement

Syntax:

A Formal DELETE Statement

2. An Alternative DELETE Statement

Syntax:

An Alternative DELETE Statement

Result:

An Alternative DELETE Statement

2. Outer Join (Microsoft SQL Server Joins)

An OUTER JOIN produces results based on a relationship and non-relationship between both the tables.

1. LEFT OUTER JOIN

In Left Outer JOIN, it returns all the rows from the first table and only matching rows of the second table and in this case if non-matching rows found in the second table, it returns the NULL value in the result set against the first table.

Microsoft SQL Server Joins Part - 9

1. A SELECT STATEMENT USING A LEFT OUTER JOIN

A) A Formal Select Statement

Syntax:

A Formal Select Statement

2. An Alternative Select Statement

Syntax:

An Alternative Select Statement

Note :

An Alternative Select Statement

Tip: to resolve this compatibility level issue, please set the compatible level of the current database. i.e.

Syntax:

An Alternative Select Statement

B) An INSERT STATEMENT USING A LEFT OUTR JOIN

1. A Formal INSERT Statement

Syntax:

A Formal INSERT Statement:

2. An Alternative INSERT Statement

Syntax:

An Alternative INSERT Statement

Result:

An Alternative INSERT Statement

C) An UPDATE STATEMENT USING A LEFT OUTER JOIN

1. A Formal UPDATE Statement

Syntax:

A Formal UPDATE Statement

2. An Alternative INSERT Statement

Syntax:

An Alternative INSERT Statement

Result:

An Alternative INSERT Statement

D) A DELETE STATEMENT USING A LEFT OUTER JOIN:

1. A Formal DELETE Statement

Syntax:

A Formal DELETE Statement

2. An Alternative DELETE Statement

Syntax:

An Alternative DELETE Statement

Result:

An Alternative DELETE Statement

2. RIGHT OUTER JOIN

In Right Outer JOIN, It returns all the rows from the second table and only matching rows of the first table and in this case, if non-matching rows found in the first table, it returns the NULL value in the result set against the second table.

Microsoft SQL Server Joins Part - 9

A) A SELECT STATEMENT USING A RIGHT OUTER JOIN

1. A Formal Select Statement

Syntax:

A Formal Select Statement:

2. An Alternative Select Statement

Syntax:

An Alternative Select Statement

Result:

An Alternative Select Statement

A) An INSERT STATEMENT USING A RIGHT OUTER JOIN

1. A Formal INSERT Statement

Syntax:

A Formal INSERT Statement

2. A Formal INSERT Statement

Syntax:

A Formal INSERT Statement

Result:

A Formal INSERT Statement

B) An UPDATE STATEMENT USING A RIGHT OUTER JOIN

1. A Formal UPDATE Statement

Syntax:

A Formal UPDATE Statement

2. An Alternative INSERT Statement

Syntax:

An Alternative INSERT Statement

Result:

An Alternative INSERT Statement

C) DELETE STATEMENT USING A RIGHT OUTER JOIN

1. A Formal DELETE Statement

Syntax:

A Formal DELETE Statement

2. An Alternative DELETE Statement

Syntax:

An Alternative DELETE Statement

Result:

An Alternative DELETE Statement

3. FULL OUTER JOIN

In Full Outer JOIN, it returns all matching and non-matching rows from both tables.

Microsoft SQL Server Joins Part - 9

Syntax:

Microsoft SQL Server Joins Part - 9

3. SELF-JOIN (Microsoft SQL Server Joins)

A SELF JOIN keyword we can use when if we want to JOIN two instances of the same table.

If we want to know about that employees list who role as boss.

Syntax:

Microsoft SQL Server Joins Part - 9

Result:

 SELF-JOIN

4. NON-EQUI JOIN (Microsoft SQL Server Joins)

A NON-EQUI JOIN keyword we can use when if we want to JOIN a table with another one’s some criteria.

If we want to know about that employees grading list with their facilities

NON EQUI JOIN

Syntax:

NON EQUI JOIN

5. CROSS JOIN (Microsoft SQL Server Joins)

A Cross JOIN returns a combination of all rows from all the tables. i.e. Summation of columns and multiplication of rows. 

Syntax:

CROSS JOIN

An Alternative of Cross Join

Syntax:

An Alternative of Cross Join

Result:

An Alternative of Cross Join

Assuming this tutorial post has well-acquainted aspiring to the complete requirement.

Also requesting, if you have any ideas or suggestions for me or you need some important blogs/Videos or guidelines related to SQL Server, Excel, WordPress, SEO, Website technologies then please feel free to write me on below comment section or you can go through the Contact Page. I will definitely come back to you with your desired requirements.

Hope this tutorial post was helpful for you and you liked very well, I am happy to serve you better. If you liked really this tutorial post, please don’t forget to share on social media like – Facebook, Whatsapp, LinkedIn, Twitter, Instagram, etc.

Leave a Reply