Joins in Sql Server

Joins in SQL SERVER 2008 R2 :

Query :


SELECT
a.[d_no],
a.[l_no],
a.[m_cd],
b.[m_desc],
a.[p_cd]
FROM [Test].[db].[TABLE1] a left outer join [Test].[db].[table2] b
on a.[m_cd]= b.[m_cd]
where <Your conditions>

INNER JOIN

This join returns rows when there is at least one match in both the tables.

OUTER JOIN

There are three different Outer Join methods.

LEFT OUTER JOIN
This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.

RIGHT OUTER JOIN
This join returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.

FULL OUTER JOIN
This join combines left outer join and right outer join. It returns row from either table when the conditions are met and returns null value when there is no match.

CROSS JOIN

This join is a Cartesian join that does not necessitate any condition to join. The resultset contains records that are multiplication of record number from both the tables.