EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials Oracle Tutorial Inner Join in Oracle
Secondary Sidebar
Oracle Tutorial
  • Joins
    • Joins in Oracle
    • Inner Join in Oracle
    • Oracle Cross Join
    • Left Join in Oracle
    • OUTER Join in Oracle
    • Oracle Full Outer Join
    • Natural Join in Oracle
    • Oracle Self Join
    • Oracle hash join
    • Oracle? Update with Join
  • Basic
    • Oracle Marketing Cloud
    • What is Oracle?
    • Career in Oracle
    • How to Install Oracle
    • Oracle Versions
    • What Is Oracle Database
    • Oracle Data Warehousing
    • Oracle Warehouse Builder
    • Career In Oracle Database Administrator
    • Career In Oracle DBA
    • What is Oracle RAC
    • Oracle DBA
    • Oracle? Vanderbilt
    • What is RMAN Oracle
    • Oracle Database Administration
    • Oracle Operators
    • Oracle Constraints
    • Oracle number
    • Oracle Data Types
    • Oracle UNIQUE Constraint
    • Oracle Check Constraint
  • Oracle SET Operators
    • UNION in Oracle
    • Oracle UNION ALL
    • INTERSECT in Oracle
    • MINUS in Oracle
  • Advanced
    • Timestamp to Date in Oracle
    • Oracle Golden Gate
    • Oracle Virtual Machine
    • Oracle Describe Table
    • Oracle Clauses
    • Oracle Having Clause
    • Oracle?Primavera
    • Oracle FOREIGN Key
    • PIVOT in Oracle
    • Oracle Alter Table
    • Oracle Queries
    • Oracle Views
    • Oracle Window Functions
    • Oracle String Functions
    • Oracle Date Functions
    • Oracle Analytic Functions
    • Oracle Aggregate Functions
    • Select in Oracle
    • INSERT in Oracle
    • DISTINCT in Oracle
    • Function in Oracle
    • Oracle GROUP_CONCAT
    • Oracle INSTR()
    • Oracle CONVERT
    • Oracle LENGTH()
    • Oracle EXISTS
    • Oracle REPLACE()
    • Oracle MERGE
    • Oracle LEAD()
    • Oracle EXTRACT()
    • Oracle LISTAGG()
    • Oracle SYS_CONTEXT()
    • Oracle COALESCE
    • Oracle NVL()
    • Oracle SYSDATE()
    • Oracle?Date Format
    • Oracle SYS_GUID()
    • Oracle WILDCARDS
    • Oracle Synonyms
    • Oracle Subquery
    • BETWEEN in Oracle
    • FETCH in Oracle
    • Oracle Index
    • Oracle Function-based Index
    • Oracle UNIQUE Index
    • Oracle Bitmap Index
    • Oracle Column
    • Oracle Triggers
    • Oracle Procedures
    • Sample Database for Oracle
    • Oracle LIKE Operator
    • ORDER BY in Oracle
    • Oracle ORDER BY DESC
    • GROUP BY in Oracle
    • Oracle GROUP BY HAVING
    • Oracle Aliases
    • Table in Oracle
    • Oracle Temporary Table
    • Oracle? Table Partition
    • Oracle rename table
    • Oracle CTE
    • Cursor in Oracle
    • Oracle LOCK TABLE
    • Oracle Tablespace
    • Oracle CARDINALITY
    • Oracle REGEXP
    • Oracle REGEXP_REPLACE
    • Oracle to_date
    • JSON in Oracle
    • Oracle COMMIT
    • Oracle GRANT
    • Oracle MD5
    • Oracle ROLLBACK
    • Oracle Users
    • Oracle TIMESTAMP
    • IF THEN ELSE in Oracle
    • Oracle While Loop
    • Oracle Clone Database
    • Oracle Backup Database
    • Oracle? XML
    • Oracle XMLAGG
    • Oracle XMLTABLE
    • Oracle Performance Tuning
    • Oracle B Tree Index
    • Oracle fusion
    • Oracle ebs
    • Oracle GRC
    • Oracle ERP
    • Oracle ASM
    • Oracle Cloud
    • Oracle HCM Cloud
    • Oracle Integration Cloud
    • Oracle Jinitiator
    • Oracle pathfinder
    • Oracle VirtualBox
    • Oracle Weblogic Server
    • Oracle decode
    • Oracle Exadata
    • Oracle ZFS
    • Oracle? utilities
    • JDBC Driver for Oracle
    • Oracle? DBA Versions
    • Oracle DBA Salary
  • Interview Questions
    • Oracle Interview Questions
    • Oracle Apps Interview Questions
    • Oracle Apps Technical Interview Questions
    • Oracle Database Interview Questions
    • Oracle Forms Interview Questions
    • Oracle PL/SQL Interview Questions
    • Oracle RAC Interview Questions
    • Oracle SOA Interview Questions

Related Courses

Oracle Course Training

Oracle DBA Certification Course

MongoDB Certification Training

Inner Join in Oracle

By Afshan BanuAfshan Banu

Inner Join in Oracle

Introduction to Inner Join in Oracle

Inner Join in Oracle is a type of join where the first and second table is matched based on some joins predicate or condition by comparing each row of the first table with the rows of the second table to find the pair of rows that satisfies the join condition and all those pair of rows which satisfies the join condition (non-null values also included while comparing the rows) provided in the query are then combined into a row to form a result set to be displayed as the output.

