Understanding INNER JOIN.
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