CODEWITHSUNDEEP
sql
Airbum88
Airbum88

Reputation: 19

Are cartesian (cross) joins with where statement still slower than inner joins?

Compare these two queries:

select b.prod_name, b.prod_category, a.transaction_amt, a.transaction_dt
  from transactions a, prod_xref b
where a.prod_id = b.id

VS.

select b.prod_name, b.prod_category, a.transaction_amt, a.transaction_dt
  from transactions a 
  inner join b.prod_xref b on a.prod_id = b.id

Is the first query still slower than the second? What are the benefits / disadvantages of using a cartesian join vs an explicit join statement?

Upvotes: 0

Views: 49

Answers (1)

Niqua
Niqua

Reputation: 644

Answering your question, "Cartesian" or "Cross" join are much slower than almost any joins. The reason is that CROSS join multiply each row of t1 by each row of t2.

The example you provided is not a CROSS join;

It's the old syntax of inner joins, where 2 or more consecutive tables are given in FROM clause, comma separated.

Upvotes: 2

Related Questions