Understanding INNER JOIN.

The other posts in this series are:
SQL Joins and Applies – Understanding CROSS JOIN
SQL Joins and Applies – Understanding OUTER JOIN
SQL Joins and Applies – Understanding APPLY

This is part of a four part post series on SQL Joins and Applies.
If you want to follow the examples given, you can get the T-SQL to create the tables, function and test data from my earlier post SQL Joins and Applies – Test Data.

Inner join is the most useful join to know in a relational database.

From Books Online:

A join that displays only the rows that have a match in both joined tables.

Lets jump straight into some SQL.

SELECT		tsi.sourceValue [sourceValue_left],
		tsi.sysID [sysID_left],
		tss.sysID [sysID_Right],
		tss.sourceValue [sourceValue_right]
FROM		dbo.tblSourceIntegers tsi
INNER JOIN	dbo.tblSourceStrings tss
	ON	tsi.sysID = tss.sysID;

The result of this is:

sourceValue_left sysID_left  sysID_Right sourceValue_right
---------------- ----------- ----------- -----------------
200              2           2           Apple
500              5           5           Dog

(2 row(s) affected)

Note that the use of a comparison
In the above example, the comparison operator is comparing the sysID from both tables.
Results are only returned if the sysID’s match. Remember that nothing (not even another NULL) will evaluate to true when compared with another NULL.

A comparison operator is mandatory in an inner join.

Note: The use of “ON 1=1” is identical to a CROSS JOIN