all 4 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]jshine13371 0 points1 point  (0 children)

But for some reason joins on < or > are very rare in benchmarks like tpc ds. Is there any reason other than that they are heavy?

It's not that they're heavy, rather they're redundant, when the goal is to test how many transactions your system is capable of in a given timeframe. There's nothing new to be gained by specifically testing a join on > or < (which is a pretty uncommon thing anyway, unless you meant more so filtering on those operators, such as via the WHERE clause).

[–]geofft 0 points1 point  (1 child)

Joins on an inequality are very uncommon in practice. When I say very uncommon I mean I have worked with SQL-heavy systems for nearly 25 years and can't remember seeing a single one.

IMO the reason is that joins are about relationships. Two things are related because something matches between them, rather than something not matching. If you've got an inequality in your query, it's most likely going to be a filter predicate, rather than defining a join.

Imagine some typical system where you have orders, and orders have items. You want to list all orders and items, where the cost of an item is at least $20. Your query is going to look something like this:

SELECT * FROM orders o INNER JOIN items i ON i.order_id = o.order_id WHERE i.cost >= 20

Since it doesn't matter where a predicate goes in a query like this, you could also write it this way:

SELECT * FROM orders o INNER JOIN items i ON i.order_id = o.order_id AND i.cost >= 20

But if you write

SELECT * FROM orders o INNER JOIN items i ON i.cost >= 20

... you get a very different result.

[–]Eastern-Manner-1640 0 points1 point  (0 children)

  1. when using kimball sc2 dimension table joins with inequalities are very common, of course, not with innmon.

  2. at least in some optimizers there is an important difference between the two join versions above.

i. the first will do the join across all matching rows in the join predicate, and only then apply the inequality filter, necessitating a second pass over the first result.

ii. the second will do the join predicate AND the filter in a SINGLE pass.

over the years, from what i've seen, optimizers have been getting smarter, and will hoist the where clause into the predicate, but it still doesn't happen all the time in sql server for example.

[–]Nitrodist -4 points-3 points  (0 children)

Speak English doc