Roja-Sql Server: Explain the different types of joins

There are 5 types of joins available in sql server

1) inner join
2) outer join( left outer join,Right outer join)
3) Full Join
4) Cross Join
5) Self Join

1) inner Join : Two tables having the common column values go with inner join.

Example:
select * from emp inner join empsalary
on
emp.empid=empsalary.empid

2) outer join:
Outer join has two sub types left outer join,Right outer join
Left outer join: Taking Left side values of the table checking into right side
select * from emp left outer join empsalary
on emp.empid=empsalary.empid
Right Outer join:Taking Right side values of the table checking left side
select * from emp right outer join empsalary
on emp.empid=empsalary.empid

3) Full join :
Full join is combined with left outer join ,Right outer join.
select * from emp full join employee
on emp.empid=employee.empid

4) cross Join: cross join is an carteasion product ,its like matrix format here it wont come on condidtion

select * from emp cross join employee

Self join :

Self join can check within the table called self join.

Equi Join "="
Non Equi Join "<, >, <>""
Outer Join (Left, right, full)
self join
Cartesian Product
Anti Join ("NOT IN", "NOT EXISTS")

By default, a join is assumed to be an inner join. You can also request other types of joins by clicking Join Type on the Joins page of SQL Assist. The following types of joins are available:

Inner join
Left outer join
Right outer join
Full outer join

An inner join is join method in which 7a column that is not common to all of the tables being joined is dropped from 7the resultant table. If your database supports the OUTER JOIN keywords, you 7can extend the inner join to add rows from one table that have no matching 7rows in the other table.
For example, you want to join two tables to get the last name of the manager for each department. The first table is a Department table that lists the employee number of each department manager. The second table is an Employee table that lists the employee number and last name of each employee. However, some departments do not have a manager; in these cases, the employee number of the department manager is null. To include all departments regardless of whether they have a manager, and the last name of the manager, if one exists, you create a left outer join. The left outer join includes rows in the first table that match the second table or are null. The resulting SQL statement is as follows:

SELECT DEPTNO, DEPTNAME, EMPNO, LASTNAME
   FROM DEPARTMENT LEFT OUTER JOIN EMPLOYEE
      ON MGRNO = EMPNO

A right outer join is the same as a left outer join, except that it includes rows in the second table that match the first table or are null. A full outer join includes matching rows and null rows from both tables.
For example, you have two tables, Table 1 and Table 2, with the following data:

Table 1. Table 1
Column A	Column B
1	A
2	B
3	C

Table 2. Table 2
Column C	Column D
2	X
4	2

You specify a join condition of Column A = Column C. The result tables for the different types of joins are as follows:

Inner join

Table 3. Inner join result table
Column A	Column B	Column C	Column D
2	B	2	X

Left outer join

Table 4. Left outer join result table
Column A	Column B	Column C	Column D
1	A	null	null
2	B	2	X
3	C	null	null

Right outer join

Table 5. Right outer join result table
Column A	Column B	Column C	Column D
2	B	2	X
null	null	4	2

Full outer join

Table 6. Full outer join result table
Column A	Column B	Column C	Column D
1	A	null	null
2	B	2	X
3	C	null	null
null	null	4	2

If you specify value (a,c), you obtain the following result:

Table 7. Result of value (a,c)
Value (a,c)
1
2
3
4