Explain different types of joins in SQL server?

 What are the different types of joins in SQL Server?

 Answer: Joins are used to bring data together from different tables based on their database relations. 

Some different types of joins are as below:

1. Inner Join

2. Left outer Join or Left Join

3. Right outer Join or Right Join

4. Full Outer Join or Full Join

5. Cross join

1. Inner join

In the case of Inner join, from both left and right tables, matching records will be produced

Ex:

SELECT a.col1, b.col1 FROM table1  a INNER JOIN table2 b ON a.Id = b.Id;

2. Left join

The LEFT JOIN keyword returns all records from the left table (table2), and the matching records from the right table (table1). If there is no match in right side able, the result is 0 records.

Ex:

SELECT a.desc, b.area  FROM table1 a LEFT JOIN table2 b

ON a.ColId = b.ColID;

3. Right join

The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records from the left table (table1). The result is 0 records from the left side, if there is no match.

Ex:

SELECT column_name(s) FROM table1 RIGHT JOIN table2

ON table1.column_name = table2.column_name;

4. Full join or Full Outer Join

The Full join returns a result set that contains all rows from both left and right tables, with the matching rows from both sides where available. In case there is no match, the missing side will have NULL values.

Ex:

SELECT a.Id,a.Name,b.DepName

FROM table1 a FULL JOIN table2 b ON a.id= b.Id;

5. Cross Join

A cross join returns the Cartesian product of rows from the rowsets in the join. In other words, it will combine each row from the first rowset with each row from the second rowset.

It produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table if no WHERE clause is used along with CROSS JOIN.

Ex: SELECT *  From table1 CROSS JOIN table2