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 ?    74 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 15 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

Table E: Name dept month sal 1 A JAN 800 2 B APR 1000 3 A JAN 300 4 A JAN 600 5 C JUN 400 1) SELECT HIGHEST SAL PAID DEPT IN JAN MONTH? 2) WRITE QUERY GET MAX SAL DEPT NO?

6 Answers   IBM,


what are Triggers?

5 Answers   TCS,


how to clone 9i Database on to 10g Database.

0 Answers  


i want department wise maxmum salary and empolyee name

9 Answers  


How do you rate yourself in oracle and sql server ?

2 Answers   Keane India Ltd,






without using count(*) and rownum how can we count total record in a table

5 Answers   HP, AC,


how to retrieve daily sal from emp table in oracle 10g

2 Answers   Infosys,


what are different types of locks?

6 Answers  


What is a Sequence ?

3 Answers  


Shall I get Pro*C Compiler in Oracle-10g release ?

1 Answers  


What are the components of Physical database structure of Oracle Database?

4 Answers  


create or replace procedure show_tab_rec ( P_tab VARCHAR2 ) IS cmd varchar2(50); begin cmd := 'select * from '|| P_tab; for int in ( execute immediate cmd ) loop dbms_output.put_line ( int.ename||' '||int.deptno); end loop; end; when i m compling this procedure i m getting this error PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one of the following: . ( ) , * @ % & | = - + < / > at in is mod remainder not range rem => .. <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKEC_ between || multiset member SUBMULTISET_ PLZ solve this error give this question answer asap Thanks advance.......

1 Answers   HCL,






Categories