CODEWITHSUNDEEP
javamysqlsql
William Roberts
William Roberts

Reputation: 329

SQL speed increase: Left Join, or Cartesian product/where clause

I'm working with a sql query in a webservice and am looking to increase its speed. As of now, the query takes the cartesian products of multiple tables and uses a where clause to separate them out.

I was wondering if switching to a LEFT JOIN would make the query execute faster since every row I want in my result is based off of one table, with additional information being pulled in from others.

Upvotes: 0

Views: 399

Answers (1)

O. Jones
O. Jones

Reputation: 108841

There's no way to express the intent of a LEFT JOIN in MySQL using Cartesian syntax.

In Oracle, this so-called omega-join syntax 

SELECT whatever FROM A, B WHERE A.col=B.col(+)

is equivalent to this:

SELECT whatever FROM A LEFT JOIN B ON A.col=B.col

But in MySQL only the latter syntax works. So if you want to do true LEFT JOIN operations in MySQL you have no choice: the cartesian/WHERE syntax doesn't work.

As far as optimization goes, MySQL's query execution planner does a pretty good job of understanding ordinary INNER JOIN operations expressed in cartesian/WHERE syntax. But you're almost always better off using the JOIN syntax. It's easier to read, and the query planner understands it.

Upvotes: 0

Related Questions