How to Use the INNER JOIN Keyword in SQL Server

In this tutorial you will learn how to use the INNER JOIN keyword. The INNER JOIN keyword returns rows when there is at least one match in two tables. This is especially important when working with multiple tables because it can join them and provide data that is linked to each one. For this tutorial we will create two tables, Employee and Department. Department will have a primary key and Employee will have a foreign key that connects to the primary key, thus connecting the tables.

Setting Up

Before we start getting to the detailed work, we need to set up the tables.  Let’s create the Department table first since it will include the primary key. Open SQL Server and open a new query. Use the ‘CREATE TABLE’ statement to create a table and name it “Department”. In it, we will have two columns, “DepartmentId” and “DepartmentName”. DepartmentId will be of ‘int’ data type and be a primary key while DepartmentName will be of ‘nvarchar’ data type. Execute the query and make sure it completes successfully.

 

 

Next we will create the Employee table. The Employee table will have two columns, “LastName” and “DepartmentId”. LastName will be of ‘nvarchar’ data type and DepartmentId will be of ‘int’ data type. We will also mark DepartmentId as the foreign key and reference it to the DepartmentId column in the Department table. Execute the query and make sure it completes successfully.

 

 

Step One

Now that we have our two tables created, we can start adding data into them. First we will add data into the Department table. By making use of the INSERT INTO, SELECT, and UNION ALL statements we are able to add multiple entries into the table in a single query.

 

 

Click the query button and make sure it completes successfully.

Step Two

Next we will insert data into the Employee table. Just like in the previous step, we will make use of the INSERT INTO, SELECT, and UNION ALL statements to insert multiple values in a single query.

 

 

Execute the query and make sure it completes successfully.

Step Three

Now we can create a query that will join the two departments and allow us to return rows that have at least one match. We will select all columns from the Employee table and join it with the Department table. After we join the two tables, we can declare that we only want to output rows where the DepartmentId in the Employee and Department tables match.

 

 

Execute the query and make sure it completes successfully.

Output

When the query is executed you should see four columns: LastName, DepartmentId, DepartmentId, and DepartmentName. Rows with the same DepartmentId will appear together so the table will be easier to see what department an employee works in.

Thanks for reading and make sure to download the source files to get a better understanding of how the code works.

Leave a Reply

Your email address will not be published. Required fields are marked *