Visual Representation of SQL Joins

Source: http://www.codeproject.com/KB/database/Visual_ SQL _Joins.aspxUsing the code

I am going to discuss seven different ways you can return data from two relational tables. I will be excluding cross Joins and self referencing Joins. The seven Joins I will discuss are shown below:

1. INNER JOIN
2. LEFT JOIN
3. RIGHT JOIN
4. OUTER JOIN
5. LEFT JOIN EXCLUDING INNER JOIN
6. RIGHT JOIN EXCLUDING INNER JOIN
7. OUTER JOIN EXCLUDING INNER JOIN

For the sake of this article, I'll refer to 5, 6, and 7LEFT EXCLUDING JOIN,RIGHT EXCLUDING JOIN, AndOUTER EXCLUDING JOIN, Respectively. some may argue that 5, 6, and 7 are not really joining the two tables, but for simplicity, I will still refer to these as Joins because you use a SQL Join in each of these queries (but exclude some records withWHEREClause ).

Inner JOIN

This is the simplest, most understood Join and is the most common. this query will return all of the records in the left table (table A) that have a matching record in the right table (table B ). this Join is written as follows:

SELECT <select_list> FROM Table_A AINNER JOIN Table_B BON A.Key = B.Key

Left JOIN

This query will return all of the records in the left table (table A) regardless if any of those records have a match in the right table (table B ). it will also return any matching records from the right table. this Join is written as follows:

SELECT <select_list>FROM Table_A ALEFT JOIN Table_B BON A.Key = B.Key

Right JOIN

This query will return all of the records in the right table (table B) regardless if any of those records have a match in the left table (table ). it will also return any matching records from the left table. this Join is written as follows:
SELECT<Select_list>
FROM Table_A
Right join Table_ B B
On a. Key = B. Key

Outer JOIN

This Join can also be referred to asFULL OUTER JOINOrFULL JOIN. This query will return all of the records from both tables, joining records from the left table (table A) that match records from the right table (table B ). this Join is written as follows:

SELECT<Select_list>
FROM Table_A
Full outer join Table_ B B
On a. Key = B. Key

Left Excluding JOIN

This query will return all of the records in the left table (table A) that do not match any records in the right table (table B). This Join is written as follows:

SELECT<Select_list>
FROM Table_A
Left join Table_ B B
On a. Key = B. Key
Where B. Key IS NULL

Right Excluding JOIN

This query will return all of the records in the right table (table B) that do not match any records in the left table (table A). This Join is written as follows:

SELECT<Select_list>
FROM Table_A
Right join Table_ B B
On a. Key = B. Key
Where a. Key IS NULL

Outer Excluding JOIN

This query will return all of the records in the left table (table A) and all of the records in the right table (table B) that do not match. I have yet to have a need for using this type of Join, but all of the others, I use quite frequently. this Join is written as follows:

SELECT<Select_list>
FROM Table_A
Full outer join Table_ B B
On a. Key = B. Key
Where a. Key is null or B. Key IS NULL

Additional: http://en.wikipedia.org/wiki/ SQL _join