Inner Join in SQL: Syntax, Examples and Use Cases

A Join is any operation performed on database tables to collect data from related tables according to common fields or columns. You will come across two major types of joins in SQL: inner join and outer join. Dig into this article to learn more about inner join in SQL. 

What is Inner Join in SQL?

The inner join in SQL is useful for combining records from two related tables according to common columns. The inner join query can compare every row of the first table with every row of the second table to identify all pairs of rows that meet the join predicate. When the join-predicate is met, the column value of every matched row pair of both tables is combined into an outcome row. 

According to SQL inner join meaning, it is the default join in SQL. Even if "Join" is used instead of "Inner Join," tables will get joined according to matching records of common columns. The inner join in SQL is also frequently used and called the Equijoin. 

How Does It Differ from other Types of SQL Joins

The biggest difference between an outer join and an inner join in SQL is that the inner join only keeps information from both tables related to one another. But the outer join will also keep information unrelated to the other table in the resulting table. 

Learn: Full Stack Development with Cloud for Web and Mobile

SQL Inner Join in Action

Let us consider a hypothetical example to understand SQL inner join in action. Let us begin with a list of pizza joints in different cities.

Now let’s talk about the food items sold by each joint and the number of units sold.

You will be able to combine the two tables by using the inner join query. It will ensure that you can view the items and number of units delivered by pizza outlets in different cities. 

Understanding Inner Join Syntax in SQL

The inner join syntax in SQL is as follows: 

SELECT tableA.column1, tableB.column2... 
FROM tableA
INNER JOIN tableB
ON tableA.id_field = tableB.id_field;

ON tableA.id_field = tableB.id_field;

Explanation of the ON Clause in Inner Join in SQL

The join condition for any natural join is an equijoin of all columns with the same name. The ON clause is used to mention arbitrary conditions or particular columns to join. The inner join in SQL condition is distinguished from other search conditions. 

The ON clause makes it easy to comprehend code. The ON clause is useful for joining columns with different names. It enables specifying join conditions different from any search or filter conditions in the WHERE clause. 

Read: What is Arrays in Java | Everything You Need to Know

SQL Inner Join on Three Tables

Now, let's consider the two tables from the inner join in SQL in action example. With that, we are going to add some more data related to water parks. Let us consider the number of pizzas sold at several water parks from different outlets. Using an SQL inner join query, you will be able to combine the data from all three tables and acquire several valuable insights. 

Real-World Examples of Inner Join SQL

Some real-world examples of inner join SQL are as follows:

• Order Tables for Sales Analysis

  You can use an inner join in SQL query to calculate total product sales. Apart from that, you will be able to display the top products by sale and the products with the lowest production cost. Moreover, you will be able to determine the sales across different product categories. 

  Let us reveal the code for displaying sales products by figures:

  Select
  	p.EnglishProductName AS product_name,
  	SUM(f.SalesAmount) AS sales_amount
  from DimProduct p
  inner join FactInternetSales f on p.ProductKey = f.ProductKey
  Group By p.EnglishProductName
  Order by SUM(f.SalesAmount) DESC
  

• Salary Tables for Payroll Calculations

  Suppose you want to find the monthly salary of employees from a table where the annual salary is present. You will have to divide the annual salary by 12 and create an alias column as Monthly salary to find out the monthly salary of each employee. You will have to use the following inner SQL join query for it:

  SELECT emp_name, (emp_An_salary/12) AS' Monthly Salary',
  emp_An_Salary AS 'Annual Salary' FROM GFG_salary ;
  Use this inner join query to round the salary by 2 decimal points:
  SELECT emp_name, round(emp_An_salary/12,2) AS 'Monthly Salary' ,
  emp_An_Salary AS 'Annual Salary' FROM GFG_salary
  Use the following query to find the monthly salary of each employee:
  SELECT emp_name, round(emp_An_salary/12,2) AS 'Monthly Salary' , emp_An_Salary AS 'Annual Salary'
  FROM gfg_salary
  WHERE
  emp_ID = 1 
  OR
  emp_name = 'EmpABC' ;	

  Find out: Why Python & JavaScript are Loved by Developers

Best Practices for Using Inner Join in SQL

Some of the best practices for using inner join SQL are as follows:

• Don't let the large table join another large table. Instead, you should let the large table join the small table. 
• Always choose the inner join over the cross join.
• Avoid the or condition. Instead, go for the equality condition and the and condition in join conditions.

Tips for Optimizing SQL Inner Join Queries

Some tips for optimizing inner join queries in SQL are as follows:

• Index All the Predicates in JOIN, ORDER BY, WHERE, and GROUP BY Clauses

  Typically, WebSphere Commerce largely relies on indexes to increase SQL performance and scalability. Without appropriate indexes, SQL queries may result in table scans, which might have negative effects on locking or performance. It is advised to index all predicate columns. The case where column data has relatively little cardinality is the exception.

• Avoid the Use of Functions in Predicates 

  A database cannot use the index if a function is present on the column. Due to the function UPPER(), database optimizers don't use the index on COL1. 

• Don't Add Unnecessary Columns in the Select Clause

  Instead of using SELECT *, try specifying the columns in the select clause. The unnecessary column spaces add extra load to the database. It slows down the whole system along with the single SQL.

Check out: SQL Interview Questions

Common Pitfalls to Avoid When Using Inner Join SQL

Some common mistakes to avoid while using inner join in SQL are as follows:

• Missing Join Condition

  Missing the inner join condition in your query is a major mistake to avoid. The join condition specifies the relationship between tables. If you omit the join condition, it will automatically lead to a cross join.

• Unnecessary Joins

  Unnecessary inner joins don't contribute to the query logic or the result set. Therefore, don't add unnecessary joins and avoid adding complexity.

• Poor Formatting

  Poor formatting is another inner join query mistake. The right formatting is necessary for improved readability of your inner join query. You need to focus more on formatting when you have complex joins with multiple conditions and tables. 

Conclusion

The inner join in SQL enables you to query data from two or more related tables. It is the most commonly used join in SQL. Learn about inner SQL joins in detail to apply it in real-life scenarios.