• Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
  • Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

Noddy ANSI SQL question syntax

Collapse
X
  •  
  • Filter
  • Time
  • Show
Clear All
new posts

    Noddy ANSI SQL question syntax

    Hi,

    For hundreds of years I have been using (+) or * for my outer joins, now I have to use the ANSI stuff LEFT OUTER, RIGHT OUTER etc, and I'm struggling with it (I feel ashamed). I've looked online at guides etc and wrote SQL to test it but I am still not 100% sure my understanding is correct.

    In a nutshell what I am not clear on is what part of the JOIN clause does it consider when its working out the left and right tables for example see the basic example below.

    SELECT
    dim_client.NAME,
    fact_sales.Revenue
    FROM
    fact_sales RIGHT OUTER JOIN dim_client
    ON (dim_clients.client_id=fact_sales.client_id)


    When its working out what the RIGHT side table is does it look at this part of the SQL,

    FROM
    fact_sales RIGHT OUTER JOIN dim_client


    or this part ?

    On(
    dim_clients.client_id=fact_sales.client_id)

    Playing around with it if I move the tables round in the first part I get different results however if I change the order in the part after ON I get no change, so I'm assuming it considers the first clause after the FROM to work out the right and left is this correct ?

    FROM
    fact_sales RIGHT OUTER JOIN dim_client
    Last edited by ELBBUBKUNPS; 13 November 2012, 16:54.
    I like big butts and I cannot lie.

    #2
    Originally posted by ELBBUBKUNPS View Post
    Hi,

    For hundreds of years I have been using (+) or * for my outer joins, now I have to use the ANSI stuff LEFT OUTER, RIGHT OUTER etc, and I'm struggling with it (I feel ashamed). I've looked online at guides etc and wrote SQL to test it but I am still not 100% sure my understanding is correct.

    In a nutshell what I am not clear on is what part of the JOIN clause does it consider when its working out the left and right tables for example see the basic example below.

    SELECT
    dim_client.NAME,
    fact_sales.Revenue
    FROM
    fact_sales RIGHT OUTER JOIN dim_client
    ON (dim_clients.client_id=fact_sales.client_id)


    When its working out what the RIGHT side table is does it look at this part of the SQL,

    FROM
    fact_sales RIGHT OUTER JOIN dim_client


    or this part ?

    On(
    dim_clients.client_id=fact_sales.client_id)

    Playing around with it if I move the tables round in the first part I get different results however if I change the order in the part after ON I get no change, so I'm assuming it considers the first clause after the FROM to work out the right and left is this correct ?

    FROM
    fact_sales RIGHT OUTER JOIN dim_client
    Correct and by playing around you have figured it out. Are you just making sure what you deduced is correct ?
    Vote Corbyn ! Save this country !

    Comment


      #3
      Originally posted by fullyautomatix View Post
      Correct and by playing around you have figured it out. Are you just making sure what you deduced is correct ?
      Yeah just making sure I was correct and there wasn't anything else I was missing. Thanks for confirming
      I like big butts and I cannot lie.

      Comment

      Working...
      X