Joins in SQL Server

Joins in SQL Server

In this article, I am going to give an overview of Joins in SQL Server. As part of this article, we are going to discuss the following pointers related SQL Server Joins.

  1. What are the different types of joins available in SQL Server?
  2. What are ANSI and NON-ANSI Joins in SQL Server?
  3. Inner Join in SQL Server?
  4. What is Outer Join in SQL Server?
  5. What is CROSS join in SQL Server?
  6. Self-Join in SQL Server?
  7. What is the difference between JOIN and UNION in SQL Server?

The SQL Server Joins are used to retrieve the data from two or more related tables. In general, tables are related to each other using the primary key and foreign key relationship but it is not mandatory. The tables involved in the joins must have a common field. And based on that common field the SQL Server JOINS retrieve the records.

What are the different types of joins available in SQL Server?

The SQL Server Joins are classified into two types such as

  1. ANSI format JOINS
  2. NON-ANSI format JOINS

Again the ANSI format joins classified into three types such as

  1. Inner join
  2. Outer join
  3. Cross join

Further, the outer join is divided into three types are as follows

  1. Left outer join
  2. Right outer join
  3. Full outer join

NON-ANSI join in SQL Server are classified into four types such as

  1. EQUI join
  2. NON-EQUI join
  3. SELF-join
  4. Natural Join
What are ANSI and NON-ANSI Joins?

When we retrieve the data from two or more tables based on ‘ON’ keyword condition then it is called as ANSI format joins whereas if we retrieve the data from two or more tables based on ‘WHERE’ keyword condition then it is called as NON-ANSI format joins.

What is the default JOIN in SQL Server?

The default join of joins is INNER JOIN.

What is the default join of outer join?

The default join of the outer join is the full outer join.

What is Inner Join in SQL Server?

The join that retrieves only the matching records from both the tables involved in the join is known as the inner join in SQL Server. That means Inner join returns only the matching records from both the tables. Non-matching records are eliminated.

What is Outer Join in SQL Server?

The Outer Join is an extension of Inner Join in SQL Server. In the inner join, we will only get the matching records from both the tables by eliminating the un-matching data from both the tables involved in the join. To overcome the above problem we need to use outer join mechanism in SQL Server. So, by using outer join in SQL Server, we can retrieve matching records as well as non-matching records from both the tables involved in the join.

Outer join is again classified into three types.

  1. Left outer join
  2. Right outer join
  3. Full outer join
What is Left outer join in SQL server?

It retrieves matching data from both the tables and also non-matching data from the left side table. Un-matching data will take null value in this case.

What is Right outer join in SQL Server?

It retrieves matching data from both the tables and non-matching data from the right side table.

What is Full outer join in SQL Server?

It retrieves matching data and also un-matching data from both tables at a time.

What is Cross join in SQL Server?

If two or more tables are combined with each other without any condition then we call it a Cartesian or cross join in SQL Server. In cross join, each record of a table is joins with each record of another table. Cross Join should not have ON clause.

The size of the Cartesian product in SQL Server is the number of the rows in the first table multiplied by the number of rows in the second table. In our next article, we will discuss the cross join in detail.

What is Self-Join in SQL Server?

Joining a table by itself is called as self-join in SQL Server. When we have some relation between the columns within the same table then we need to use the self-join mechanism.

When we implement the self-join in SQL Server, we should create the alias for the table name. We can create any number of aliases for a single table. Self-join is not a different kind of join. It can be classified as any type of join, such as

  1. Inner Join
  2. Outer (Left, Right, Full) join
  3. Cross Join

Note: The Self-join in SQL Server is just like any other joins except that the two instances of the same table will be joined in the query.

What is the difference between Union and Join in SQL Server?

Joins and Unions are two different things. The Union in SQL Server is used to combines the result-set of two or more select queries into a single result-set. On the other hand, the Joins in SQL Server are used to retrieve the data from two or more related tables involved in the join.

What is the General Formula for Joins in SQL Server?

Syntax of Joins in SQL Server

In the next article, I am going to discuss Cross Join in SQL Server with one real-time example. Here, in this article, I try to give an overview of SQL Server Joins. I hope you enjoy this article.

Leave a Reply

Your email address will not be published. Required fields are marked *