Joining SQL Tables

There are totally 3 types of Joins..

Inner, Outer and Cross. Outer Joins are again classified into 3 types Left, Right and Full. There is another type called the Self Join in which you use the same table to join with it again.

If you consider 2 tables

Inner Join -- Gives you all matching records from TableA and TableB

SELECT *
FROM TableA a
INNER JOIN TableB b ON a.PK = b.FK

Left Outer Join -- Gives you all matching records along with non-matching records from the left table in the query.

SELECT *
FROM TableA a
LEFT OUTER JOIN TableB b ON a.PK = b.FK

Right Outer Join -- Gives you all matching records along with non-matching records from the right table in the query.

SELECT *
FROM TableA a
RIGHT OUTER JOIN TableB b ON a.PK = b.FK

Full Outer Join -- Gives you all matching records from both tables as well as non-matching records too.

SELECT *
FROM TableA a
FULL OUTER JOIN TableB b ON a.PK = b.FK

Cross Join -- Gives you the Cartesian product of the records of two tables. Suppose table A has 2 records and tableB has 5 records. Your output will have 10 records.

SELECT *
FROM TableA, TableB

Self Join -- It's nothing but joining the a table with the same table one more time using any of the above joins.

SELECT *
FROM TableA a
INNER JOIN TableB b ON a.PK = b.ParentPK

Please mark as answer, if this has helped you solve the issue.

Good Luck :) .. visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.