A FULL JOIN, also called a FULL OUTER JOIN, is a type of join in PL/SQL that returns all rows from both tables, even if there is no matching data in the other table.
If a row from one table doesn’t have a match in the other, it will still be included in the result, with NULL values filling in the missing data. In this article, we will explain the concept of PL/SQL Full Join with its syntax, examples, and output in detail.
PL/SQL FULL JOIN
A FULL JOIN effectively combines the results of both LEFT JOIN and RIGHT JOIN operations. It ensures that all rows from both tables are included in the result set:
- Rows with matching data in both tables are shown together.
- Rows without matching data in the other table display NULL values in the corresponding columns.
Syntax:
SELECT column1, column2, column3
FROM table1
FULL JOIN table2
ON table1.column = table2.column;
Key Terms:
- table1 and table2 are the tables being joined.
- column1, column2, and column3 are the columns to be selected from the joined tables.
- The ON clause specifies the condition on which the tables are joined.
Examples of PL/SQL Full Join
The Students table holds information about students, including their unique identifier, name, and the course they are enrolled in. This table is designed to manage student records and their associated course IDs.
The StudentID column is the primary key, ensuring that each student record is unique. The CourseID column is a foreign key that links to the Courses table, indicating which course the student is enrolled in.
1. Students Table
The CREATE TABLE statement creates the Students table with three columns: StudentID (the primary key), StudentName, and CourseID. The INSERT statements add four students to the table, with one student having no course assigned (represented by NULL in the CourseID column).
Query:
CREATE TABLE Students (
StudentID NUMBER PRIMARY KEY,
StudentName VARCHAR2(50),
CourseID NUMBER
);
INSERT INTO Students (StudentID, StudentName, CourseID) VALUES (1, 'Alice', 101);
INSERT INTO Students (StudentID, StudentName, CourseID) VALUES (2, 'Bob', 102);
INSERT INTO Students (StudentID, StudentName, CourseID) VALUES (3, 'Charlie', 103);
INSERT INTO Students (StudentID, StudentName, CourseID) VALUES (4, 'David', NULL);
Output:
student_ID | student_name | CourseID |
|---|
1 | Alice | 101 |
2 | Bob | 102 |
3 | Charlie | 103 |
4 | David | NULL |
Explanation:
The output shows the data stored in the Students table, listing the student IDs, names, and their respective course IDs. David's CourseID is NULL, indicating that he is not enrolled in any course. The rest of the students are assigned to specific courses.
2. Courses Table
The Courses table stores information about various courses, including their unique identifier and name. This table helps manage course records and allows linking student enrollments to specific courses.
The CourseID column serves as the primary key, ensuring unique identification of each course.
Query:
CREATE TABLE Courses (
CourseID NUMBER PRIMARY KEY,
CourseName VARCHAR2(50)
);
INSERT INTO Courses (CourseID, CourseName) VALUES (101, 'Math');
INSERT INTO Courses (CourseID, CourseName) VALUES (102, 'Science');
INSERT INTO Courses (CourseID, CourseName) VALUES (104, 'History');
Output:
CourseID | CourseName |
|---|
101 | Math |
102 | Science |
104 | History |
Explanation:
The table displays the CourseID and CourseName for each course, showing the courses available along with their unique identifiers.
- The
INSERT INTO Courses statements add four courses to the Courses table.
- Each
CourseID is associated with a CourseName, such as 101 for 'Math', 102 for 'Science', 103 for 'English', and 104 for 'History'.
Example 1: List All Students with Their Courses
This query retrieves a complete list of students and the courses they are enrolled in. By using a FULL JOIN, the result includes all students and all courses.
If a student is not enrolled in any course, or if a course has no students, those records are still included with NULL values for the missing data. This approach provides a comprehensive view of both tables, reflecting any gaps in the data.
Query:
SELECT Students.StudentName, Courses.CourseName
FROM Students
FULL JOIN Courses
ON Students.CourseID = Courses.CourseID;
Output:
StudentName | CourseName |
|---|
Alice | Math |
Bob | Science |
Charlie | NULL |
David | NULL |
NULL | History |
Explanation :
The output shows all students alongside their corresponding courses. For students not enrolled in a course, CourseName is NULL. Similarly, courses with no students enrolled have a NULL StudentName..
Example 2: Find Courses Without Students
This query identifies courses that have no students enrolled. By performing a FULL JOIN and then filtering where StudentID is NULL, it highlights courses for which there are no student records. This is useful for pinpointing courses that are offered but not currently utilized by any students.
Query:
SELECT Courses.CourseName
FROM Students
FULL JOIN Courses
ON Students.CourseID = Courses.CourseID
WHERE Students.StudentID IS NULL;
Output:
Explanation:
The output lists the course name "History," indicating that no students are enrolled in this course. This is determined by the FULL JOIN, which shows rows from both tables even if there are no matches, and the WHERE clause filters for courses with a NULL StudentID, meaning no students are associated with this course.
Example 3: Find Students Not Enrolled in Any Course
This query retrieves the names of students who are not enrolled in any course. By using FULL JOIN and filtering for NULL CourseID, it selects students who do not have a corresponding course entry.
This helps identify students without current course enrollments.
Query:
SELECT Students.StudentName
FROM Students
FULL JOIN Courses
ON Students.CourseID = Courses.CourseID
WHERE Courses.CourseID IS NULL;
Output:
Explanation :
The result shows that students "Charlie" and "David" are not enrolled in any course. This is identified through the FULL JOIN and the WHERE clause filtering for NULL in the CourseID column, indicating that these students have no matching course records.
Example 4: Combine All Students and Courses with Descriptions
This query combines all students not enrolled in any courses and courses without students into a single result set. By using FULL JOIN and adding descriptive labels, it provides a clear distinction between students and courses that are not matched. The UNION ALL operator ensures that all relevant records are included.
Query:
SELECT Students.StudentName, 'Student' AS Type
FROM Students
FULL JOIN Courses
ON Students.CourseID = Courses.CourseID
WHERE Courses.CourseID IS NULL
UNION ALL
SELECT Courses.CourseName, 'Course' AS Type
FROM Students
FULL JOIN Courses
ON Students.CourseID = Courses.CourseID
WHERE Students.StudentID IS NULL;
Output
Name | Type |
|---|
Charlie | Student |
David | Student |
History | Course |
Explanation:
The output provides a list of students who are not currently enrolled in any course and courses that have no students, with each row labeled as either "Student" or "Course" for clarity.
Conclusion
The FULL JOIN in PL/SQL is a versatile operation for combining data from two tables while ensuring that all rows are included in the result set. It handles cases where there might be unmatched rows in either of the tables, filling in NULL values where necessary. This approach is valuable for obtaining a complete view of data relationships and identifying discrepancies or gaps in datasets.
Similar Reads
SQL FULL JOIN
In SQL, the FULL JOIN (or FULL OUTER JOIN) is a powerful technique used to combine records from two or more tables. Unlike an INNER JOIN, which only returns rows where there are matches in both tables, a FULL JOIN retrieves all rows from both tables, filling in NULL values where matches do not exist
4 min read
PL/SQL JOIN
JOIN is a powerful operation in PL/SQL that allows us to combine data from two or more related tables based on a common key. The PL/SQL JOIN is used to select data from multiple tables using this key to match records. This powerful PL/SQL feature allows for selecting data across multiple tables usin
5 min read
PL/SQL DELETE JOIN
In database management, removing specific records from a table is a common task especially when cleaning or updating data. One useful technique is the DELETE JOIN which allows us to delete records from one table based on conditions involving another table. In this article, we will explore PL/SQL DEL
4 min read
PL/SQL Cross Join
Combining data from several tables is an ordinary procedure in the field of relational databases. The Cross Join is one of the several table joining techniques offered by PL/SQL, Oracle's procedural SQL extension. To improve your comprehension, this article will provide you with an introduction to C
7 min read
PL/SQL Left Join
In the world of database management, efficiently retrieving and combining data from multiple tables is crucial. Among these the LEFT JOIN is particularly important because it includes all records from one table even when there are no corresponding records in another table. In this article, we will P
6 min read
SQL LEFT JOIN
In SQL, LEFT JOIN retrieves all records from the left table and only the matching records from the right table. When there is no matching record found, NULL values are returned for columns from the right table. This makes LEFT JOIN extremely useful for queries where you need to retain all records fr
5 min read
SQL Self Join
A Self Join in SQL is a powerful technique that allows one to join a table with itself. This operation is helpful when you need to compare rows within the same table based on specific conditions. A Self Join is often used in scenarios where there is hierarchical or relational data within the same ta
4 min read
FULL OUTER JOIN in SQLite
In the area of data querying and manipulation, the ability to combine information from different sources is important. SQLite, a popular embedded database engine, offers a range of join operations to fast process. One such operation FULL OUTER JOIN is particularly powerful as it allows us to merge d
4 min read
PL/SQL Right Join
In the area of database management, efficiently retrieving and combining data from multiple tables is essential. Among these techniques, the RIGHT JOIN is particularly useful because it includes all records from one table, even when there are no corresponding records in another table. PL/SQL Right J
4 min read
SQL Server FULL OUTER JOIN
Joins in SQL are used to retrieve data from multiple tables based on a related column (or common column) between them. In this article, we will learn how to use FULL OUTER JOIN, which returns all rows from both tables being joined. It combines the results of both LEFT OUTER JOIN and RIGHT OUTER JOIN
6 min read