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.
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.
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.
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.
InnerJoin.zip
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.
Code Block
CreateDepartmentTable.sqlce
Create the Department table.CREATE TABLE Department
(
DepartmentId int PRIMARY KEY NOT NULL,
DepartmentName nvarchar(30) NOT NULL
);
Code Block
CreateForeignKey.sqlce
Create the Employee table.
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 OneNow 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.
Code Block
InsertValuesIntoDepartment.sqlce
Insert entries into the Department table.INSERT INTO Department(DepartmentId, DepartmentName)
SELECT 31, 'Sales'
UNION ALL
SELECT 33, 'Engineering'
UNION ALL
SELECT 34, 'Clerical'
UNION ALL
SELECT 35, 'Marketing'
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.
Code Block
InsertValuesIntoEmployee.sqlce
Insert entries into the Employee table.INSERT INTO Employee(LastName, DepartmentId)
SELECT 'Lister', 31
UNION ALL
SELECT 'Silva', 33
UNION ALL
SELECT 'Jones', 33
UNION ALL
SELECT 'Robinson', 34
UNION ALL
SELECT 'Diaz', 34
We stand behind Server Intellect and their support team. They offer dedicated servers, and they are now offering cloud hosting!
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.
Code Block
InnerJoin.sqlce
Join the Department and Employee tables.SELECT * FROM Employee INNER JOIN Department
ON Employee.DepartmentId = Department.DepartmentId;
We chose Server Intellect for its cloud hosting, for our web hosting. They have managed to handle virtually everything for us, from start to finish. And their customer service is stellar.
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.
InnerJoin.zip
2 Responses
4.16.2011
Well we know this procedure that inner joins can be used like this i though this article will have something special in it …
4.21.2011
Check back soon for more advanced tutorials utilizing inner join