Hit enter after type your search item

Things to Know about 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

The INNER join, which is the topic of this tutorial, returns the records of matched values in both tables that are joined.

On the opposite, the OUTER JOIN returns all records from both tables.

Did you know? The INNER JOIN is the default join type. If you do not specify the keyword “INNER” and just use the JOIN, it is taken as an INNER JOIN.

The example of SQL INNER JOIN

For our first example, I will use two tables that store employee information in a departmental store and orders 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:

Queries


The record-sets for these queries:

SQL INNER JOIN

You can see, the two tables are combined by IDs and only those records are returned which IDs exist in both tables. For instance, the employee ID 5, 6 and 7 do not exist in the orders table, so INNER JOIN query did not retrieve the employee records from employee table.

Also, notice that I just used “JOIN” keyword rather than “INNER JOIN” in the query.

This query will produce the same result:

Using table alias in INNER JOIN query

In the following example, I have used the table alias to refer respective columns in the INNER JOIN clause. Have a look:

The query:


The result-set:

SQL INNER JOIN Alias

Not only tables alias are used for columns IDs in the INNER JOIN clause but also in SELECT FROM part as well.

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 whom 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. For making sure only those employees records are retrieved that exist in the sto_orders table, the INNER JOIN clause is used as follows:


The Output:

SQL INNER JOIN GROUP BY

Sorting the results by ORDER BY clause with INNER JOIN

For sorting the results in ascending or descending order, you may use the ORDER BY clause with INNER JOIN as shown in this example. The query fetches the record set sorted from highest to lowest salaries of employees:


INNER JOIN ORDER BY

The example of using multiple INNER JOINs

You may also combine more than two tables by using the multiple INNER JOIN clauses. For showing 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:


The result-set:

INNER JOIN three table

Adding WHERE clause for filtering results in three tables

In this query, we will filter the results of three joined tables by using a WHERE clause. The query returns the records of prod_id = 2 as follows:


The result:

INNER JOIN WHERE

You can see the table aliases are used for all tables in the query.

Filtering aggregated results by HAVING clause

The query shows using the HAVING clause with GROUP BY and INNER JOIN:


INNER JOIN HAVING

You can compare it with the GROUP BY example in above section and see that query returned three records.

This div height required for enabling the sticky sidebar