TYPES OF JOINS
Join is a query that is used to combine rows from two or more tables, views, or materialized views.
1.)INNER JOIN
Inner join refers to the foreign key of one table and the primary key for another and creates a join between them.
For detailed learning of inner join go through the blog: https://medium.com/p/6744ae4583d0/edit
2.)LEFT OUTER JOIN
In Left Outer Join, returns all the records of left table(table1) and the matched records from the right table (table2). The result is NULL from the right side if there is no match.
QUERY:
SELECT a.FULL_NAME, e.ROLL_ID, e.SUBJECT
FROM ABCD a, EFGH e
WHERE a.ROLL_ID = e.ROLL_ID(+);
3.)RIGHT OUTER JOIN
In Right Outer Join, returns all the records of right table(table2) and the matched records from the left table (table1). The result is NULL from the left side if there is no match.
QUERY:
SELECT a.FULL_NAME, e.ROLL_ID, e.SUBJECT
FROM ABCD a, EFGH e
WHERE a.ROLL_ID(+) = e.ROLL_ID ;
4.)FULL OUTER JOIN
The FULL OUTER JOIN returns all records when there is a match in left (table1) or right (table2) table records.
NOTE: We need to use Union in full outer join and here, we will union both the queries right join and left join
QUERY:
SELECT FULL_NAME, ROLL_ID, SUBJECT
FROM ABCD a, EFGH e
WHERE a.ROLL_ID (+) = e.ROLL_ID
UNION
SELECT FULL_NAME, ROLL_ID, SUBJECT
FROM ABCD a, EFGH e
WHERE a.ROLL_ID = e.ROLL_ID (+)
To know more about Union and Union All refer to this blog :
