Join us as we dive into the intricacies of SQL in Oracle databases! In this tutorial, we demonstrate how to perform a full outer join operation using two sample tables. You'll learn how to seamlessly merge data and categorize it by its source table, enabling comprehensive data analysis and reporting.
We start by creating Table1 and Table2 and then proceed to fill them with data. By executing a well-structured full outer join query, we produce a unified dataset that includes IDs and Names, along with a category column indicating the originating table for each record.
Whether you're preparing for a job interview, brushing up on your database management skills, or simply love to accumulate new knowledge, this video is tailored just for you! Don't forget to hit the like button, subscribe for more insightful content, and drop your questions or feedback in the comments section below.
CREATE TABLE Table1 (
ID NUMBER PRIMARY KEY,
NAME VARCHAR2(50)
);
CREATE TABLE Table2 (
ID NUMBER PRIMARY KEY,
NAME VARCHAR2(50)
);
INSERT INTO Table1 (ID, NAME) VALUES (1, 'ANU');
INSERT INTO Table1 (ID, NAME) VALUES (3, 'RADHA');
INSERT INTO Table1 (ID, NAME) VALUES (6, 'RAGHU');
INSERT INTO Table2 (ID, NAME) VALUES (2, 'RAMESH');
INSERT INTO Table2 (ID, NAME) VALUES (4, 'SIVA');
INSERT INTO Table2 (ID, NAME) VALUES (5, 'KAVI');
SELECT * FROM Table1;
SELECT * FROM Table2;
--UNION ALL
select * from (SELECT ID,NAME,'TABLE1' AS CATEGORY FROM Table1
union all
SELECT ID,NAME,'TABLE2' AS CATEGORY FROM Table2)a order by id;
--FULL OUTER JOIN
--COALESCE FUNCTION --IT IS GOING TO IDENTIFY THE FIRST NON NULL VALUES, IF BOTH ARE NULL IT WILL POPULATE NULL VALUES
SELECT
COALESCE(A.ID,B.ID) AS ID,
COALESCE(A.NAME,B.NAME) AS NAME,
CASE WHEN A.ID IS NOT NULL THEN 'TABLE1'
WHEN B.ID IS NOT NULL THEN 'TABLE2'
ELSE 'NA' END AS CATEGORY
FROM TABLE1 A
FULL OUTER JOIN
TABLE2 B ON (A.ID=B.ID)
ORDER BY ID;
--A.ID IS NOT NULL THEN POPULATE TABLE1
--B.ID IS NOT NULL THEN POPULATE TABLE2
Timestamps:
0:00 - Introduction to Full Outer Joins
1:15 - Creating Tables in Oracle
2:30 - Inserting Data into Tables
3:45 - Writing the Full Outer Join Query
5:00 - Analyzing the Query Results
6:30 - Tips for Efficient SQL Queries
#SQL #Oracle #FullOuterJoin #DatabaseTutorial #DataManagement #SQLJoins #DataAnalysis #OracleDatabase #LearnSQL #SQLTutorial"
Негізгі бет Mastering SQL: Full Outer Joins in Oracle Explained with Real Examples
Пікірлер: 3