Inner Join In SQL.
This is the fifth tutorial in the Join series and 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.
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.
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 table (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 side 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 condition 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.
When to use ON and USING clause
Hope syntax is clear.
Let’s do some practical exercise. I’ll be using the same table which we have been using in all our JOIN tutorial 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 column 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 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.
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.
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 try to sum up all the concepts of INNER JOIN hope you enjoyed reading.
Do Share this article with your friends on social networking. You can also watch my Video for the same. Thanks!
SQL Script and Presentation used
You can DOWNLOAD SQL script and presentation used in the Video and in this article.
SQL Script used in Video and in this article
Presentation used in Video and in this article
Do Not Drink & Drive Use My Uber Code "UberRebellionRider"
SignUp using my referral code and get first ride worth $20 FREE