How the joins work in SQL

I would like to know how the joins work in SQL and what is particularly self join where it is implemented and how and how to use which join in particular senarios

A join is used to combine rows from multiple tables. A join is performed whenever two or more tables is listed in the FROM clause of an SQL statement.

There are different kinds of joins. Let's take a look at a few examples.



Inner Join (simple join)
Chances are, you've already written an SQL statement that uses an inner join. It is is the most common type of join. Inner joins return all rows from multiple tables where the join condition is met.

For example,

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id;

This SQL statement would return all rows from the suppliers and orders tables where there is a matching supplier_id value in both the suppliers and orders tables.



Let's look at some data to explain how inner joins work:

We have a table called suppliers with two fields (supplier_id and supplier_ name).
It contains the following data:

supplier_id supplier_name
10000 IBM
10001 Hewlett Packard
10002 Microsoft
10003 Nvidia


We have another table called orders with three fields (order_id, supplier_id, and order_date).
It contains the following data:

order_id supplier_id order_date
500125 10000 2003/05/12
500126 10001 2003/05/13


If we run the SQL statement below:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id;



Our result set would look like this:

supplier_id name order_date
10000 IBM 2003/05/12
10001 Hewlett Packard 2003/05/13

The rows for Microsoft and Nvidia from the supplier table would be omitted, since the supplier_id's 10002 and 10003 do not exist in both tables.



Outer Join
Another type of join is called an outer join. This type of join returns all rows from one table and only those rows from a secondary table where the joined fields are equal (join condition is met).

For example,

select suppliers.supplier_id, suppliers.supplier_name, orders.order_date
from suppliers, orders
where suppliers.supplier_id = orders.supplier_id(+);

This SQL statement would return all rows from the suppliers table and only those rows from the orders table where the joined fields are equal.

The (+) after the orders.supplier_id field indicates that, if a supplier_id value in the suppliers table does not exist in the orders table, all fields in the orders table will display as <null> in the result set.

The above SQL statement could also be written as follows:

select suppliers.supplier_id, suppliers.supplier_name, orders.order_date
from suppliers, orders
where orders.supplier_id(+) = suppliers.supplier_id



Let's look at some data to explain how outer joins work:

We have a table called suppliers with two fields (supplier_id and name).
It contains the following data:

supplier_id supplier_name
10000 IBM
10001 Hewlett Packard
10002 Microsoft
10003 Nvidia


We have a second table called orders with three fields (order_id, supplier_id, and order_date).
It contains the following data:

order_id supplier_id order_date
500125 10000 2003/05/12
500126 10001 2003/05/13


If we run the SQL statement below:

select suppliers.supplier_id, suppliers.supplier_name, orders.order_date
from suppliers, orders
where suppliers.supplier_id = orders.supplier_id(+);



Our result set would look like this:

supplier_id supplier_name order_date
10000 IBM 2003/05/12
10001 Hewlett Packard 2003/05/13
10002 Microsoft <null>
10003 Nvidia <null>

The rows for Microsoft and Nvidia would be included because an outer join was used. However, you will notice that the order_date field for those records contains a <null> value.

SELF JOIN : joins are made on the same table this is in use when u need to create a report on basis of join cond of data in the same table

[QUOTE=savitha_shankar;15500]I would like to know how the joins work in SQL and what is particularly self join where it is implemented and how and how to use which join in particular senarios[/QUOTE]

Self-join works like this....

Suppose if u want to see the employees who draw more salary than that of their managers. And all the employees are stored in the same table with thier salaries and respective mgrs.

u do the following:-

SELECT w.ename,w.esal,m.ename,m.esal
FROM emp w,emp m
WHERE w.sal>m.sal
AND w.mgr = m.empno;


EMP
-----------------------------------
Empno Ename Esal Deptno Mgr
------------------------------------
100 Anil 4000 10 102
101 Ajay 3200 20 103
102 John 2800 10 101
103 Allen 5400 20 101
104 Sanjay 5800 30 102

self join is the join of the same table to itself.
Performance wise Left join is always better than right join.

hi,
u can interpret joins in this way.
instead of getting info fr one table, if the situation occurs to get it from multiple rows v use joins.
it actually takes info fr multiple tables with the association of som rules.

self join is nothing but whn u want to retrieve diff set of data fr the same table like, who r all the managers for particular employees. the table cld be

emp_no
emp_name
sal
mgr
.............


so u sld retrieve emp_ name & their mgr name using the mgr no.


the query cld be

select e.emp_no, e.emp_name, m.emp_name "Manager"
from emp e, emp m
where e.mgr=m.emp_no;

bye
all the best
viji

Join is the method used in SQL to retrieve records from one or more different table having one column in common. There are three types of joins
1. Self Join, 2. Equi Join and 3. Outer Join
1. Self Join is the method used to retrieve records from the same table using join condition
2. Equi join is used to retrieve records from two or more different tables. If N tables are joined then there should be 'N-1' conditions specified in the query. Otherwise, the query will result in retrieving cartesian product of records.
3. Outer Join is used to retrieve records from two or more different tables by using '+' operator.

Regards,
Ganesan

Dear,

I think some time non equi join condition is used to joint two table i.e. to get the Grades based on range of salary

[QUOTE=savitha_shankar;15500]I would like to know how the joins work in SQL and what is particularly self join where it is implemented and how and how to use which join in particular senarios[/QUOTE]

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.



To write the query, 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.

please can anyone send me the some SQL related senario type questions and answres

[QUOTE=sharanappa;32572]please can anyone send me the some SQL related senario type questions and answres[/QUOTE]

whatr did a Google say ?

Have you tried for that option ?

[QUOTE=sharanappa;32572]please can anyone send me the some SQL related senario type questions and answres[/QUOTE]

we can get lot of real time examples & solutions from the below site

[url]http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6407993912330[/url]

regards
viji