Subscribe to this thread
Home - General / All posts - Inner join or cross product
Forest
625 post(s)
#20-Sep-18 09:55

I have been brushing up on my SQL skills and have seen SQL statements like this:

SELECT name, pet

FROM people, pets

WHERE people.id = pet_owner.id

Usually I would write

SELECT name, pet

FROM people INNER JOIN pets ON people.id = pet_owner.id

Both work, but I am wondering if the first way creates a cross product in memory and then applies the filter. I know for certain that the second statement can be optimised. I have no idea whether the first statement is. Would love to hear more about the difference between the two statements.

Dimitri


7,413 post(s)
#20-Sep-18 12:25

The first query is really a CROSS JOIN that uses shorthand, "comma" syntax instead of spelling out CROSS JOIN.

SELECT name, pet

FROM people, pets

WHERE people.id = pet_owner.id

is a shorthand way, using a comma, of writing

SELECT name, pet

FROM people CROSS JOIN pets

WHERE people.id = pet_owner.id

How that compares to the equivalent INNER JOIN form using an ON is discussed in the "Cross Joins with WHERE Clauses" section of the JOIN Statements topic.

In a nutshell, use the INNER JOIN because that can optimize better than the process of creating a Cartesian product and then applying the WHERE condition, as you surmised.

adamw


10,447 post(s)
#24-Sep-18 14:25

To add to what Dimitri said: there is a semantic difference between ON and WHERE, they generate different results on outer joins. For the specific example that you posted, it does not matter what you write, the semantics are the same and the queries are simple enough to have us generate the exact same internal code. But once the queries start growing, and, god forbid, some joins stop being inner / cross and start becoming outer, putting the condition into WHERE will just return different results than if you put it into ON - you should use either ON or WHERE depending on when specifically you want the filtering to happen.

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.