What are diff kind of join in SQL?

All replies

• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  Excellent answer, thanks for the link

   

  Tuesday, September 11, 2007 6:22 AM
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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 (

  (

  compContactName != ""

  ), compContactName, clientUsername

  ) AS Name, compMasterEmail AS MasterEmail
  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