EXISTS, NOT EXISTS

[Michael Widenius]

>>>>> "jussi" == jussi jaaskelainen <Jussi> writes:

jussi> Hi!
jussi> We have some plans to move our SQL-databases from MSSql 7.0 to
jussi> MySql-linux.
jussi> Problem is that (according documentations) MySql don't support
jussi> SQL-commands "EXISTS" and "NOT EXISTS" what our code is full-off...
jussi> 1) Any plans to include those commands to MySql-server
jussi> 2) Posibility to go around problem with some SQL-code modifications...

jussi> F.Ex.

jussi>       'SELECT * FROM SpotBlocks97 ulkosp WHERE EXISTS ('
jussi>       ' SELECT * FROM SpotBlocks97 sp, RADIOID97 rd, SUBGROUPS97 sb'
jussi>       ' WHERE'
jussi>       '      sp.active=1 AND'
jussi>       '      rd.id=:BoxID AND'
jussi>       '      (sp.enddate is null or (sp.enddate is not null AND
sp.enddate> =:Curdate)) AND'
jussi>       '      (sp._group is null OR'
jussi>       '      (sp._group=rd._group AND'
jussi>       '      (sp.subgroup is null or (sp.subgroup IN'
jussi>       '      (SELECT DISTINCT subgroup from boxsubgroups97 where
jussi> Maingroup=rd._group and ID=rd.id)'
jussi>       '     ))))'
jussi>       '  AND sp.Number=ulkosp.Number'
jussi>       ')'
jussi>       'ORDER BY 1'

Hi!

As long as your columns are defined as NOT NULL, you can use LEFT JOIN
instead of NOT exists and DISTINCT as EXISTS. In case of complicated
queries, you can always use temporary tables:

(NOT EXISTS is easy, EXIST is a little harder)

CREATE TEMPORARY TABLE t1 (Number uint not null primary_key(Number))
  SELECT sp.Number as Number FROM SpotBlocks97 sp, RADIOID97 rd,
		                  SUBGROUPS97 sb,
                                  boxsubgroups97
  WHERE sp.active=1 AND rd.id=:BoxID AND
      (sp.enddate is null or
      (sp.enddate is not null AND sp.enddate>=:Curdate)) AND
      (sp._group is null OR
        (sp._group=rd._group AND
         (sp.subgroup is null or (sp.subgroup = boxsubgroups97.subgroup and 
          boxsubgroups97.Maingroup=rd._group and boxsubgroups97.ID=rd.id))));

SELECT DISTINCT ulkosp.* FROM SpotBlocks97 ulkosp,t1 WHERE t1.Number=ulkosp.Number;
DROP TABLE t1;

(Hope I got it right..., but the idea should work)

EXISTS AND NOT EXISTS will be implemented in MySQL 3.24 (at the same
time as sub selects)

Regards,
Monty