I've a self-join with only inequalities (< and >)

select a.name, a.salary, b.name, b.salary
  from salary_tax a
     , salary_tax b
 where a.salary > b.salary
   and a.tax < b.tax

on a simple table like this

CREATE TABLE salary_tax (
  name character varying(255) NOT NULL,
  dept character varying(50),
  salary integer,
  tax integer)

All the DBMSs I tried are doing a Cartesian product followed by selection which is very expensive any idea on how to do it better?

I was thinking about using a variant of sort-merge join?

asked Dec 16 '14 at 09:54

momovt's gravatar image


edited Dec 16 '14 at 12:04

Markus%20Winand's gravatar image

Markus Winand ♦♦

Well, no. In theory, you are right that theta-joins can be executed using a merge join. How to convince the optimizer to actually do it, is a different story. In general, merge join has gone out of fashion. The only database that I regularly see using it is SQL Server.

(Dec 16 '14 at 12:06) Markus Winand ♦♦