Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2011
    Posts
    3

    Unanswered: w3schools alias example - can sb explain

    Hi,

    I came across a query at SQL Alias I can't understand. It says:

    SELECT po.OrderID, p.LastName, p.FirstName
    FROM Persons AS p,
    Product_Orders AS po
    WHERE p.LastName='Hansen' AND p.FirstName='Ola'

    From what I see, there's no "JOIN" keyword used, but it's apparently going to operate on two separate tables. If it's going two retrieve data from "Product_Orders" tables, why isn't it mentioned after "WHERE" at all? So, how could "p.LastName=" and "p.FirstName" be related to "po.OrderID", how it knows what rows should be picked from "Product_Orders" table?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    [SIGH] yet another example of crappy sql from w3schools...

    QUESTION: how it knows what rows should be picked from "Product_Orders" table?

    ANSWER: by the absence of any filtering or join criteria, it picks all of them




    does this make sense? no, but then, this example was about table aliases only

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2011
    Posts
    3
    Thanks, so the result will be like this, right?

    OrderID|LastName|FirstName
    1|Hansen|Ola
    2|Hansen|Ola
    3|Hansen|Ola
    4|Hansen|Ola
    5|Hansen|Ola
    ...

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    To add to Rudy's comments, as there is no join between the Persons and Product_Orders table you will have "Cartesian Join". This is where for every row in one table will join with every row from a second table resulting in large amounts of data. In some cases this is desirable, in other cases not.

    As for no JOIN the above statement could be rewritten as follows:

    Code:
    SELECT po.OrderID, p.LastName, p.FirstName
    FROM Persons AS p
    JOIN Product_Orders AS po
    WHERE p.LastName='Hansen' AND p.FirstName='Ola'
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by expired View Post
    Thanks, so the result will be like this, right?
    right


    cartesian joins aren't necessarily bad if there are WHERE clause conditions

    most optimizers are smart enough to push those conditions up into the data retrieval stage



    did that make sense to anyone but me?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2011
    Posts
    3
    Ok thank you for answers

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •