What are joins..how many types of joins are there?
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
How do I connect to oracle database?
What is RULE-based approach to optimization ?
Name the three major set of files on disk that compose a database in Oracle?
How to create a table interactively?
Can a primary key contain more than one columns?
can anyody please send me the dump for Oracle 10g certifications for DBA path?
What are the limitations oracle database 10g xe?
What is logical backup in oracle?
Can you assign multiple query result rows to a variable?
How does propagation differ between Advanced Replication and Snapshot Replication (read-only)?
A VIEWS takes memory in the database. If yes, how can u proove it? is there any way to display the size of views?
Differentiate between translate and replace?