Answered by:
What are diff kind of join in SQL?

Question
-
I have an assgmt due tommorow, what are the diff kinds of joinz?
Pease help quick!
Tuesday, September 11, 2007 6:14 AM
Answers
-
There are different kinds of joins in SQL
Natural Join: Here you join the table using a single common entity.
Outer Join: In this we join two tables according to the given condition.
It can be of 3 types: Left Outer, Right Outer, And Full Join
1) Left Outer: Here all the records from the left table are shown and only the matching right table records are shown. Rest all are NULL.
2) Right Outer: Here all the records from the right table are shown and only the matching right table records are shown. Rest all are NULL.
3) Full Join: Here all records are shown. But whatever dont match are NULL.
You can find SQL commands for these Joins:
http://www.techonthenet.com/sql/joins.php
http://www.techonthenet.com/sql/joins.php Tuesday, September 11, 2007 6:20 AM
All replies
-
There are different kinds of joins in SQL
Natural Join: Here you join the table using a single common entity.
Outer Join: In this we join two tables according to the given condition.
It can be of 3 types: Left Outer, Right Outer, And Full Join
1) Left Outer: Here all the records from the left table are shown and only the matching right table records are shown. Rest all are NULL.
2) Right Outer: Here all the records from the right table are shown and only the matching right table records are shown. Rest all are NULL.
3) Full Join: Here all records are shown. But whatever dont match are NULL.
You can find SQL commands for these Joins:
http://www.techonthenet.com/sql/joins.php
http://www.techonthenet.com/sql/joins.php Tuesday, September 11, 2007 6:20 AM -
Excellent answer, thanks for the linkTuesday, September 11, 2007 6:22 AM
-
http://en.wikipedia.org/wiki/Join_(SQL)
Hope this link provides u wid some more additional inf. including example tables for better understanding...Tuesday, September 11, 2007 1:56 PM -
There are four kinds of joins: cross, inner, outer, and self joins. The join in our example is an inner join. Changing the syntax to make this more apparent, the join can also look like this:
SELECT title, year, actor FROM Movie INNER JOIN Actor ON
(Movie.MovieID=Actor.MovieID) AND MovieID='22'
The inner join returns only the records that match the specific criteria you ask for (Movie.MovieID=Actor.MovieID) and nothing else. This result is different from that of an outer join, which can return these records as well as unmatched rows from one or both of the tables you are pulling from.
A self join joins data from different fields within the same table. Self joins are rarely required.
A cross join is a specialized inner join. It does the same thing as the inner join, but it does not have a WHERE clause, making it the Cartesian product of the tables you are comparing.Tuesday, September 11, 2007 6:02 PM -
Basically Joins are used to get result from two or more tables and there are two types of joins
inner join and outer join
Inner join : a join of two or more tables which omits the blank rows while checking
Outer join is subcatogorised in to left outer join and right outer join. Which includes blank rows in specifed side if condition satisfies.
Simple outer join is combination of left and right outerjoins.
Apart from these there are
Natural join : cartisian product
Equi join : which includes = operator in condition
NonEqui join : All conditional joins which doesn't uses = in there conditions.
Again format of Joins depends upon what SQL Database u r using .
Like MySQL, Oracle, sqlite, MS-SQL, Sybase, DB2 etc.
Joins are perferd over subquiries from different tables, as they are faster in comparison.
Also, In MySQL Explain statement is very helpful to find the complexity & Keys used in the joins, so that we can furthere optimize our query.
a typical example of join in Subqueries :
SELECT DISTINCT
IF (() AS Name, compMasterEmail AS MasterEmailcompContactName != ""), compContactName, clientUsername
FROM niclient.CLIENT_REG, niclient.COMPANY_DETAILS
WHERE COMPANY_DETAILS.compId = CLIENT_REG.clientCompanyId
AND CLIENT_REG.clientId NOT
IN (SELECT DISTINCT CLIENT_REG.clientId)
FROM niclient.SUBSCRIPTION, niclient.ORDER_DETAILS, niclient.TRANSACTION, niclient.PAYMENT, niclient.CLIENT_REG
WHERE subOrdrDetId = ordrDetId
AND ordrDetTransId = transId
AND ordrDetCanceled != 'y'
AND subCancelFlag != 'y'
AND transId = paymentTransId
AND clientCompanyId = transCompId
AND subCatId =1
AND subSubcatId =2
AND paymentAmount >1
And after explaining the query in MySQL we get :
id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY CLIENT_REG ALL clientCompanyId NULL NULL NULL 11948 Using where; Using temporary 1 PRIMARY COMPANY_DETAILS eq_ref PRIMARY PRIMARY 4 niclient.CLIENT_REG.clientCompanyId 1 2 DEPENDENT SUBQUERY CLIENT_REG eq_ref PRIMARY,clientCompanyId PRIMARY 4 func 1 Using where; Using temporary 2 DEPENDENT SUBQUERY TRANSACTION ref transaction_id,company_id,transaction_id_2 company_id 4 niclient.CLIENT_REG.clientCompanyId 1 2 DEPENDENT SUBQUERY PAYMENT ref transaction_id transaction_id 102 niclient.TRANSACTION.transId 1 Using where 2 DEPENDENT SUBQUERY ORDER_DETAILS ref PRIMARY,ordrDetTransId,ordrDetTransId_2 ordrDetTransId 22 niclient.PAYMENT.paymentTransId 1 Using where 2 DEPENDENT SUBQUERY SUBSCRIPTION ref cat_id,subcat_id,order_id order_id 4 niclient.ORDER_DETAILS.ordrDetId 1 Using where Wednesday, September 12, 2007 8:27 AM