SQL 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 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:

Queries

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:

The query:

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;

The result-set:

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;

The Output:

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;

The result-set:

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;

The result:

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:

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.