Sunday, April 23, 2006

Types of Join



Types of join


Occasionally there is a post on a forum asking what a certain type of join is all about, so I thought it would probably be good to have a stock explanation to refer people to so that I don't re-write near enough the same response each time the question arises.

First lets consider these two tables.

A

Key         Data
----------- ----------
1 a
2 b

B

Key         Data
----------- ----------
1 c
3 d
We can see that the only match is where Key is 1.

INNER JOIN

In an INNER JOIN that will be the only thing returned. If we use the query

SELECT A.[Key] AS aKey, A.Data AS aData, B.[Key] AS bKey, b.Data AS bData
FROM A
INNER JOIN B ON a.[Key] = b.[Key]
the returned set will be
aKey        aData      bKey        bData
----------- ---------- ----------- ----------
1 a 1 c

In the case of the various outer joins non-matches will be returned also.

LEFT OUTER JOIN

In a LEFT OUTER JOIN everything on the left side will be returned. Any matches on the right side will be returned also, but if there is no match on the right side then nulls are returned instead.

The query

SELECT A.[Key] AS aKey, A.Data AS aData, B.[Key] AS bKey, b.Data AS bData
FROM A
LEFT OUTER JOIN B ON a.[Key] = b.[Key]
returns
aKey        aData      bKey        bData
----------- ---------- ----------- ----------
1 a 1 c
2 b NULL NULL

RIGHT OUTER JOIN

The RIGHT OUTER JOIN is very similar to the LEFT OUTER JOIN, except that, of course, the matching is reversed. Everything on the right side is returned, and only matches on the left side are returned. Any non-matches will be filled with nulls on the left side.

The query

SELECT A.[Key] AS aKey, A.Data AS aData, B.[Key] AS bKey, b.Data AS bData
FROM A
RIGHT OUTER JOIN B ON a.[Key] = b.[Key]
returns
aKey        aData      bKey        bData
----------- ---------- ----------- ----------
1 a 1 c
NULL NULL 3 d

FULL OUTER JOIN

A FULL OUTER JOIN returns a set containing all rows from either side, matched if possible, but nulls put in place if not.

The query

SELECT A.[Key] AS aKey, A.Data AS aData, B.[Key] AS bKey, b.Data AS bData
FROM A
FULL OUTER JOIN B ON a.[Key] = b.[Key]
returns
aKey        aData      bKey        bData
----------- ---------- ----------- ----------
1 a 1 c
2 b NULL NULL
NULL NULL 3 d

CROSS JOIN

The CROSS JOIN doesn't obey the same set of rules as the other joins. This is because it doesn't care about matching rows from either side, so there is no ON qualifier within the join clause. This is a simple join that joins all rows on the left side to all rows on the right side. Where the other joins cannot return more rows than exist in the most populous of the source tables, the CROSS JOIN will return the product of rows from each side. If you have 5 rows in Table A, and 6 rows in Table B it will return a set containing 30 rows.

The query

SELECT A.[Key] AS aKey, A.Data AS aData, B.[Key] AS bKey, b.Data AS bData
FROM A
CROSS JOIN B
returns
aKey        aData      bKey        bData
----------- ---------- ----------- ----------
1 a 1 c
2 b 1 c
1 a 3 d
2 b 3 d

from http://blogs.wdevs.com/colinangusmackay/archive/2006/02/27/12387.aspx


1 comment:

howardandres10759758 said...

Are you stuck in a job that is leading you on the path to no where?
We can help you obtain a College Degree with classes, books, and exams
Get a Genuine College Degree in 2 Weeks!
Well now you can get them!

Call this number now 24 hours a day 7 days a week (413) 208-3069

Get these Degrees NOW!!!

BA, BSc, MA, MSc, MBA, PHD,

Within 2 weeks!
No Study Required!
100% Verifiable

Call this number now 24 hours a day 7 days a week (413) 208-3069

These are real, genuine, They are verifiable and student records and
transcripts are also available. This little known secret has been
kept quiet for years. The opportunity exists due to a legal loophole
allowing some established colleges to award degrees at their discretion.


With all of the attention that this news has been generating, I wouldn't
be surprised to see this loophole closed very soon

Get yours now, you will thank me later
Call this number now (413) 208-3069
We accept calls 24 hours a day 7 days a week.