What is INNER JOIN in SQL?
The JOIN clause is the way of combining records from different tables that are related logically in RDBMS.
Different RDBMS like MySQL, MS SQL Server, Oracle, etc. support the JOIN clause.
Generally, there are four types of Joins in SQL. These are:
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- OUTER JOIN
On the opposite, the OUTER JOIN returns all records from both tables.
The example of SQL INNER JOIN
For our first example, I will use two tables that store employee information in a departmental store and order data. Both these tables are related by employee’s ID.
Following are three queries that are used to retrieve the records of two tables and the third uses an INNER JOIN that combines the records of these two tables into one recordset:
SELECT id, emp_name, emp_age, emp_salary FROM sto_employees; SELECT emp_id, prod_id FROM sto_orders; SELECT * FROM sto_employees JOIN sto_orders ON sto_employees.id=sto_orders.emp_id;
The record-sets for these queries:
You can see that the two tables are combined by IDs and only those records are returned which IDs exist in both tables. For instance, employee ID 5, 6, and 7 do not exist in the orders table, so the INNER JOIN query did not retrieve the employee records from the employee table.
Also, notice that I just used the “JOIN” keyword rather than “INNER JOIN” in the query.
This query will produce the same result:
SELECT * FROM sto_employees INNER JOIN sto_orders ON sto_employees.id=sto_orders.emp_id;
Using table alias in INNER JOIN query
In the following example, I have used the table alias to refer to respective columns in the INNER JOIN clause. Have a look:
SELECT ORD.emp_id, EMP.id, emp_name, emp_age, emp_salary FROM sto_employees EMP INNER JOIN sto_orders ORD ON EMP.id=ORD.emp_id;
Not only tables alias are used for column IDs in the INNER JOIN clause but also in the SELECT FROM part.
The example of using GROUP BY clause with INNER JOIN
The GROUP BY clause can be used after the JOIN clause. In this query, we will get the SUM of salaries of those employees whose records exist in the ORDERS table.
For that, the SUM aggregate function is used in the SELECT statement and results are “GROUPED BY” employee names.
To make sure only those employees’ records are retrieved that exist in the sto_orders table, the INNER JOIN clause is used as follows:
SELECT emp_name, SUM(emp_salary) As "Total Salary" FROM sto_employees EMP INNER JOIN sto_orders ORD ON EMP.id=ORD.emp_id GROUP BY emp_name;
Sorting the results by ORDER BY clause with INNER JOIN
The query fetches the record set sorted from highest to lowest salaries of employees:
SELECT * FROM sto_employees JOIN sto_orders ON sto_employees.id=sto_orders.emp_id ORDER BY emp_salary DESC;
The example of using multiple INNER JOINs
To show that, I am using another table, sto_products that is related to sto_orders by prod_id column.
See how two INNER JOIN clauses are used for combining three records for all matched values:
SELECT * FROM sto_orders INNER JOIN sto_employees ON sto_employees.id=sto_orders.emp_id INNER JOIN sto_products ON sto_products.prod_id = sto_orders.prod_id;
Adding WHERE clause for filtering results in three tables
In this query, we will filter the results of three joined tables by using a SQL WHERE clause. The query returns the records of prod_id = 2 as follows:
SELECT * FROM sto_orders ORD INNER JOIN sto_employees EMP ON EMP.id=ORD.emp_id INNER JOIN sto_products PROD ON PROD.prod_id = ORD.prod_id Where PROD.prod_id = 2;
You can see the table aliases are used for all tables in the query.
Filtering aggregated results by HAVING clause
SELECT emp_name, SUM(emp_salary) As "Total Salary" FROM sto_employees EMP INNER JOIN sto_orders ORD ON EMP.id=ORD.emp_id GROUP BY emp_name HAVING SUM(emp_salary) >=3500;
You can compare it with the GROUP BY example in the above section and see that the query returned three records.