What is a JOIN? Explain types of JOIN in oracle.
A JOIN is used to match/equate different fields from 2 or more tables using primary/foreign keys. Output is based on type of Join and what is to be queries i.e. common data between 2 tables, unique data, total data, or mutually exclusive data.
Types of JOINS:Simple JOINSELECT p.last_name, t.deptName FROM person p, dept t WHERE p.id = t.id;
Find name and department name of students who have been allotted a department
Inner/Equi/Natural JOINSELECT * from Emp INNER JOIN Dept WHERE Emp.empid=Dept.empid
Extracts data that meets the JOIN conditions only. A JOIN is by default INNER unless OUTER keyword is specified for an OUTER JOIN.
Outer JoinSELECT distinct * from Emp LEFT OUTER JOIN Dept Where Emp.empid=Dept.empid
It includes non matching rows also unlike Inner Join.
Self JOINSELECT a.name,b.name from emp a, emp b WHERE a.id=b.rollNumber
Joining a Table to itself.
What is a JOIN? Explain types of JOIN in oracle.
A join is a query that extracts data from two or more tables, views or snapshots.
Types of JOINEQUI-JOINThis is represented by (=) sign. This join retrieves information by using equality condition.
NON-EQUI JOINIf sign other than =, then it is non-equi join.
SELF JOINSelf join is a join type between a row of a table to another row of the same table.
OUTER JOINThis type fetches the row that matches the join condition and rows that don’t match the join condition.
