This is the fifth tutorial in the Join series. Till now we have seen Natural Join, Right Outer Join, Full Outer Join and left Outer Join.
And in this tutorial we will cover the concepts of INNER Join. 

Definition
  Inner Join is the join which returns all those rows from both the participating tables that satisfy the Join condition or for that matter expression of ON/USING clause.

Syntax

  SELECT column names FROM 

  table1 INNER JOIN / JOIN table 2

  ON(expression) or USING(column_name)

  WHERE(expression) ORDER BY column names;

In the first line of our syntax we have our SELECT statement where you specify all those columns from both the participating tables (table1 and table2) whose data you want to fetch in your result set. The SELECT statement is followed by FROM keyword.

In the second line of our syntax we have our JOIN clause which is INNER join as obvious. You can either write INNER JOIN or simple JOIN as both are permissible and perform the same task. On both sides of our Join clause we have our tables which are TABLE 1 and TABLE 2.

Followed by our join clause we have our Join condition. Basically we have two types of join conditions that we can use one at a time but never together. These two join conditions are ON and USING. Usage of each join condition depends on certain conditional parameters. 

And at the end of the syntax we have our WHERE and ORDER BY clause. 

*Note here if you are using ORDER BY clause then it must be the last statement of your query.

Let’s do some practical exercises. I’ll be using the same table which we have been using in all our JOIN tutorials – emp and dept.
(for tables you can refer to Table for SQL Joins tutorial)

QUERY 1: INNER JOIN with ON clause

SELECT emp_name,dept_name FROM emp INNER JOIN dept ON(emp.EMP_ID = dept.EMP_ID);

Here in this query we are selecting emp name from emp table and dept name from dept table while in JOIN condition which in this case is the ON clause. We are comparing emp_id column of both the tables.
As here in this query we are using ON join condition thus we can use any column as expression of ON clause as long as columns share same data types. The name of the column doesn’t matter here.
For example we have dept_id column in dept table which shares same data type as of emp_id column of emp table which is a NUMBER thus here in our ON clause we can use this dept_id column too.

SELECT emp_name,dept_name FROM emp INNER JOIN dept ON(emp.EMP_ID = dept.DEPT_ID);

QUERY 2: INNER JOIN with USING clause.

We already know that we can use USING clause when the columns in join condition share same name and same data type and are compared only using equal to ( = ) comparison operator and no other comparison operator such as greater than, less than etc.

SELECT emp_name,dept_name FROM emp INNER JOIN dept USING(EMP_ID);

Here we used emp_id column in USING clause because it’s common in both the tables (foreign key relation in emp and dept table) and shares same name as well as data type. We can easily compare its value using equal to operator in both the tables as we did in our first query where we used emp_id column of both the tables in the expression of ON clause.

QUERY 3: INNER JOIN with WHERE clause.

We use WHERE clause to limit the result of a Query, similarly you can use WHERE clause here with Inner join to do the same. Say you want to see the name and departments of only those employees who have a salary of less than 50000. For that you just have to add the WHERE Clause right after the JOIN condition in the query. 

For example 

SELECT emp_name,dept_name FROM emp INNER JOIN dept USING(EMP_ID) WHERE emp.emp_salary < 50000;

QUERY 4: INNER JOIN with ORDER BY clause.

Feel free to use ORDER BY clause if you want to sort the result returned by your query in Ascending or Descending order.
NOTE here ORDER BY clause must be the last statement of a QUERY.
ORDER BY clause by default sorts the result in ascending order. But if you want to arrange the result in Descending order then you have to specify it by using DESCENDING or DESC keyword with the ORDER BY clause.

For example 
Sort the result in ascending order according to emp_name column

SELECT emp_name,dept_name FROM emp INNER JOIN dept USING(EMP_ID) ORDER BY emp_name;

Sort the result in descending order according to emp_name column.

SELECT emp_name,dept_name FROM emp INNER JOIN dept USING(EMP_ID) ORDER BY emp_name DESC;

In this tutorial I tried to sum up all the concepts of INNER JOIN hope you enjoyed reading.
Do Share this article with your friends on social network. You can also watch my Video for the same. Thanks!