Results 1 to 12 of 12

Thread: How the joins work in SQL

  1. #1

    How the joins work in SQL

    I would like to know how the joins work in SQL and what is particularly self join where it is implemented and how and how to use which join in particular senarios


  2. #2
    Junior Member
    Join Date
    Jul 2007
    Answers
    1

    Re: How the joins work in SQL

    A join is used to combine rows from multiple tables. A join is performed whenever two or more tables is listed in the FROM clause of an SQL statement.

    There are different kinds of joins. Let's take a look at a few examples.



    Inner Join (simple join)
    Chances are, you've already written an SQL statement that uses an inner join. 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.



    Let's look at some data to explain how inner joins work:

    We have a table called suppliers with two fields (supplier_id and supplier_ name).
    It contains the following data:

    supplier_id supplier_name
    10000 IBM
    10001 Hewlett Packard
    10002 Microsoft
    10003 Nvidia


    We have another table called orders with three fields (order_id, supplier_id, and order_date).
    It contains the following data:

    order_id supplier_id order_date
    500125 10000 2003/05/12
    500126 10001 2003/05/13


    If we run the SQL statement below:

    SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
    FROM suppliers, orders
    WHERE suppliers.supplier_id = orders.supplier_id;



    Our result set would look like this:

    supplier_id name order_date
    10000 IBM 2003/05/12
    10001 Hewlett Packard 2003/05/13

    The rows for Microsoft and Nvidia from the supplier table would be omitted, since the supplier_id's 10002 and 10003 do not exist in both tables.



    Outer Join
    Another type of join is called an outer join. 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.

    The (+) after the orders.supplier_id field indicates that, if a supplier_id value in the suppliers table does not exist in the orders table, all fields in the orders table will display as in the result set.

    The above SQL statement could also be written as follows:

    select suppliers.supplier_id, suppliers.supplier_name, orders.order_date
    from suppliers, orders
    where orders.supplier_id(+) = suppliers.supplier_id



    Let's look at some data to explain how outer joins work:

    We have a table called suppliers with two fields (supplier_id and name).
    It contains the following data:

    supplier_id supplier_name
    10000 IBM
    10001 Hewlett Packard
    10002 Microsoft
    10003 Nvidia


    We have a second table called orders with three fields (order_id, supplier_id, and order_date).
    It contains the following data:

    order_id supplier_id order_date
    500125 10000 2003/05/12
    500126 10001 2003/05/13


    If we run the SQL statement below:

    select suppliers.supplier_id, suppliers.supplier_name, orders.order_date
    from suppliers, orders
    where suppliers.supplier_id = orders.supplier_id(+);



    Our result set would look like this:

    supplier_id supplier_name order_date
    10000 IBM 2003/05/12
    10001 Hewlett Packard 2003/05/13
    10002 Microsoft
    10003 Nvidia

    The rows for Microsoft and Nvidia would be included because an outer join was used. However, you will notice that the order_date field for those records contains a value.


  3. #3
    Junior Member
    Join Date
    Jun 2007
    Answers
    18

    Re: How the joins work in SQL

    SELF JOIN : joins are made on the same table this is in use when u need to create a report on basis of join cond of data in the same table


  4. #4
    Junior Member
    Join Date
    Nov 2005
    Answers
    1

    Post Re: How the joins work in SQL

    Quote Originally Posted by savitha_shankar View Post
    I would like to know how the joins work in SQL and what is particularly self join where it is implemented and how and how to use which join in particular senarios
    Self-join works like this....

    Suppose if u want to see the employees who draw more salary than that of their managers. And all the employees are stored in the same table with thier salaries and respective mgrs.

    u do the following:-

    SELECT w.ename,w.esal,m.ename,m.esal
    FROM emp w,emp m
    WHERE w.sal>m.sal
    AND w.mgr = m.empno;


    EMP
    -----------------------------------
    Empno Ename Esal Deptno Mgr
    ------------------------------------
    100 Anil 4000 10 102
    101 Ajay 3200 20 103
    102 John 2800 10 101
    103 Allen 5400 20 101
    104 Sanjay 5800 30 102

    Last edited by pramatha; 08-09-2007 at 07:36 PM. Reason: alignment

  5. #5
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: How the joins work in SQL

    self join is the join of the same table to itself.
    Performance wise Left join is always better than right join.


  6. #6
    Junior Member
    Join Date
    Jul 2007
    Answers
    7

    Re: How the joins work in SQL

    hi,
    u can interpret joins in this way.
    instead of getting info fr one table, if the situation occurs to get it from multiple rows v use joins.
    it actually takes info fr multiple tables with the association of som rules.

    self join is nothing but whn u want to retrieve diff set of data fr the same table like, who r all the managers for particular employees. the table cld be

    emp_no
    emp_name
    sal
    mgr
    .............


    so u sld retrieve emp_ name & their mgr name using the mgr no.


    the query cld be

    select e.emp_no, e.emp_name, m.emp_name "Manager"
    from emp e, emp m
    where e.mgr=m.emp_no;

    bye
    all the best
    viji


  7. #7

    Re: How the joins work in SQL

    Join is the method used in SQL to retrieve records from one or more different table having one column in common. There are three types of joins
    1. Self Join, 2. Equi Join and 3. Outer Join
    1. Self Join is the method used to retrieve records from the same table using join condition
    2. Equi join is used to retrieve records from two or more different tables. If N tables are joined then there should be 'N-1' conditions specified in the query. Otherwise, the query will result in retrieving cartesian product of records.
    3. Outer Join is used to retrieve records from two or more different tables by using '+' operator.

    Regards,
    Ganesan


  8. #8
    Junior Member
    Join Date
    Oct 2007
    Answers
    4

    Re: How the joins work in SQL

    Dear,

    I think some time non equi join condition is used to joint two table i.e. to get the Grades based on range of salary


  9. #9
    Junior Member
    Join Date
    Jun 2006
    Answers
    2

    Re: How the joins work in SQL

    Quote Originally Posted by savitha_shankar View Post
    I would like to know how the joins work in SQL and what is particularly self join where it is implemented and how and how to use which join in particular senarios
    A self-join is a query in which a table is joined (compared) to itself. Self-joins are used to compare values in a column with other values in the same column in the same table. One practical use for self-joins: obtaining running counts and running totals in an SQL query.



    To write the query, select from the same table listed twice with different aliases, set up the comparison, and eliminate cases where a particular value would be equal to itself.


  10. #10
    Junior Member
    Join Date
    Jul 2008
    Answers
    1

    Re: How the joins work in SQL

    please can anyone send me the some SQL related senario type questions and answres


  11. #11
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: How the joins work in SQL

    Quote Originally Posted by sharanappa View Post
    please can anyone send me the some SQL related senario type questions and answres
    whatr did a Google say ?

    Have you tried for that option ?


  12. #12
    Junior Member
    Join Date
    Jul 2007
    Answers
    7

    Re: How the joins work in SQL

    Quote Originally Posted by sharanappa View Post
    please can anyone send me the some SQL related senario type questions and answres
    we can get lot of real time examples & solutions from the below site

    http://asktom.oracle.com/pls/asktom/...:6407993912330

    regards
    viji


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact