What is a JOIN? Explain types of JOIN in oracle.

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.

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 JOIN

EQUI-JOIN
This is represented by (=) sign. This join retrieves information by using equality condition.

NON-EQUI JOIN
If sign other than =, then it is non-equi join.

SELF JOIN
Self join is a join type between a row of a table to another row of the same table.

OUTER JOIN
This type fetches the row that matches the join condition and rows that don’t match the join condition.
Explain various types of joins.
Explain various types of joins - Types of joins are: - Equijoins , Non-equijoins....
What is oracle Join?
What is oracle Join? - A join is a query that combines rows of two or more tables or views.....
What are the guidelines for joins?
Guidelines for joins - Join condition should ideally be written in the FROM clause.
Post your comment