CODEWITHSUNDEEP
sqljoin
rubixibuc
rubixibuc

Reputation: 7397

SQL and implicit join

w3schools.com has the following SQL statement example:

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

What is not clear to me is how the product order table is joined with the persons table. Is this SQL proper, and what does it imply about the result set sent back?

Upvotes: 0

Views: 598

Answers (2)

Dave Markle
Dave Markle

Reputation: 97691

It's technically proper, though I would avoid using that type of syntax for a couple reasons which I won't go into right here.

That syntax is colloquially called the "old style" join syntax and is equivalent to:

SELECT Product_Orders.OrderID, Persons.LastName, Persons.FirstName 
FROM Persons
CROSS JOIN Product_Orders 
WHERE Persons.LastName='Hansen' AND Persons.FirstName='Ola'

The answer of how they "join" together is that they don't. The result of the query is the cross product of the entire Product_Orders table with the "Ola Hanson" row(s) in the Persons table. It doesn't make much sense in most of the use cases I can think of, to be quite honest with you.

The really strange thing about the results you'll get here is that the OrderID from the Product_Orders table will not necessarily align with the person on the order. It looks to be a mistake of omission on the page -- though to be fair the example intends to demonstrate aliasing, not joins.

W3Schools doesn't have a good reputation around here.

Upvotes: 2

fanbondi
fanbondi

Reputation: 1047

Firs of all you have to understand how Joins works. From you query:

  1. A cross product is done between the two tables supplied in the SQL statement, you can see this from this FROM Persons, Product_Orders.

  2. For every record in Persons table is joined to each record in the Product_Orders table. This will give you a huge temp table if both tables are big.

  3. Then your filter i.e. the qualifying condition will be applied to this temp table to give you your result.

I hope this helps.

Upvotes: 1

Related Questions