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.


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 , Department.Deptname inner join
Department on = 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.

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 = 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 =

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


Author: srirama13 Feb 2014 Member Level: Gold   Points : 0


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.

Author: Phagu Mahato22 Feb 2014 Member Level: Gold   Points : 6

An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.The most common type of join is: SQL INNER JOIN (simple join). An SQL INNER JOIN return all rows from multiple tables where the join condition is met.

SELECT Orders.OrderID, Students.StudentName, Orders.OrderDate
FROM Orders
ON Orders.StudentID=Students.StudentID;

Different SQL JOINs

Before we continue with examples, we will list the types the different SQL JOINs you can use:

Returns all rows when there is at least one match in BOTH tables
Return all rows from the left table, and the matched rows from the right table
Return all rows from the right table, and the matched rows from the left table
Return all rows when there is a match in ONE of the tables
[5]Outer join : You might want to include them in your results even if the chef didn't include ingredient details yet. To do this, we'll use an outer join.

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: