What is a JOIN? Explain types of JOIN in oracle

          

What is a JOIN? Types of JOIN in oracle

<<Previous  Next>>

Oracle - What is a JOIN? Explain types of JOIN in oracle - Feb 07, 2010 at 14:20 PM by Shuchi Gauri

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.

Oracle - What is a JOIN? Explain types of JOIN in oracle - April 10, 2009 at 11:00 AM

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.

Also read

Oracle Joins

What is oracle Join?, What are the guidelines for joins?, What are the types of Join?...........

Explain the types of joins.

Types of joins are: Equijoins, Non-equijoins, self join, outer join..........

What is object data type in oracle?

Object data type is created using object type in oracle. The object data type can only be created using SQL data type such as Number, Varchar 2, Date etc............

What is composite data type?
Differences between CHAR and NCHAR in Oracle.

CHAR and NCHAR are character data types which and have a fixed length........

<<Previous  Next>>

Write your comment - Share Knowledge and Experience


 

 
Interview questions
 
Home | Want a Job? Submit Key Skills | Employer login | My key skills | About us | Sitemap | Contact us