SQL select EXIST over multiple tables as Boolean (Bit)

Question

I am using the accepted answer from this question, but for multiple tables.

$result = mssql_query("SELECT CAST(
       CASE WHEN 
         EXISTS(select 1 from codes where code = '".$generatedCode."') OR 
         EXISTS(select 1 from pakete where code = '".$generatedCode."') OR
         EXISTS(select 1 from kunden where code = '".$generatedCode."') OR
         EXISTS(select 1 from formulare where code = '".$generatedCode."') OR  
         EXISTS(select 1 from berater where code = '".$generatedCode."') 
       THEN 1 
       ELSE 0 
       END 
    AS BIT) as 'exists'";

$row = mssql_fetch_assoc($result);
if ($row['exists']==0)

Theoretically it looks good, practically it works too. But it happens from time to time, that the query (i think) wents wrong and returns 1 even if there is a "code" in one of the tables.

Maybe someone knows a better solution for checking across tables, where it could happen that the searched varchar exists in one or two tables only.

fyi: generatedcode is a nine digit alphanumeric unique string.

Regards, Markus

UPDATE: The Query works as expected, the failure was a wrong named variable after return, so the original generatedcode (the duplicate one) was used instead. Thanks for your help and for simplifying the query.

score 1 · Accepted Answer · answered May 26 '14 at 14:00

I am guessing that your code works. You can do a couple things to improve it. First, the cast() isn't necessary because you can put in bit literals directly (see here). The following code uses both methods for expressing a bit literal.

Second, don't use single quotes for column names (and, of course, mysql_ is deprecated, but that is another matter because this just focuses on the query). In fact, you should avoid using reserved words as column names. So:

SELECT (CASE WHEN 
         EXISTS(select 1 from codes where code = '".$generatedCode."') OR 
         EXISTS(select 1 from pakete where code = '".$generatedCode."') OR
         EXISTS(select 1 from kunden where code = '".$generatedCode."') OR
         EXISTS(select 1 from formulare where code = '".$generatedCode."') OR  
         EXISTS(select 1 from berater where code = '".$generatedCode."') 
         THEN 0b1 
         ELSE  b'0' 
       END) as CodeExists

By the way, I would discourage you from using bits, unless you really know what you are doing in terms of optimization. They may not save space and they may not run faster. The following seems totally reasonable to me:

SELECT (EXISTS(select 1 from codes where code = '".$generatedCode."') OR 
        EXISTS(select 1 from pakete where code = '".$generatedCode."') OR
        EXISTS(select 1 from kunden where code = '".$generatedCode."') OR
        EXISTS(select 1 from formulare where code = '".$generatedCode."') OR  
        EXISTS(select 1 from berater where code = '".$generatedCode."') 
       ) as CodeExists

score 0 · Answer 2 · answered May 26 '14 at 13:50

0

You know, you can use simple union and num_rows, there's no need to select a bit - it won't do you any good unless your query is a part of other complex query:

$sql="select 1 from codes where code = '".$generatedCode."' 
union select 1 from pakete where code = '".$generatedCode."'
union select 1 from kunden where code = '".$generatedCode."'
union select 1 from formulare where code = '".$generatedCode."'
union select 1 from berater where code = '".$generatedCode."'";
$result = mssql_query($sql);
if(mssql_num_rows($result)){}

answered May 26 '14 at 13:50

Jehy

4,729
1
38
55

This would be much less efficient, because it would process all five conditions before returning a value. Plus, it would return the value as an integer, not a bit. – Gordon Linoff May 26 '14 at 13:55
Are you sure? Unfortunately I don't have MSSQL installation at hand but in Oracle it could be pretty same and may be even faster because of the caching mechanism - try it. – Jehy May 26 '14 at 14:03
. . Yes. The `union` needs to run all the subqueries, get the results, and remove duplicates before it can return any rows. This requires processing all the values. The `or` method should stop at the first match. – Gordon Linoff May 26 '14 at 14:19
Yup, of cause you should use UNION ALL which does not remove duplicates and does not process values. As for subqueries execution time - it depends on SQL interpreter and caching. – Jehy May 26 '14 at 14:23
. . The `union all` would still process all the subqueries before passing back any results. In addition, your version would return multiple rows, where the OP's version only adds a column to a single row. – Gordon Linoff May 26 '14 at 14:28
Yup, that is why I already wrote that it should only be considered when using in standalone script, not in complex query. – Jehy May 26 '14 at 14:39

SQL select EXIST over multiple tables as Boolean (Bit)

2 Answers2