SQL Inner Join

SQL Inner Join

INNER JOIN in SQL is the most common and important type of join which allows users to access matching data from two or more tables. When the join condition is met between the tables, then it returns all the common rows from them.

The Venn diagram of INNER JOIN is shown in the following picture. The shaded region of the Venn diagram shows the intersection values of two tables:

Example:

SELECT Column_Name1, Column_Name2, ….., Column_NameN 

FROM Table_Name1 INNER JOIN Table_Name2 

ON Table_Name1.Column_Name = Table_Name2.Column_Name; 

Let’s take two tables named Employee_Details and Department to understand the concept of INNER JOIN. The Employee_Details table contains Emp_ID, Emp_Name, Dept_ID, and Emp_Salary columns. The Department table contains Dept_Id and Dept_Name columns.

We can check the data of Employee_Details and Department table using the following two different queries:

SELECT * FROM Department; 

 Output:

SELECT * FROM Employee_Details

 Output:

The following query joins these above two tables using INNER JOIN in structured Query Language:

SELECT  

Employee_Details.Emp_Id,  

Employee_Details.Emp_Name,

Department.Dept_Name,

Employee_Details.Emp_Salary,  

FROM Department INNER JOIN Employee_Details  

ON  

Department.Dept_ID = Employee_Details.Dept_ID;

Explanation of above INNER JOIN query:

This query joins the Employee_Details and Department table and accesses the records from both the tables where Department.Dept_Id = Employee_Details.DeptId.

It only fetches the details of those employees from both the tables whose Dept_Id in the Employee table matches with the Dept_Id of the Department table.

If the Dept_Id is NULL or does not match, then that row will not show in the output.

 Output:

Course Video

NOTE: Practice below questions on site editor.

1. Write a SQL query to get employeeID, Job title, firstName, MiddleName, LastName and title from Person and HumanResources.Employee tables.

NOTE: OUTPUT on SQL Editor Count: 100 Rows.

Output 

2. Write a SQL query to get employeeID, Job title, firstName, LastName and startDate from Person, HumanResources.Employee and HumanResources.EmployeeDepartmentHistory tables.

NOTE: OUTPUT on SQL Editor Count: 100 Rows.
OUTPUT 

3. Write a SQL query to get employeeID, Job title, firstName, LastName, departmentName, department groupName, startDate from Person, HumanResources.Employee, HumanResources.EmployeeDepartmentHistory and HumanResources.Department tables.

NOTE: OUTPUT on SQL Editor Count: 100 Rows.

4. Write a SQL query to get customerID, storeId, StoreName, SalesPersonID, salesQuota, bonus from Customer, Sales.Store and Sales.SalesPerson Tables.

NOTE:  OUTPUT on SQL Editor Count: 4 Rows.
OUTPUT 

5. Write a SQL query to join SALES.CUSTOMER, SALES.STORE, SALES.SALESPERSON AND SALES.SALESTERRITORY TABLES to retrieve two columns from each table.

NOTE: OUTPUT on SQL Editor Count: 4 Rows.
OUTPUT 

NOTE: Use NORTHWIND DATABASE For Practicing these Questions.

6. Retrieve employeeId, firstname, lastname and TerritoryID for Employees Table where firstName is starts with M and ends with T, joining the EmployeeTerritory Table. [OUTPUT: 3 ROWS]

OUTPUT 
OUTPUT 

7. Retrieve the Customer ID, Company Name, Contact Name, Order ID, Order Date, Employee ID, and Title from the Customers, Orders, and Employees tables, filtering for orders placed within the temporal span of 1996 and 1997, ensuring that the results are sorted in descending order based on the order date. [OUTPUT: 678 ROWS]

 

OUTPUT 

8.Retrieve the Order ID, Quantity, Product ID, Product Name, Category ID, and Category Name from the OrderDetails, Products, and Categories tables, focusing on product categories such as ‘daily products’, ‘beverages’, and ‘seafood’. [OUTPUT: 734 ROWS]

OUTPUT 

9. Retrieve the Product ID, Product Name, Order ID, Unit Price, Quantity, Category Name, Supplier ID, Contact Title, and City from the Products, OrderDetails, Categories, and Suppliers tables, focusing on orderdetails where the quantity ordered exceeds the average quantity, with the additional constraint that the supplier’s city is Tokyo. [OUTPUT: 15 ROWS]

OUTPUT 

10. Compose a SQL query to perform a join operation involving the Shippers, Orders, Employees and Customers tables, extracting two columns from each table. Further add filtering for employees who have handled more than 50 orders. OUTPUT: 510 ROWS]

OUTPUT 
Frequently Asked Questions

Still have a question?

Let's talk

The SQL INNER JOIN retrieves records with matching values in both tables involved in the query. It is one of the most commonly used JOIN operations.

The INNER JOIN compares rows from two tables and returns only those where there is a match based on the specified condition. Example:

sqlCopy codeSELECT Employees.name, Departments.name  FROM Employees  INNER JOIN Departments ON Employees.dept_id = Departments.id;

  • Use indexed columns for better performance.
  • Specify the exact columns to retrieve, rather than using SELECT *.
  • Always include an ON clause to define the relationship between tables.

Yes, you can use aggregate functions like COUNT(), SUM(), and AVG() in conjunction with INNER JOIN. Example:

sqlCopy codeSELECT Departments.name, COUNT(Employees.id) AS EmployeeCount  FROM Employees  INNER JOIN Departments ON Employees.dept_id = Departments.id  GROUP BY Departments.name;

You can use multiple INNER JOIN statements to query data from more than two tables. Example:

sqlCopy codeSELECT Orders.id, Customers.name, Products.name  FROM Orders  INNER JOIN Customers ON Orders.customer_id = Customers.id  INNER JOIN Products ON Orders.product_id = Products.id;

  • Forgetting to define the relationship in the ON clause.
  • Including ambiguous column names without qualifying them with table names.
  • Using non-indexed columns in the JOIN condition, which can slow down the query.

Yes, visit our detailed SQL INNER JOIN Tutorial for examples and step-by-step guidance.

  • INNER JOIN: Returns rows that have matching values in both tables.
  • OUTER JOIN: Returns all rows from one table and matching rows from the other, depending on the type (LEFT, RIGHT, FULL).

Yes, subqueries can be combined with INNER JOIN to add more complexity to the query. Example:

sqlCopy codeSELECT Employees.name  FROM Employees  INNER JOIN (SELECT id FROM Departments WHERE name = ‘IT’) AS IT_Departments  ON Employees.dept_id = IT_Departments.id;

  • Fetching employee names and their respective departments.
  • Combining sales data with customer details to analyze trends.
  • Merging product details with inventory to monitor stock levels.