TSQL INNER JOIN

>>> Try our JOIN Types Course Today!

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:

LEFT OUTER JOIN and RIGHT OUTER JOIN
SQL Server JOIN Types Poster


 


More from Stedman Solutions:



  

Steve and the team at Stedman Solutions are here for all your SQL Server needs.

Contact us today for your  free 30 minute consultation..

We are ready to help!