• Skip to primary navigation
  • Skip to main content

Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

  • Home
  • HTML
    • Tags in HTML
    • HTML Attributes
  • OA Framework
    • Item in OAF
    • Regions in OAF
    • General OAF Topics
  • Oracle
    • statement
    • function
    • clause
    • plsql
    • sql
You are here: Home / Oracle / sql / JOINS in Oracle SQL – PLSQL

JOINS in Oracle SQL – PLSQL

October 25, 2012 by techhoneyadmin

As the name suggests a join in Oracle SQL / PLSQL is used to join or combine 2 or more table rows. A join can be performed whenever we have more than one table in the FROM clause of any SQL SELECT statement.

Different kinds of JOINS are:

  1. Inner Join
  2. Outer Join

1. Inner JOIN:

Inner join is the most common type of join used in SQL queries. An INNER JOIN returns all the rows from multiple tables whenever the join condition is met. It’s also known as “EQUI JOIN”

Suppose have a table named ‘employee’ in the database as shown below.

Employee_ID Employee_Name Salary Department
101 Emp A 10000 Sales
102 Emp B 20000 IT
103 Emp C 28000 IT
104 Emp D 30000 Support
105 Emp E 32000 Sales

Also we have one more table ‘comm’ as shown below:

Emp_ID Commission_Percent
102 20
103 20
104
105 10
106 15
107 25

Let’s join ‘employee’ and ‘comm’ tables using INNER JOIN as:

SELECT employee_id
       ,employee_name
       ,salary
       ,department
       ,c.commission_percent
FROM employee e
     ,comm c
WHERE e.employee_id = c.emp_id;

The result of the above query will be:

Employee_ID Employee_Name Salary Department Commission_Percent
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Here we can see that the record for employee_id = ‘101’ from ‘employee’ table and records for emp_id = ‘106’ and ‘107’ from ‘comm’ table have not been fetched by the SELECT statement as they are not available in both the tables.

Also note that we have created alias names for tables ‘employee’ and ‘comm’ as ‘e’ and ‘c’ respectively.


2. Outer JOIN:

The outer join returns all the rows from one table and only those rows from the second table where the condition of the join is met.
Let’s take an example for easy understanding:

Assume that we have a new table namely ‘employee’ as shown below:

Employee_ID Employee_Name Salary Department
101 Emp A 10000 Sales
102 Emp B 20000 IT
103 Emp C 28000 IT
104 Emp D 30000 Support
105 Emp E 32000 Sales

Also we have one more table ‘comm’ as shown below:

Emp_ID Commission_Percent
102 20
103 20
104
105 10
106 15
107 25

If we write a query for OUTER JOIN as:

SELECT employee_id
       ,employee_name
       ,salary
       ,department
       ,c.commission_percent
FROM employee e
     ,comm c
WHERE e.employee_id = c.emp_id(+);

Once we have run the above code following will be the result:

Employee_ID Employee_Name Salary Department Commission_Percent
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Here we can see that the records for ‘employee_id = 101’ from ‘employee’ table are also fetched by query but the same was not available in ‘comm’ table.

Hence the above query returns all the rows from ‘employee’ table but returns only those rows from ‘comm’ table where the condition for join is met i.e. where ‘empoloyee_id’ from ‘employee’ table = ‘emp_id’ from ‘comm’ table.

If we change our query as shown below:

SELECT c.emp_id
       ,employee_name
       ,salary
       ,department
       ,c.commission_percent
FROM employee e
     ,comm c
WHERE e.employee_id(+) = c.emp_id;

We get the following output:

Emp_ID Employee_Name Salary Department Commission_Percent
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10
106 15
107 25

Here we can see that the records for ‘emp_id = 106’ and ‘107’ from ‘comm’ table are also fetched by query but the same was not available in ‘employee’ table.

Hence the above query returns all the rows from ‘comm’ table but returns only those rows from ‘employee’ table where the condition for join is met i.e. where ‘empoloyee_id’ from ‘employee’ table = ‘emp_id’ from ‘comm’ table.


Filed Under: sql Tagged With: how to use inner and outer join in oracle database query, how to use inner and outer join in oracle plsql, how to use inner and outer join in oracle sql, inner and outer join in oracle plsql, inner and outer join in oracle sql, JOINSPLSQL, syntax and example of inner and outer join in oracle database query, syntax and example of inner and outer join in oracle plsql, syntax and example of inner and outer join in oracle sql, TableJoinsPLSQL, using inner and outer join in oracle database query, using inner and outer join in oracle plsql, using inner and outer join in oracle sql

Copyright © 2023 · Parallax Pro on Genesis Framework · WordPress · Log in