mssql joins

What are SQL Joins?

SQLJoin is used to fetch data from two or more tables, which is joined as a single set of data. It is used for combining column from two or more tables by using values common to both tables.

Types of Joins

There are following types of Joins:-

Inner

Outer

Left

Right

CROSS JOIN:-

This type of JOIN returns the Cartesian product of rows from the tables in Join. It will return a table which consists of records which combines each row from the first table with each row of the second table.

Cross JOIN syntax:-

select column_names

from 

first_table CROSS JOIN second_table

For Example: We have two tables Employees and city as shown below:-

Cross join of these table is as:-

INNER JOIN:-

This is a simple JOIN which returns the matched data between two tables as per the equality condition in the sql query.

Inner Join Syntax is,

                         SELECT  * FROM table1 INNER JOIN
                         table2 ON Equality_Condition
For Example:-
                        Inner join of tables Employees and gender is as shown below:-

Query that shows data of two tables and its inner join is as shown below:-

OUTER JOIN:-

Outer Join is based on both matched and unmatched data.

Outer join further divides into three parts:

1. Left Outer Join
2. Right Outer Join
3. Full Outer Join

LEFT Outer Join

The left outer join returns a resultset table with the matched data from the two tables and then the remaining rows of the left table and null from the right table's columns.

Syntax for Left Outer Join is,

select * from table1 left outer join table2 on (equality condition).

For example: we have two table emp and city as shown below.

Inner join of these two table is as below:-

RIGHT Outer Join

The right outer join returns a resultset table with the matched data from the two tables being joined, then the remaining rows of the right table and null for the remaining left table's columns.

Syntax for Right Outer Join is:-

select * from table1 right join table2 on (equality condition).

For example: We have two tables.

Resultant of these two tables as right outer join is as:-

Full Outer Join

The full outer join returns a resultset table with the matched data of two table then remaining rows of both left table and then the right table.

Syntax of Full Outer Join is,

select * from table1 full outer join table2 on (equality condition).

For example:- We have two tables as shown below:-

Resultant of these two tables as Full Outer Join is as below:-

asp.net