SELECT columns
FROM Left_Table
INNER JOIN Right_Table
ON Left_Table.column1 = Right_Table.column2;
Explanation:
The INNER JOIN is equivalent to the default JOIN clause when no type is explicitly stated. Both can be used interchangeably to achieve the same result.
Joins in SQL involve two kinds of operations:
| Feature | INNER JOIN | Standard JOIN (CROSS JOIN) |
|---|---|---|
| Join Condition | Explicitly specified using ON clause. | Implicitly combines all rows from both tables. |
| Result Rows | Only rows where the join condition is met. | All possible combinations of rows from both tables. |
| Purpose | Combining tables with shared data. | Creating a Cartesian product of all rows. |
| Common Uses | Merging customer and order data. | Testing scenarios with multiple variables. |
| Example | SELECT * FROM customers INNER JOIN orders ON customers.customerID = orders.customerID | SELECT * FROM customers, orders |
Key Differences between INNER JOIN and Standard JOIN (CROSS JOIN):
ON clause to match rows, whereas Standard JOIN (CROSS JOIN) combines every row from both tables by default.
Learn how to use SQL INNER JOIN with ON clause effectively through an example that demonstrates matching records from two related tables using a shared column.
Input Data
EmployeeName Table-(T1) & Position Table-(T2) ID NAME ID TITLE TEAM 10 Sandy 20 Sales Mgr 5 20 Sam 30 Clerk 10 30 Cindy 30 Manager 2 40 Sales Rep 7 50 Sr. Manager 11
-- SQL INNER JOIN Query Using ON Clause
SELECT T1.NAME,
T1.ID,
T2.ID,
T2.TITLE
FROM EmployeeName T1
INNER JOIN Position T2
ON T1.ID = T2.ID
ORDER BY T1.ID,
T2.TITLE;
The above SQL query performs an INNER JOIN on the EmployeeName and Position tables using the ID column. This is equivalent to a standard join written as follows:
-- Equivalent SQL Join Syntax
SELECT T1.NAME,
T1.ID,
T2.ID,
T2.TITLE
FROM EmployeeName T1,
Position T2
WHERE T1.ID = T2.ID
ORDER BY T1.ID,
T2.TITLE;
Result ID NAME ID TITLE 20 Sam 20 Sales Mgr 30 Cindy 30 Clerk 30 Cindy 30 Manager
This INNER JOIN result set returns all matching rows between EmployeeName and Position tables. Note that ID values 10, 40, and 50 are excluded because they do not have matching entries in both tables.
From the result, we can identify unmatched data — e.g., employee Sandy has no assigned position, and roles like Sales Rep and Sr. Manager remain unfilled. This illustrates a common use case for SQL INNER JOIN in data validation and relationship analysis.
Understand the key differences between the ON clause and the WHERE clause in SQL. This comparison table helps you learn how these clauses function in JOIN operations and filtering result sets.
| Feature | ON Clause | WHERE Clause |
|---|---|---|
| Purpose | Specifies the join condition between tables | Filters rows from the resulting joined table |
| Location | Part of the JOIN statement | Part of the SELECT statement |
| Execution Order | Executed before the WHERE clause | Executed after the JOIN clause |
| Applicability | Used with JOIN statements | Used with SELECT, UPDATE, and DELETE statements |
| Result Rows | Affects which rows are included in the join | Affects which rows are returned from the already joined table |
| Example | SELECT * FROM customers INNER JOIN orders ON customers.customerID = orders.customerID | SELECT * FROM customers WHERE country = 'USA' |
Using GROUP BY with INNER JOIN allows grouping the result set by one or more columns after combining rows from multiple tables. This is often used with aggregate functions like SUM(), COUNT(), and AVG() to produce summarized insights from related tables.
SELECT T1.ID,
T1.NAME,
SUM(T1.TEAM) AS EMPCOUNT
FROM EmployeeName T1
INNER JOIN Position T2
ON T1.ID = T2.ID
GROUP BY T1.ID
ORDER BY T1.ID, T2.TITLE;Without GROUP BY clause:
ID NAME EMPCOUNT
20 Sam 5
30 Cindy 10
30 Cindy 2With GROUP BY clause:
ID NAME EMPCOUNT
20 Sam 5
30 Cindy 12Scenario: Analyze employee performance and satisfaction across departments, projects, and managers.
Tables Involved:
SELECT
employees.name AS employee_name,
departments.name AS department,
projects.name AS project,
performance_reviews.rating,
satisfaction_surveys.satisfaction_score
FROM employees
INNER JOIN departments
ON employees.departmentID = departments.departmentID
INNER JOIN projects
ON employees.employeeID = projects.managerID
INNER JOIN performance_reviews
ON employees.employeeID = performance_reviews.employeeID
INNER JOIN satisfaction_surveys
ON employees.employeeID = satisfaction_surveys.employeeID
WHERE departments.name = 'Sales';Scenario: Identify top-selling products across different stores along with supplier and stock information.
Tables Involved:
SELECT
products.name AS product_name,
suppliers.name AS supplier,
stores.name AS store,
SUM(sales.quantity_sold) AS total_sales,
SUM(inventory.quantity_in_stock) AS current_stock
FROM products
INNER JOIN suppliers
ON products.supplierID = suppliers.supplierID
INNER JOIN sales
ON products.productID = sales.productID
INNER JOIN stores
ON sales.storeID = stores.storeID
INNER JOIN inventory
ON products.productID = inventory.productID
AND stores.storeID = inventory.storeID
WHERE suppliers.name = 'Acme Inc.'
GROUP BY product_name, supplier, store
ORDER BY total_sales DESC;Ambiguous Column Names: Always prefix columns with table aliases when names repeat.
Missing Matches: INNER JOIN excludes unmatched rows. Use LEFT JOIN if you must retain all left-table rows.
Performance Considerations:
WHERE clauses.Practical Use Cases:
INNER JOIN is a powerful SQL operation for combining data from multiple tables based on specified conditions. It forms the backbone of relational databases, enabling efficient data retrieval and analysis. Mastering INNER JOINs empowers you to delve deeper into your database, uncovering valuable insights and forging connections that bring your data to life. So, unleash your inner data detective and start querying with confidence!
The Product Owner role has shifted from just being a requirements proxy to a strategic,…
Business Value: In the world of Agile development, the user story has long been the…
The SAFe Scrum Master certification has become one of the most sought-after credentials for Agile…
The Professional Scrum with Kanban (PSK) course enhances your organization's ability to deliver value efficiently…
Effective User interviews play a crucial role in Scrum methodology, helping Product Owners and Scrum…
Product Owners should be well-versed in various user research tools and techniques to effectively understand…