Re: What is Equijoin and Self-Join Query?

A selfjoin is when you join a table to itself.

Example:

TABLE PERSONS
    person_identifier varchar2(20),
    name varchar2(50),
    marriedto varchar2(20),
    Country_name varchar2(50)

select p1.name, p2.name
from persons p1, persons p2
where p2.person_identifier = p1.marriedto; <- This is a selfjoin

The above query selects each name from table persons (p1) along with the name of the person (p2) whom the first person is married to.

An equijoin is a join by equality (=) of two tables that have a column in common.

Example:

TABLE COUNTRIES
    Country_name varchar2(50),
    Population number

select name, population
from persons, countries
where countries.country_name = persons.country_name; <- This is an equijoin

The above query selects every name from the persons table along with the population in the country he or she lives in. If you construct a join with
>, <, !=, in, like, between, or whatever, the join is no longer an equijoin.
It's a non-equijoin. Some places, you might see people stating that an inner join is the same as an equijoin. This is not correct. An equijoin is an inner join using the equality operator between two related columns from two different tables.

To add to the confusion, there's a join called an outer join. Consider the second example again:

select name, population
from persons, countries
where countries.country_name = persons.country_name;

This query does not select the persons if the country they live in do not exist in the country table. This could happen if persons.country_name is NULL. In this situation, an outer join will remedy the situation

select name, population
from persons, countries
where countries.country_name(+) = persons.country_name; <- This is an outer join

Here, all persons would be selected even if the country they live in is unknown. A result might look like:

Bob        10000000
Mary        5000000
Bill                                    <- Bill is included because of the
outer join.
Jill            22000000

Hope this clarifies the termonoligy for you. - Ivan Bajon

suisum_at_freenet.edmonton.ab.ca wrote in message <6tsjh0$gcb$1_at_news.sas.ab.ca>...
>Hi:
>
>I saw the terms Equijoin, Selfjoin. Can anyone explain these terms and
>provide soem sample SELECT statements, please?
>
>--
>Best regards,
Received on Fri Sep 18 1998 - 05:06:33 CDT