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 JOIN

SELECT 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 JOIN

SELECT * 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 Join

SELECT distinct * from Emp LEFT OUTER JOIN Dept Where Emp.empid=Dept.empid

It includes non matching rows also unlike Inner Join.

Self JOIN

SELECT a.name,b.name from emp a, emp b WHERE a.id=b.rollNumber

Joining a Table to itself.

Posted in: 500 Oracle interview questions and answers