SQL join's

An SQL join clause combines records from two or more tables in a database. It creates a set that can be saved as a table or used as is. A JOIN is a means for combining fields from two tables by using values common to each. ANSI standard SQL specifies four types of JOIN: INNER, OUTER, LEFT, and RIGHT.

A programmer writes a JOIN predicate to identify the records for joining. If the evaluated predicate is true, the combined record is then produced in the expected format, a record set or a temporary table.

Note: As a special case, a table (base table, view, or joined table) can JOIN to itself in a self-join.


Different SQL JOINs

JOIN:	Return rows when there is at least one match in both tables
FULL JOIN:	Return rows when there is a match in one of the tables
LEFT JOIN:	Return all rows from the left table, even if there are no matches in the right table
RIGHT JOIN:	Return all rows from the right table, even if there are no matches in the left table

Example:

id	name	id	name
1	Petri	1	Codinghorror
2	Khankennels	2	Petri
3	Malherbe	3	Microsoft
4	Contoso	4	Malherbe

Inner join produces only the set of records that match in both Table A and Table B.
Note: INNER JOIN is the same as JOIN.

SELECT * FROM TableA
INNER JOIN TableB
ON TableA.name = TableB.name

id	name	id	name
1	Petri	2	Petri
2	Malherbe	4	Malherbe

Full outer join produces the set of all records in Table A and Table B, with matching records from both sides where available. If there is no match, the missing side will contain null.

SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name

id	name	id	name
1	Petri	2	Petri
2	Khankennels	null	null
3	Malherbe	4	Malherbe
4	Contoso	null	null
null	null	1	Codinghorror
null	null	3	Microsoft

Left outer join produces a complete set of records from Table A, with the matching records (where available) in Table B. If there is no match, the right side will contain null.
Note: In some databases LEFT JOIN is called LEFT OUTER JOIN. (Same applies for RIGHT)

SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name

id	name	id	name
1	Petri	2	Petri
2	Khankennels	null	null
3	Malherbe	4	Malherbe
4	Contoso	null	null

To produce the set of records only in Table A, but not in Table B, we perform the same left outer join, then exclude the records we don't want from the right side via a where clause.

SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableB.id IS null

id	name	id	name
2	Khankennels	null	null
4	Contoso	null	null

To produce the set of records unique to Table A and Table B, we perform the same full outer join, then exclude the records we don't want from both sides via a where clause.

SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableA.id IS null
OR TableB.id IS null

id	name	id	name
2	Khankennels	null	null
4	Contoso	null	null
null	null	1	Codinghorror
null	null	3	Microsoft

All venn diagram credit to:
http://www.khankennels.com
&
http://www.codinghorror.com