What are joins..how many types of joins are there?

Answers were Sorted based on User's Feedback



What are joins..how many types of joins are there?..

Answer / suresh

Joins are used to fetch a query on more than one table.
You can broadly catagorize joins in to four types.
1. Equi Join
2.Non Equijoin
3.outer joins
4.self joins

Is This Answer Correct ?    140 Yes 34 No

What are joins..how many types of joins are there?..

Answer / tripti gour

In Oracle we can catogrise joins in following ways -
(1) Equi Join (Query having equal sign for condition)

select empno, ename, dname
from emp join dept
on emp.deptno = dept.deptno;

(2) Natural Join : both joining table must have the same
column name on which we are joining both tables.and no need
to specify the column name

select empno, ename, dname
from emp natural join dept;

(3) Non Equi Join: in both table column value is not
directly equal. so, for joining both table can't use equal
sign. in plce of it use BETWEEN,<=, >= operators.

select ename, sal
from emp join salgrade
on sal between losal and hisal;

(4)Outer Join: to fetch matched or unmatched data from
single or both tables.
- left outer join
- right outer join
- full outer join
(4) self Join or Theta Join : joining one table with itself
as another table.

select e1.empno, e1.ename employee, e2.empno mgr_number,
e2.ename manager
from emp e1 right outer join emp e2
on e1.mgr = e2.empno

(5) Cross Join : when we avoid to specify the joiing
condition means WHERE clause then it's become CROSS JOIN.

select ename, dname
from emp join dept;



always try to avoid CROSS JOIN.

Is This Answer Correct ?    75 Yes 5 No

What are joins..how many types of joins are there?..

Answer / akn

In SQL Server,Join actually puts data from two or more
tables into a sinle result set.
there are 3 types of joins
1.Inner join
2.Outer join
3.Cross join

Is This Answer Correct ?    73 Yes 40 No

What are joins..how many types of joins are there?..

Answer / shree

In SQL Server,Join actually puts data from two or more
tables into a sinle result set.
there are 3 types of joins
1.Inner join
2.Outer join
3.Cross join
In Oracle server
1. Equi Join
2.Non Equijoin
3.outer joins
4.self joins

Is This Answer Correct ?    47 Yes 16 No

What are joins..how many types of joins are there?..

Answer / suresh

Join are used to connect two or more tables. There are 3
types of joins. 1) Inner Join 2) Outer Join 3) Self Join

Is This Answer Correct ?    57 Yes 31 No

What are joins..how many types of joins are there?..

Answer / deepika s verma

In Oracle we can catogrise joins in following ways -
(1) Equi Join (Query having joining condition)
- Natural Join
- Join Using
- Join ON
(2) Non Equi Join or Cartesian Join ( without using joining
condition)
(3)Outer Join
- left outer join
- right outer join
- full outer join
(4) self Join or Theta Join
(5) Cross Join
(6) Inner Join

Is This Answer Correct ?    35 Yes 16 No

What are joins..how many types of joins are there?..

Answer / venu

types of joins:
• Equijoin
• Non-equijoin
• Outer join
• Self join

Equijoins:
-------------
To determine an employee’s department name, you compare the
value in the DEPARTMENT_ID
column in the EMPLOYEES table with the DEPARTMENT_ID values
in the DEPARTMENTS table.
The relationship between the EMPLOYEES and DEPARTMENTS
tables is an equijoin—that is, values
in the DEPARTMENT_ID column on both tables must be equal.
Frequently, this type of join involvesprimary and foreign
key complements.

SELECT employees.employee_id, employees.last_name,
employees.department_id, departments.department_id,
departments.location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

Non-Equijoins:
----------------
A non-equijoin is a join condition containing something
other than an equality operator.
The relationship between the EMPLOYEES table and the
JOB_GRADES table has an
example of a non-equijoin. A relationship between the two
tables is that the SALARY
column in the EMPLOYEES table must be between the values in
the LOWEST_SALARY
and HIGHEST_SALARY columns of the JOB_GRADES table. The
relationship is
obtained using an operator other than equals (=).

SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary
BETWEEN j.lowest_sal AND j.highest_sal;

Outer Joins:
---------------
The missing rows can be returned if an outer join operator
is used in the join condition. The operator
is a plus sign enclosed in parentheses (+), and it is
placed on the “side” of the join that is deficient in
information. This operator has the effect of creating one
or more null rows, to which one or more rows
from the nondeficient table can be joined.
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id ;

Self Joins:
-------------
Joining a Table to Itself
Sometimes you need to join a table to itself. To find the
name of each employee’s manager, you need
to join the EMPLOYEES table to itself, or perform a self
join.

SELECT worker.last_name || ’ works for ’
|| manager.last_name
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id ;

Is This Answer Correct ?    32 Yes 13 No

What are joins..how many types of joins are there?..

Answer / mitra

you all confused me................

Is This Answer Correct ?    18 Yes 1 No

What are joins..how many types of joins are there?..

Answer / neet verma

A JOIN is a means for combining fields from two tables by
using values common to each.
there are three types of joins:-
1)inner join
a)eqi join
b)natural join
c)cross join
2)outer join
a)Left outer join
b)Right outer joins
c)Full outer join
3) self join

Is This Answer Correct ?    13 Yes 2 No

What are joins..how many types of joins are there?..

Answer / arpit gautam

Joins condition could be either of the one.

1.SQL SERVER

2.ORACLE SERVER

1.SQL SERVER
SQL SERVER having five joins.

a)CROSS JOIN
b)NATURAL JOIN
c)USING CLAUSE
d)OUTER JOIN
e)ARBITRARY JOIN

2.ORACLE SERVER

a)EQUI JOIN
b)NON EQUI JOIN
c)OUTER JOIN
d)SELF JOIN

Is This Answer Correct ?    11 Yes 1 No

Post New Answer

More Oracle General Interview Questions

How do I connect to oracle database?

0 Answers  


What is RULE-based approach to optimization ?

1 Answers  


Name the three major set of files on disk that compose a database in Oracle?

0 Answers  


How to create a table interactively?

0 Answers  


Can a primary key contain more than one columns?

2 Answers  






can anyody please send me the dump for Oracle 10g certifications for DBA path?

0 Answers  


What are the limitations oracle database 10g xe?

0 Answers  


What is logical backup in oracle?

0 Answers  


Can you assign multiple query result rows to a variable?

0 Answers  


How does propagation differ between Advanced Replication and Snapshot Replication (read-only)?

0 Answers  


A VIEWS takes memory in the database. If yes, how can u proove it? is there any way to display the size of views?

1 Answers   IBM,


Differentiate between translate and replace?

0 Answers  


Categories
  • Oracle General Interview Questions Oracle General (1789)
  • Oracle DBA (Database Administration) Interview Questions Oracle DBA (Database Administration) (261)
  • Oracle Call Interface (OCI) Interview Questions Oracle Call Interface (OCI) (10)
  • Oracle Architecture Interview Questions Oracle Architecture (90)
  • Oracle Security Interview Questions Oracle Security (38)
  • Oracle Forms Reports Interview Questions Oracle Forms Reports (510)
  • Oracle Data Integrator (ODI) Interview Questions Oracle Data Integrator (ODI) (120)
  • Oracle ETL Interview Questions Oracle ETL (15)
  • Oracle RAC Interview Questions Oracle RAC (93)
  • Oracle D2K Interview Questions Oracle D2K (72)
  • Oracle AllOther Interview Questions Oracle AllOther (241)