EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials Database Management Tutorial PL/SQL join
Secondary Sidebar
Bias-Variance

MongoDB vs Postgres

Oracle Java

Data Analysis Tools

MongoDB vs Cassandra

Data Structure Interview Questions

PL/SQL join

PL_SQL join

Definition of PL/SQL join

PL/SQL provides the different types of functionality to the user, in which that JOIN is one of the functionalities provided by the PL/SQL. Basically, JOIN is used to retrieve the records from more than one table. By using JOIN we can merge the result from different tables as per our requirement. Basically JOIN is a SQL statement and by using this SQL statement we can combine the different columns from the different tables as per the requirement. When we need to implement the JOIN at that time we must have the primary key and foreign key in tables then and then we can implement the JOIN.

Syntax:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

select colm name 1, colm name2, ….colm name N from specified first table name(Table 1) JOIN specified second table name(Table 2) ON specified first table name.colm name= specified second table name.colm name;

Explanation:

In the above syntax, we use JOIN with different parameters as follows.

  • colm name 1: It is used to specify the column name from the first table that we want. Similarly, we can write multiple column names as per our requirements.
  • specified first table name: After the FROM clause we need to specify the first table name that is specified first table name.
  • JOIN: JOIN is a keyword and it is used to combine the two different tables.
  • specified second table name: After the JOIN keyword we need to specify the second table name that is specified second table name.
  • ON: ON is a keyword that we can also call clause and it is used to combine the two different columns from different tables.

Types of join in PL/SQL

Now let’s see the different types of JOIN in PL/SQL with examples as follows.

First, we need to create two different tables by using the create table statement as follows. One important point during the table creation, the left-hand table must have a primary key and the right-hand table must have a foreign key.

create table stud1(roll_no number(10) not null, name varchar2(30), city varchar(30), constraint stud_pk primary key(roll_no));

Explanation

By using the above statement we created a stud1 table with different attributes and data types as shown.

create table dept(dept_id number(10) not null, dept_name varchar2(30), roll_no number(10) not null);

Explanation

Now insert some records into both tables to implement JOIN. Here we need to perform the insert operation as follows.

In the first table that stude1, we inserted five records as shown in the below screenshot as follow.

pl sql join 1

Similarly, we inserted five records into the second table that is dept as shown in the below screenshot as follows.

pl sql join 2

Now we have two tables with records, now perform different types of JOIN as follows.

1. INNER JOIN

This is a very simple type of JOIN; it is also called a simple join. By using this type we return the only matching rows from both tables. In another word, we can say that we intersect between the two tables as follows.

Syntax:

select colm name 1, colm name2, ….colm name N from specified first table name(Table 1) INNER JOIN specified second table name(Table 2) ON specified first table name.colm name= specified second table name.colm name;

Example

In this, we use already created table stud1 and dept.

select stud1.roll_no, stud1.name, dept.dept_name from stud1 inner join dept on
stud1.roll_no=dept.roll_no;

Explanation

In the above example, we try to implement the INNER JOIN as shown in the above SQL statement. The INNER JOIN returns all matching rows from both tables where roll_no is matching in both tables. The final output of the above statement we illustrated by using the following screenshot as follows.

PL SQL bulk collect 4

2. LEFT OUTER JOIN

This is the second type of join, by using this type we can return all rows from the first table that is a left-hand table.

Syntax:

select colm name 1, colm name2, ….colm name N from specified first table name(Table 1) LEFT OUTER JOIN specified second table name(Table 2) ON specified first table name.colm name= specified second table name.colm name;

Example

select stud1.roll_no, stud1.name, dept.dept_name from stud1 left outer join dept on
stud1.roll_no=dept.roll_no;

Explanation

In the above example, we try to implement the left outer join. In this example, we return all rows from the stud1 table and only matching rows from the dept table. The final output of the above statement we illustrated by using the following screenshot as follows.

PL SQL bulk collect 5

3. RIGHT OUTER JOIN

This is another type of join, in this type, it returns all rows from the right-hand side and only matching rows from the left-hand side.

Syntax:

select colm name 1, colm name2, ….colm name N from specified first table name(Table 1) RIGHT OUTER JOIN specified second table name(Table 2) ON specified first table name.colm name= specified second table name.colm name;

Example

select stud1.roll_no, stud1.name, dept.dept_name from stud1 right outer join dept on
stud1.roll_no=dept.roll_no;

Explantion

In the above example, we try to implement the right outer join as shown. In this example, we return all rows from the dept table and only matching rows from the stud1 table. The final output of the above statement we illustrated by using the following screenshot as follows.

image

4. FULL OUTER JOIN

This is another type of JOIN that is FULL OUTER JOIN; in this type, it returns all rows from the stud1 table that is the left-hand table, and the dept table that is the right-hand table.

Syntax:

select colm name 1, colm name2, ….colm name N from specified first table name(Table 1) FULL OUTER JOIN specified second table name(Table 2) ON specified first table name.colm name= specified second table name.colm name;

Example

select stud1.roll_no, stud1.name, dept.dept_name from stud1 full outer join dept on
stud1.roll_no=dept.roll_no;

Explanation

In the above example, we try to implement the full outer join. The final output of the above statement we illustrated by using the following screenshot as follows.

image 2

In this way, we can also implement cross join, anti join, and semi-join as per our requirements.

Conclusion

We hope from this article you learn PL/SQL JOIN. From the above article, we have learned the basic syntax of JOIN with different types and we also see different examples of JOINS. From this article, we learned how and when we use PL/SQL JOIN.

Recommended Articles

This is a guide to PL/SQL join. Here we discuss the Definition, syntax, Types of join in PL/SQL with Examples and code implementation. You may also have a look at the following articles to learn more –

  1. PL/SQL Cursor Loop
  2. PL SQL vs SQL
  3. PL/SQL TRIM
  4. PL/SQL to_DATE
Popular Course in this category
Oracle Training (17 Courses, 8+ Projects)
  17 Online Courses |  8 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

SQL Training Program (10 Courses, 8+ Projects)4.9
PL SQL Training (4 Courses, 2+ Projects)4.8
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

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

EDUCBA

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

Let’s Get Started

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
EDUCBA

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

Forgot Password?

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