Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

hi all

Former Member
0 Kudos

hi all

could anybody give me how and when to use self join and equi join???

syntax + when to use self and when to use equi join???

thanx

rocky

1 ACCEPTED SOLUTION

Former Member
0 Kudos

Hi Rocky,,,,,

You hav got the defination of both joins from above answer ...

Here i am sending you some sample code with syntax...

It may clear your doubts...

<i><b>SELF-JOIN</b></i>

Self-Join is a concept where we are join a table to itself. This is useful when we want some particular information on join a table to itself. For eg.

EMPNO ENAME MGR

1 AA 2

2 BB Null

3 CC 1

In the above example we have a empno column and mgr column . In general they are the empno values only. Now if you have a query to find who is the manager for each then if i have to retrieve the data then we have to join the table to itself.

Here is the example for self join,

The Employee table is re is a table with structure as,

EmpId int

EmpName varchar(20)

Address varchar(50)

ManagerId int

Here ManagerId references some record in the Employee table itself.

Write a query to fetch the Name of the manager (employee), who manages an employee whose employee id is given.

Query:

Select m.EmpId ManagerId, m.EmpName ManagerName

From Employee e inner join Employee m on e.ManagerId = m.EmpId

Where e.EmpId = 1111

<i><b>One More Example of SELF JOIN</b></i>

Joining the same tbale to itself

use Pubs SELECT

authors.au_fname,

authors.au_lname,

authors1.au_fname AS Expr2,

authors1.au_lname AS Expr3

FROM

authors

INNER JOIN

authors authors1

ON authors.city

= authors1.city

AND authors.au_id

< authors1.au_id

WHERE

authors.city = 'Berkeley'

Plz Reward points if answer is useful...

Regards,

Mandeep.

3 REPLIES 3

Former Member
0 Kudos

self join--A join in which a table is joined with itself.

equijoin--A join statement that uses an equivalency operation. The converse of this is the nonequijoin operation.

Equi joins - contains only = sign

Non-Equi Joins - other simbles

regards

anju

Former Member
0 Kudos

Self join:

A self-join is a query in which a table is joined (compared) to itself. Self-joins are used to compare values in a column with other values in the same column in the same table. One practical use for self-joins: obtaining running counts and running totals in an SQL query.

Eg: Select from the same table listed twice with different aliases, set up the comparison, and eliminate cases where a particular value would be equal to itself.

Equi join:

Equi join is the join which contains an equal operator in its join condition.

Former Member
0 Kudos

Hi Rocky,,,,,

You hav got the defination of both joins from above answer ...

Here i am sending you some sample code with syntax...

It may clear your doubts...

<i><b>SELF-JOIN</b></i>

Self-Join is a concept where we are join a table to itself. This is useful when we want some particular information on join a table to itself. For eg.

EMPNO ENAME MGR

1 AA 2

2 BB Null

3 CC 1

In the above example we have a empno column and mgr column . In general they are the empno values only. Now if you have a query to find who is the manager for each then if i have to retrieve the data then we have to join the table to itself.

Here is the example for self join,

The Employee table is re is a table with structure as,

EmpId int

EmpName varchar(20)

Address varchar(50)

ManagerId int

Here ManagerId references some record in the Employee table itself.

Write a query to fetch the Name of the manager (employee), who manages an employee whose employee id is given.

Query:

Select m.EmpId ManagerId, m.EmpName ManagerName

From Employee e inner join Employee m on e.ManagerId = m.EmpId

Where e.EmpId = 1111

<i><b>One More Example of SELF JOIN</b></i>

Joining the same tbale to itself

use Pubs SELECT

authors.au_fname,

authors.au_lname,

authors1.au_fname AS Expr2,

authors1.au_lname AS Expr3

FROM

authors

INNER JOIN

authors authors1

ON authors.city

= authors1.city

AND authors.au_id

< authors1.au_id

WHERE

authors.city = 'Berkeley'

Plz Reward points if answer is useful...

Regards,

Mandeep.