locked
What are diff kind of join in SQL? RRS feed

  • 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 link

     

    Tuesday, 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 (
    (
    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