Babu

• Sep 7th, 2006

 

Join Def : A join is performed whenever two or more tables is listed in the FROM clause of an SQL statement.There are different kinds of joins1) Inner join (Def) : 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.Outter Join (Def) : 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.

Silu

• Sep 8th, 2006

 

Join is used to retrieve data from more than one table simultaneously....

There are two types of join:
1. INNER JOIN
2. OUTER JOIN

1. INNER JOIN: Inner Join is used to retrieve only matching data from two or more table. It is represented by the symbol '=' in the where clause in the query.

2. OUTER JOIN: There are three different types of outer join: a. LEFT OUTER JOIN b. RIGHT OUTER JOIN c. FULL OUTER JOIN

a. LEFT OUTER JOIN: It retrieves all the records from left most table (irrespective of the condition specified) and retrieves only matching records from the right most table and assigns NULL for unmatched fields. Represented by the symbol '*='.

b. RIGHT OUTER JOIN: It retrieves all the records from right most table (irrespective of the condition specified) and retrieves only matching records from the left most table and assigns NULL for unmatched fields. (opposite to LEFT OUTER JOIN). Represented by the symbol '=*'.

c. FULL OUTER JOIN: It retrieves records from both the table (irrespective of the condition) and for unmatched fields it assigns NULL.

Lalita.p

• Sep 17th, 2006

 

INNER

Specifies all matching pairs of rows are returned. Discards unmatched rows from both tables. This is the default if no join type is specified.

LEFT [OUTER]

Specifies that all rows from the left table not meeting the specified condition are included in the result set, and output columns from the right table are set to NULL in addition to all rows returned by the inner join.

RIGHT [OUTER]

Specifies that all rows from the right table not meeting the specified condition are included in the result set, and output columns from the left table are set to NULL in addition to all rows returned by the inner join.

FULL [OUTER]

If a row from either the left or right table does not match the selection criteria, specifies the row be included in the result set, and output columns that correspond to the other table be set to NULL. This is in addition to all rows usually returned by the inner join.

BinduJoseph

 Profile Answers by BinduJoseph 

• Jan 22nd, 2007

 

First lets consider these two tables.

A

Key         Data----------- ----------1           a         2           b    

 

B

Key         Data----------- ----------1           c         3           d         

We can see that the only match is where Key is 1.

INNER JOIN

In an INNER JOIN that will be the only thing returned. If we use the query

SELECT A.[Key] AS aKey, A.Data AS aData, B.[Key] AS bKey,

 b.Data AS bDataFROM A 

INNER JOIN B ON a.[Key] = b.[Key]

the returned set will be

aKey        aData      bKey        bData----------- ---------- 

----------- ----------1           a          1           c         

 

In the case of the various outer joins non-matches will be returned also.

LEFT OUTER JOIN

In a LEFT OUTER JOIN everything on the left side will be returned. Any matches on the right side will be returned also, but if there is no match on the right side then nulls are returned instead.

The query

SELECT A.[Key] AS aKey, A.Data AS aData, B.[Key] AS bKey, b.Data AS bDataFROM ALEFT 

OUTER JOIN B ON a.[Key] = b.[Key]

returns

aKey        aData      bKey        bData----------- ---------- 

----------- ----------1           a          1           c         2           b         

 NULL        NULL

 

RIGHT OUTER JOIN

The RIGHT OUTER JOIN is very similar to the LEFT OUTER JOIN, except that, of course, the matching is reversed. Everything on the right side is returned, and only matches on the left side are returned. Any non-matches will be filled with nulls on the left side.

The query

SELECT A.[Key] AS aKey, A.Data AS aData, B.[Key] AS bKey, b.Data AS bDataFROM ARIGHT 

OUTER JOIN B ON a.[Key] = b.[Key]

returns

aKey        aData      bKey        bData----------- ---------- 

----------- ----------1           a          1           c         NULL        NULL     

  3           d         

 

FULL OUTER JOIN

A FULL OUTER JOIN returns a set containing all rows from either side, matched if possible, but nulls put in place if not.

The query

SELECT A.[Key] AS aKey, A.Data AS aData, B.[Key] AS bKey, b.Data AS bDataFROM AFULL OUTER 

JOIN B ON a.[Key] = b.[Key]

returns

aKey        aData      bKey        bData----------- ---------- 

----------- ----------1           a          1           c         2           b          NULL        NULLNULL        NULL       3           d         

 

CROSS JOIN

The CROSS JOIN doesn't obey the same set of rules as the other joins. This is because it doesn't care about matching rows from either side, so there is no ON qualifier within the join clause. This is a simple join that joins all rows on the left side to all rows on the right side. Where the other joins cannot return more rows than exist in the most populous of the source tables, the CROSS JOIN will return the product of rows from each side. If you have 5 rows in Table A, and 6 rows in Table B it will return a set containing 30 rows.

The query

SELECT A.[Key] AS aKey, A.Data AS aData, B.[Key] AS bKey,

 b.Data AS bDataFROM ACROSS JOIN B

returns

aKey        aData      bKey        bData----------- ---------- 

----------- ----------1           a          1           c         2           b          

1           c         1           a          3           d         2           b          3           d     

sindhu

• Aug 2nd, 2007

 

Thanks for your answer.It helped me alot for my interview.