The Syntax of the inner join

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

SELECT column [ , column ] FROM t1
INNER JOIN t2
ON t1.column = t2.column;

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (85,992 ratings)

The inner join visual representation is represented in the below diagram, as in the below diagram the shaded area return as the result of the Oracle Inner Join

inner join

The Oracle Inner Join returns the intersect records of t1 and t2 as a result.

Query Examples for Inner Join

Let’s understand the inner join in detail with the help of some of the query examples:

Example #1

Query example for Inner Join

SELECT employee.employee _id, employee.employee_name, department. department_name
FROM employee
INNER JOIN department
ON employee.employee _id = department.employee _id;

This above Oracle INNER JOIN example will return all rows from the employee table and department table where the employee _id value in both the employee table and department table are matched.

We consider here the hr schema which is the oracle database sample schemas. The hr schema contains COUNTRIES, EMPLOYEES, DEPARTMENTS, JOB_HISTORY, JOBS, LOCATIONS, REGIONS tables, in which we are interested or require EMPLOYEES, DEPARTMENTS and LOCATIONS tables.

The description of these tables are –

Table EMPLOYEES

Inner Join in Oracle Table 1

Table DEPARTMENTS

Inner Join in Oracle Table 2

Table LOCATIONS

Inner Join in Oracle Table 3

Example #2

Inner join example for two Tables

Here we would like to retrieve data from two tables EMPLOYEES and DEPARTMENTS. Here query we write to retrieve the employee’s employee id and employee’s first name from the employee’s table and the department’s name from the department’s table –

SELECT employees. employee_id , employees. first_name, departments.department_name
FROM   employees , departments
WHERE  employees. employee_id = departments. department_id;

Output

Inner Join in Oracle Output 1

The column names with the table are preceded by the Oracle SELECT clause. If a column is common to both tables, then for clarity the column name must be prefixed with the table name. The tables from where the data is to retrieve specify in the from clause with the comma-separated. In the WHERE clause specify the join condition. The relation between employees and department tables is determined by the values in the employee_id common column in both tables must be equal and this relation is referred to as an Equi Join. Commonly primary key and foreign keys are involved in this relation.

Example #3

Inner join example of two tables with Aliases

Let us rewrite the above example by using Table Aliases as –

SELECT e. employee_id , e. first_name,
d. department_name
FROM   employees e , departments d
WHERE  e. employee_id = d. department_id;

Output

Inner Join in Oracle Output 2

As in the above query, the table aliases are created in the from clause after the column name and Instead of writing a full table name in the select clause before each column use the table Aliases as e and d in the above query.

Example #4

Inner join example for two tables with Adding Additional Clauses

Next query example where we add additional clauses to our Oracle SELECT statement in where clause for adding aggregations, restricting the rows returned, defining the sorting order and so on. Here is an example query that retrieves all employees who are working in departments Finance, Manufacturing, and Construction.

SELECT e. employee_id , e. first_name,
d. department_name
FROM   employees e , departments d
WHERE  e. employee_id = d. department_id
AND d. department_name in ( 'Finance', 'Manufacturing', 'Construction' );

Output

Output 3

Example #5

Inner join example of two tables with an order by clause

Next query example, we rewrite the above query by adding the order by clause, therefore displaying the employee’s data sorted by the department name.

SELECT e. employee_id , e. first_name,
d. department_name
FROM   employees e , departments d
WHERE  e. employee_id = d. department_id
AND d. department_name in ('Finance', 'Manufacturing', 'Construction')
ORDER BY d. department_name;

Output

Output 4

Example #6

Inner Join example for multiple tables join

Sometimes to get the information we may need to join more than two tables. Next, we write the query example to join multiple tables. Consider the example we want to retrieve the employee’s first name from the employee table, the name of the department from department table, and the country’s id of the location where this employee works in the location table.

SELECT e. first_name, e. salary , d. department_name, l. city, l. country_id
FROM  employees e , departments d , locations l
WHERE e. employee_id = d. department_id
AND
d. location_id = l. location_id;

Output

Output 5

To join four or more tables the same concept applies by adding the table name in FROM clause of an oracle and apply the join condition at the WHERE clause of an oracle.

Conclusion

Inner join is one of the types of join in the oracle database. An inner join used to join the multiple tables and return those rows for which the join condition is or are true. Commonly the primary key and foreign key are involved in inner join to create a relationship between tables.

Recommended Articles

This is a guide to Inner Join in Oracle. Here we discuss the introduction to Inner join along with some examples in detail. You can also go through our other suggested articles to learn more–

  1. Joins in MySQL
  2. Oracle Queries
  3. Oracle Data Warehousing
  4. What Is Oracle Database
  5. Learn the Different Types of Oracle Versions
  6. Inner Join vs Outer Join | Top Differences
  7. Learn the MySQL Outer Join
Popular Course in this category
Oracle Training (14 Courses, 8+ Projects)
  14 Online Courses |  8 Hands-on Projects |  120+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

Oracle DBA Database Management System Training (2 Courses)4.9
All in One Financial Analyst Bundle- 250+ Courses, 40+ Projects4.8
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more