GeekInterview.com
Series: Subject: Topic:
Question: 256 of 429

What is join? Types of Join & Difference between each.

Asked by: Interview Candidate | Asked on: Sep 5th, 2006
Showing Answers 1 - 5 of 5 Answers
Babu

Answered On : 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.

  
Login to rate this answer.
Silu

Answered On : 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.

  
Login to rate this answer.
Lalita.p

Answered On : 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.

  
Login to rate this answer.
BinduJoseph

Answered On : Jan 22nd, 2007

View all answers by BinduJoseph

First lets consider these two tables. AKey Data----------- ----------1 a 2 b  BKey Data----------- ----------1 c 3 d We can see that the only match is where Key is 1.INNER JOINIn 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 beaKey aData bKey bData----------- ---------- ----------- ----------1 a 1 c  In the case of the various outer joins non-matches will be returned also.LEFT OUTER JOINIn 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 querySELECT 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]returnsaKey aData bKey bData----------- ---------- ----------- ----------1 a 1 c 2 b NULL NULL RIGHT OUTER JOINThe 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 querySELECT 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]returnsaKey aData bKey bData----------- ---------- ----------- ----------1 a 1 c NULL NULL 3 d  FULL OUTER JOINA FULL OUTER JOIN returns a set containing all rows from either side, matched if possible, but nulls put in place if not.The querySELECT 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]returnsaKey aData bKey bData----------- ---------- ----------- ----------1 a 1 c 2 b NULL NULLNULL NULL 3 d  CROSS JOINThe 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 querySELECT A.[Key] AS aKey, A.Data AS aData, B.[Key] AS bKey, b.Data AS bDataFROM ACROSS JOIN BreturnsaKey aData bKey bData----------- ---------- ----------- ----------1 a 1 c 2 b 1 c 1 a 3 d 2 b 3 d

  
Login to rate this answer.
sindhu

Answered On : Aug 2nd, 2007

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

  
Login to rate this answer.

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

Related Open Questions

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

Interview Question

 Ask Interview Question?

 

Latest Questions

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.