FirebirdSQL logo

EXISTS

EXISTS tells you whether a subquery returns at least one row of data.Suppose you want a list of farmers who are also landowners.You could get one like this:

SELECT Farmer FROM Farms WHERE EXISTS
  (SELECT * FROM Landowners
   WHERE Landowners.Name = Farms.Farmer)

This query returns the names of all farmers who also figure in the Landowners table.The EXISTS predicate returns true if the result set of the subselect contains at least one row.If it is empty, EXISTS returns false. EXISTS never returns NULL, because a result set always either has rows, or hasn’t.Of course the subselect’s search condition may evolve to NULL for certain rows, but that doesn’t cause any uncertainty: such a row won’t be included in the subresult set.

Note

In reality, the subselect doesn’t return a result set at all.The engine simply steps through the Landowners records one by one and applies the search condition.If it evaluates to true, EXISTS returns true immediately and the remaining records aren’t checked.If it evaluates to false or NULL, the search continues.If all the records have been searched and there hasn’t been a single true result, EXISTS returns false.

NOT EXISTS always returns the opposite of EXISTS: false or true, never NULL.NOT EXISTS returns false immediately if it gets a true result on the subquery’s search condition.Before returning true it must step through the entire set.

SINGULAR

SINGULAR is an InterBase/Firebird extension to the SQL standard.It is often described as returning true if exactly one row in the subquery meets the search condition.By analogy with EXISTS this would make you expect that SINGULAR too will only ever return true or false.After all, a result set has either exactly 1 row or a different number of rows.Unfortunately, all versions of Firebird up to and including 2.0 have a bug that causes NULL results in a number of cases.The behaviour is pretty inconsistent, but at the same time fully reproducible.For instance, on a column A containing (1, NULL, 1), a SINGULAR test with subselect “A=1” returns NULL, but the same test on a column with (1, 1, NULL) returns false.Notice that only the insertion order is different here!

To make matters worse, all versions prior to 2.0 sometimes return NULL for NOT SINGULAR where false or true is returned for SINGULAR.In 2.0, this at least doesn’t happen anymore: it’s either false vs. true or twice NULL.

The code has been fixed for Firebird 2.1; from that version onward SINGULAR will return:

  • false if the search condition is never true (this includes the empty-set case);

  • true if the search condition is true for exactly 1 row;

  • false if the search condition is true for more than 1 row.

Whether the other rows yield false, NULL or a combination thereof, is irrelevant.

NOT SINGULAR will always return the opposite of SINGULAR (as is already the case in 2.0).

In the meantime, if there’s any chance that the search condition may evolve to NULL for one or more rows, you should always add an IS NOT NULL condition to your [NOT] SINGULAR clauses, e.g.like this:

... SINGULAR( SELECT * from MyTable
              WHERE MyField > 38
              AND MyField IS NOT NULL )

Searches

If the search condition of a SELECT, UPDATE or DELETE statement resolves to NULL for a certain row, the effect is the same as if it had been false.Put another way: if the search expression is NULL, the condition is not met, and consequently the row is not included in the output set (or is not updated/deleted).

Note

The search condition or search expression is the WHERE clause minus the WHERE keyword itself.

Some examples (with the search condition in boldface):

SELECT Farmer, Cows FROM Farms WHERE Cows > 0 ORDER BY Cows

The above statement will return the rows for farmers that are known to possess at least one cow.Farmers with an unknown (NULL) number of cows will not be included, because the expression “NULL > 0” returns NULL.

SELECT Farmer, Cows FROM Farms WHERE NOT (Cows > 0) ORDER BY Cows

Now, it’s tempting to think that this will return “all the other records” from the Farms table, right?But it won’t — not if the Cows column contains any NULLs.Remember that not(NULL) is itself NULL.So for any row where Cows is NULL, “Cows > 0” will be NULL, and “NOT (Cows > 0)” will be NULL as well.

SELECT Farmer, Cows, Sheep FROM Farms WHERE Cows + Sheep > 0

On the surface, this looks like a query returning all the farms that have at least one cow and/or sheep (assuming that neither Cows nor Sheep can be a negative number).However, if farmer Fred has 30 cows and an unknown number of sheep, the sum Cows + Sheep becomes NULL, and the entire search expression boils down to “NULL > 0”, which is…​ you got it.So despite his 30 cows, our friend Fred won’t make it into the result set.

As a last example, we shall rewrite the previous statement so that it will return any farm which has at least one animal of a known kind, even if the other number is NULL.To do that, we exploit the fact that “NULL or true” returns true — one of the rare occasions where a NULL operand doesn’t render the entire expression NULL:

SELECT Farmer, Cows, Sheep FROM Farms WHERE Cows > 0 OR Sheep > 0

This time, Fred’s thirty cows will make the first comparison true, while the sheep bit is still NULL.So we have “true or NULL”, which is true, and the row will be included in the output set.

Caution

If your search condition contains one or more IN predicates, there is the additional complication that some of the list elements (or subselect results) may be NULL.The implications of this are discussed in [nullguide-pred-in].

Sorts

In Firebird 2, NULLs are considered “smaller” than anything else when it comes to sorting.Consequently, they come first in ascending sorts and last in descending sorts.You can override this default placement by adding a NULLS FIRST or NULLS LAST directive to the ORDER BY clause.

In earlier versions, NULLs were always placed at the end of a sorted set, no matter whether the order was ascending or descending.For Firebird 1.0, that was the end of the story: NULLs would always come last in any sorted set, period.Firebird 1.5 introduced the NULLS FIRST/LAST syntax, so you could force them to the top or bottom.

To sum it all up:

Table 1. NULLs placement in ordered columns

Ordering

NULLs placement

Firebird 1

Firebird 1.5

Firebird 2

order by Field [asc]

bottom

bottom

top

order by Field desc

bottom

bottom

bottom

order by Field [asc | desc] nulls first

 — 

top

top

order by Field [asc | desc] nulls last

 — 

bottom

bottom

Specifying NULLS FIRST on an ascending or NULLS LAST on a descending sort in Firebird 2 is of course rather pointless, but perfectly legal.The same is true for NULLS LAST on any sort in Firebird 1.5.

Note

  • If you override the default NULLs placement, no index will be used for sorting.In Firebird 1.5, that is the case with NULLS FIRST.In 2.0 and higher, with NULLS LAST on ascending and NULLS FIRST on descending sorts.

  • If you open a pre-2.0 database with Firebird 2, it will show the old NULL ordering behaviour (that is: at the bottom, unless overridden by NULLS FIRST).A backup-restore cycle will fix this, provided that at least the restore is executed with Firebird 2’s gbak!

  • Firebird 2.0 has a bug that causes the NULLS FIRST|LAST directive to fail under certain circumstances with SELECT DISTINCT.See the bugs list for more details.
Warning

Don’t be tempted into thinking that, because NULL is now the “smallest thing” in sorts, an expression like “NULL < 3” will return true in Firebird 2 and up.It won’t.Using NULL in this kind of expression will always give a NULL outcome.