The INNER JOIN has to be the most common JOIN type in SQL Server. This is where we will start on our journey to understand all the JOIN types in SQL Server.
The best way that I have discovered to represent JOINing tables is to use Venn Diagrams that show what overlaps, what tables have in common, and what is missed. This is represented on my Free SQL Server Join Types Poster, if you haven’t seen the poster, please check it out, it will help with your understanding of all SQL Server JOIN Types.
The INNER JOIN represents an intersection of two data sets , Table1 and Table2. Its a way of saying “show me the rows from Table1 that math up with Table2, and just ignore or skip all those rows that don’t match.” This is represented in Venn diagrams with the following:
The middle area shown in green represents the rows that match between Table1 and Table2. When we use the term “Match” in this case, the match is determined by the ON clause in the INNER JOIN.
The query to show this would look something like the following. First we need to set up some tables to query against:
CREATE TABLE Table1 ( id INTEGER IDENTITY NOT NULL, Name VARCHAR(100) NOT NULL, fk INTEGER NULL, fk_table3 INTEGER NULL, PRIMARY KEY (id) ) ; CREATE TABLE Table2 ( id INTEGER NOT NULL, FavoriteColor VARCHAR(100) NOT NULL ); -- notice... Table2 is more real world, kinda slopy with no PRIMARY KEY INSERT INTO Table2 (id, FavoriteColor) VALUES (1, 'red'),(2, 'green'),(3, 'blue'), (4, 'pink'),(5, 'purple'),(6, 'mauve'), (7, 'orange'),(8, 'yellow'),(1, 'indigo'); INSERT INTO Table1 (Name, fk, fk_table3) VALUES ('Steve', 1, NULL),('Aaron', 3, NULL),('Mary', 2, NULL), ('Fred', 1, NULL),('Anne', 5, NULL),('Beth', 8, 1), ('Johnny', NULL, 1), ('Karen', NULL, 2);
Now that the tables are set up, lets just take a look at a simple INNER JOIN between Table1 and Table2.
SELECT * FROM Table1 t1 INNER JOIN Table2 t2 ON t1.fk = t2.id;
When this query is run it shows the rows that are matched up with the between t1.fk and t2.id, which looks like this:
For rows that don’t match between Table2 and Table2, they are not shown in the results.
For More Information: