An Inner Join in SQL is a type of join operation that returns only the rows that have matching values in both tables. The Inner Join only returns the rows that satisfy the join condition, and discards any rows that do not meet the criteria. This makes it a useful tool for retrieving data that is present in both tables, and is one of the most commonly used types of joins in SQL.
Syntax of SQL Inner Join
The syntax for an SQL Inner Join is as follows:
SELECT column_names FROM table1 JOIN table2 ON table1.column = table2.column;
Where “table1” and “table2” are the names of the tables you want to join, and “column” is the name of the related column between the two tables. The join condition specifies the relationship between the two tables and defines which rows will be returned in the result set.
Example of SQL Inner Join
Consider the following two tables:
Employees
| EmployeeID | EmployeeName | Salary |
|---|---|---|
| E001 | John | 5000 |
| E002 | Jane | 5500 |
| E003 | Bob | 4500 |
| E004 | Alice | 4000 |
| E005 | Eve | 6000 |
Departments
| DepartmentID | DepartmentName | EmployeeID |
|---|---|---|
| D001 | IT | E001 |
| D002 | Marketing | E002 |
| D003 | HR | E003 |
| D004 | Finance | E005 |
To retrieve the employee name, salary, and department name for all employees, we can use the following SQL Inner Join statement:
SELECT EmployeeName, Salary, DepartmentName FROM Employees JOIN Departments ON Employees.EmployeeID = Departments.EmployeeID;
The result of this Inner Join would be:
| EmployeeName | Salary | DepartmentName |
|---|---|---|
| John | 5000 | IT |
| Jane | 5500 | Marketing |
| Bob | 4500 | HR |
| Eve | 6000 | Finance |
As you can see, the result only includes the rows where there is a matching EmployeeID in both tables, and the information from both tables is combined into one result set.
Combining Multiple Tables
In SQL, it’s possible to join multiple tables together to retrieve data from all of them. This is useful when a single table is not enough to hold all of the information you need, or when the data you need is stored in multiple tables. For example, if you have a table of employees and a table of departments, you can join these two tables to get a list of employees and the departments they work in.
The syntax for joining multiple tables is similar to that of a single join, but with additional tables added. For example:
SELECT column_names FROM table1 JOIN table2 ON table1.column = table2.column JOIN table3 ON table2.column = table3.column;
In this example, the join is performed between table1 and table2, and then between the result of that join and table3. The result is a single table that combines data from all of the joined tables. The number of tables that can be joined together is limited only by the resources of the database server and the complexity of the join conditions.
It’s important to note that the order in which the tables are joined can affect the result. The first table listed in the join statement will be the “left” table, and the second table will be the “right” table. The join condition specifies the relationship between the columns in these two tables. If you switch the order of the tables, you’ll get a different result.
By using SQL Inner Joins and combining multiple tables, you can effectively retrieve and manipulate data from your database, even if it’s stored across multiple tables. This makes it an essential tool for any database administrator or developer to know.
Consider the following three tables:
Employees
| EmployeeID | EmployeeName | Salary |
|---|---|---|
| E001 | John | 5000 |
| E002 | Jane | 5500 |
| E003 | Bob | 4500 |
| E004 | Alice | 4000 |
| E005 | Eve | 6000 |
Departments
| DepartmentID | DepartmentName | EmployeeID |
|---|---|---|
| D001 | IT | E001 |
| D002 | Marketing | E002 |
| D003 | HR | E003 |
| D004 | Finance | E005 |
Countries
| CountryID | CountryName | DepartmentID |
|---|---|---|
| C001 | USA | D001 |
| C002 | UK | D002 |
| C003 | Australia | D003 |
| C004 | Canada | D004 |
To retrieve the employee name, salary, department name, and country name for all employees, we can use the following SQL Inner Join statement:
SELECT Employees.EmployeeName, Employees.Salary, Departments.DepartmentName, Countries.CountryName FROM Employees INNER JOIN Departments ON Employees.EmployeeID = Departments.EmployeeID INNER JOIN Countries ON Departments.DepartmentID = Countries.DepartmentID;
The result of this query would be:
| EmployeeName | Salary | DepartmentName | CountryName |
|---|---|---|---|
| John | 5000 | IT | USA |
| Jane | 5500 | Marketing | UK |
| Bob | 4500 | HR | Australia |
| Eve | 6000 | Finance | Canada |
In this example, we used two INNER JOINs to join three tables together. The first INNER JOIN joins the Employees and Departments tables on the EmployeeID column, and the second INNER JOIN joins the Departments and Countries tables on the DepartmentID column. The result of the query is a table that contains the combined data from all three tables. Note that the row for Alice is not included in the result, as there is no corresponding entry in the Departments table for her EmployeeID.
Benefits of SQL Inner Join
The SQL Inner Join has several benefits, including:
- Combining data from two or more tables into one result set
- Retrieving only the rows that meet the join condition
- Improving the efficiency of the database by reducing the amount of data returned
- Allowing you to retrieve related data from multiple tables in a single query
In conclusion, the SQL Inner Join is a useful tool for combining data from multiple tables, and is one of the most commonly used types of joins in SQL. By only returning the rows that meet the join condition, it helps to improve the efficiency of the database and allows you to retrieve related data in a single query.
