SQL Server JOINS
In this article, I am going to give an overview of SQL Server Joins. 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 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.
Explain the different types of SQL Server Joins
The SQL Server Joins are classified into two types such as
- ANSI format JOINS
- NON-ANSI format JOINS
Again the ANSI format joins classified into three types such as
- Inner join
- Outer join
- Cross join
Further, the outer join is divided into three types are as follows
- Left outer join
- Right outer join
- Full outer join
NON-ANSI join in SQL Server are classified into four types such as
- EQUI join
- NON-EQUI join
- 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 displays only the matching rows from both the tables involved in the join is known as the inner join. That means Inner join returns only the matching rows between both the tables. Non-matching rows are eliminated.
What is Outer Join in SQL Server?
OUTER JOIN is an extension of INNER JOIN.
In the inner join, the user will get only the matching data from both the tables i.e. the user will lose un-matching data from both the tables.
So to overcome the above problem we use outer join mechanism.
By using outer join we can retrieve matching data as well as non-matching data from both the tables.
Outer join again classified into three types.
- Left outer join
- Right outer join
- 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. That means when we join the tables without any condition then it is known as a CROSS JOIN.
In cross join, each record of a table is joins with each record of another table. Cross Join should not have ON clause.
A cross joins produces the Cartesian product of the tables that are involved in the join. 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.
We will discuss more cross join in the next article.
What is Self-Join in SQL Server?
Joining a table by itself is called as self-join. When we have some relation between the columns within the same table then we use the self-join mechanism.
When we implement self-join mechanism 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
- Inner Join
- Outer (Left, right, full) join
- Cross Join
Self-join is just like any other join except that two instances of the same table will be joined in the query.
What are the Differences between UNION and UNION ALL in SQL Server?
Both UNION and UNION ALL are used to combines the result-set of two or more select queries into a single result-set. The difference between these two is UNION removes duplicate rows whereas UNION ALL does not. When use UNION, to remove the duplicate rows, SQL server has to do a distinct sort which is time-consuming. This is the reason why the Union ALL operation is much faster than the UNION in SQL Server.
Note: If we want to see the cost of DISTINCT SORT, you can turn on the estimated query execution plan using CTRL + L.
Note: For UNION and UNION ALL to work, the Number, Data types, and the order of the columns in the select statements should be the same.
If we want to sort the results of UNION or UNION ALL the ORDER BY clause should be used on the last SELECT statement as shown below.
What is the Difference between JOIN and UNION in SQL Server?
JOINS and UNIONS are two different things. The UNION combines the result-set of two or more select queries into a single result-set whereas JOINS retrieve data from two or more tables based on logical relationships between the tables.
What is the General Formula for Joins in SQL Server?
In the next article, I will discuss Cross Join in SQL Server with one real-time example.
In this article, I try to give an overview of SQL Server Joins. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.