Hi Oracle folks,
In previous blog, I tried to explain SQL Join. Today we will talk about a very important Join, which is INNER JOIN.
INNER JOIN:
Inner Join helps to fetch or select the data (rows and columns) from multiple tables. When we join two tables using Inner join, it returns all those rows which satisfy joining condition which means all the matching or common rows from both the tables.
You can understand it more clearly in the above picture. Inner Join generates output same as area highlight/intersection in above picture.
In case of Inner Join, both the syntaxes mentioned below are true.
Syntax-1:
FROM table1 a INNER JOIN table2 b
ON
<join condition> [WHERE…];
Syntax-2:
FROM table1 a,table2 b
WHERE <join condition>…;
To understand it more clearly, let’s do some examples.
For the demonstration purpose, I have mimicked the EMPLOYEES table of HR schema as EMP.
You can use the below query to create EMP table in your HR schema.
CREATE TABLE emp
AS
( SELECT
a.department_id,
a.emp_id AS employees_id,
b.first_name
FROM
(
SELECT
department_id,
MAX(employee_id) emp_id
FROM
employees a
GROUP BY
department_id
) a
INNER JOIN employees b ON a.emp_id = b.employee_id
WHERE
ROWNUM < 8
);
SELECT
*
FROM
emp;
EMPLOYEES_ID | FIRST_NAME | DEPARTMENT_ID |
---|---|---|
113 | Luis | 100 |
119 | Karen | 30 |
178 | Kimberely | (null) |
102 | Lex | 90 |
202 | Pat | 20 |
204 | Hermann | 70 |
206 | William | 110 |
In this table we have 3 columns.
EMPLOYEES_ID
FIRST_NAME
DEPARTMENT_ID
We will use DEPARTMENTS table of HR schema to add department names of employees in EMP table.
Below are the queries with INNER JOIN.
--Query-1
SELECT
a.*, --all columns from EMP table
b.department_name --department name from DEPARTMENTS table
FROM
emp a INNER JOIN departments b
ON a.department_id = b.department_id;
--Query-2
SELECT
a.*, --all columns from EMP table
b.department_name --department name from DEPARTMENTS table
FROM
emp a,departments b
where a.department_id = b.department_id;
Let me explain a bit.
In this query, I am using TABLE ALIAS, a and b.
a for EMP table and b for DEPARTMENTS table.
Using Table alias, I am fetching all the columns from EMP table and only Department_Name from DEPARTMENTS table.
As Joining condition, I am using Department_ID from both the tables as it is a column which is common in both the tables.
As we are using EQUALITY operator (=) in the joining condition so this joining condition is EQUIJOIN.
When we run any of the above two queries, Oracle will generate output as shown below.
EMPLOYEES_ID | FIRST_NAME | DEPARTMENT_ID | DEPARTMENT_NAME |
---|---|---|---|
202 | Pat | 20 | Marketing |
119 | Karen | 30 | Purchasing |
204 | Hermann | 70 | Public Relations |
102 | Lex | 90 | Executive |
113 | Luis | 100 | Finance |
206 | William | 110 | Accounting |
You can see in the output that we do not have record with First_Name “Kimberely”. Because we have a Department_ID for this record and INNER JOIN generates only matching records based on the Joining Condition. For this record, Joining Condition is not true.
That’s all about INNER JOIN.
If you have any questions/suggestions, please comment below or write me an email.
If you like this blog, do share with your friends and colleagues on your social media.
For more updates join my facebook group and do like my facebook page.
Thank you,
Kapil Kumar