Join Types

There are three different Join Types in SQL Server. Within the types of joins are different variations of the joins.

INNER JOIN
Only join records from two different tables where the join columns are equal. Restricts the result set to rows where a matching column exists in both tables.
[cc lang=”sql”]
SELECT a.*, b.*
FROM dbo.TableA a
INNER JOIN dbo.TableB b
ON a.ColumnID = b.ColumnID
[/cc]

OUTER JOIN
Retrieves additional records from one or both tables where the columns are not equal.
[cc lang=”sql”]
— Retrieve all records from TableA even where there is not a match in TableB
SELECT a.*, b.*
FROM dbo.TableA a
LEFT OUTER JOIN dbo.TableB b
ON a.ColumnID = b.ColumnID
[/cc]
[cc lang=”sql”]
— Retrieve all records from TableB AND TableA even where there is not a match
SELECT a.*, b.*
FROM dbo.TableA a
FULL OUTER JOIN dbo.TableB b
ON a.ColumnID = b.ColumnID
[/cc]
[cc lang=”sql”]
— Retrieve all records from TableB even where there is not a match in TableA
SELECT a.*, b.*
FROM dbo.TableA a
RIGHT OUTER JOIN dbo.TableB b
ON a.ColumnID = b.ColumnID
[/cc]

CROSS JOIN
Cross Join – join every combination of record in one table with every combination of record in another table.
[cc lang=”sql”][/cc]
— No Join column is necessary
SELECT a.*, b.*
FROM dbo.TableA a
CROSS JOIN dbo.TableB b
[cc lang=”sql”][/cc]

One comment
Nidaul 29 Aug 2015 at 8:46 am

In this awesome scemhe of things you actually get a B- just for effort and hard work. Where exactly you actually confused me was first on the details. You know, people say, details make or break the argument.. And it couldn’t be much more true in this article. Having said that, allow me tell you exactly what did deliver the results. The writing can be really convincing and that is probably the reason why I am taking an effort to opine. I do not really make it a regular habit of doing that. Second, despite the fact that I can notice the jumps in reason you come up with, I am not certain of exactly how you appear to unite the points which in turn help to make the conclusion. For right now I shall yield to your position but wish in the foreseeable future you link the facts better.

Featured Articles

 Site Author

  • Thanks for visiting!