Joins and its usages
Sql server will provide the posibilities to join the number of tables.
The join Types are
1. Inner Join
2. Outer Join
3. Cross Join
Inner Join :
This kind of join will return the values which are match on both tables.
If atleast one column should match to return the values.
The entire column or the selected column from the table1 and Table2 will return, for those records have atleast one match column value.
Syntax:
Select * from Table1 innerjoin on Table1.column1 = Table2.column1
create Table Employee (id bigint identity (1,1), Name nvarchar(100), DeptID bigint)
Insert into employee
select 'prabu',2
union all
select 'Jagadeesh',2
Union All
select 'Maruthakumar',1
Create Table Department (id bigint identity(1,1), DeptName nvarchar(100))
Insert into Department
select 'SoftwareDevelopment'
union all
select 'Accounts'
union all
select 'Fabric'
select employee.name , Department.Deptname inner join
Department on Department.id = employee.DeptID
Outer Join :
This Outer join will return the values, which are matched and non matched records also.
If the Column values are matched then it return the both table values, if not matched then it will return the empty value for the colums.
Types
1. Left Outer Join
2. Right Outer Join
3. Full Outer Join
Left outer Join :
This will return the records which matched with the two tables and the entire values from the left Table.
Syntax :
Select * from Table1 left outer join
Table2 on Table1.Column1 = Table2.column1.
This will return full value from the left table.
Example :
Select * from Employee left outer join
Department on Department.id = Employee.Deptid.
This will return full values from Employee Table.
Right Outer Join :
This is just reverse action from Left outer join. This will return the values from the Right Side table.
Outer Join :
This join will return the values from both of the tables. This result will be empty for the values those are not matched.
In the above example The department 'Fabric' doesn't have any users then the department name only displays the employee name field will remain Empty.
Example :
Select * from Employee outer join
Departmnet on Department.id = Empoyee.Dept.id
Cross Join :
This will retrun the all the combination of values. This kind of join will return the values from all of the posible records form the two tables.
if Both of the tables have 4 recrods then the total possibilities will be 4 X 4 = 16 records.
Example :
Select * from Employee cross join
Department
Hi,
Cross Join is like a Cartesian product of A*B. If both table same no. of Columns are same than only it is eligible to Cross Join.