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.

CREATE TABLE Department

(

DepartmentId int PRIMARY KEY NOT NULL,

DepartmentName nvarchar(30) NOT NULL

);

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.

CREATE TABLE Employee

(

LastName nvarchar(30) NOT NULL,

DepartmentId int,

FOREIGN KEY (DepartmentId) REFERENCES Department(DepartmentId)

);

We used over 10 web hosting companies before we found Server Intellect. They offer dedicated servers, and they now offer cloud hosting!

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.

INSERT INTO Department(DepartmentId, DepartmentName)

SELECT 31, 'Sales'

UNION ALL

SELECT 33, 'Engineering'

UNION ALL

SELECT 34, 'Clerical'

UNION ALL

SELECT 35, 'Marketing'

Read more: SqlAtoms.com

Posted via email from Jasper-net