When I was studying databases, our instructors always told us to use ISO-compliant SQL code as much as possible. The prime advantage that this strategy has is that the code remains immune to the changes made to vendor specific SQL code. Ever since I wrote the piece on “CAST v/s CONVERT”, I have been looking for ISO equivalents to the SQL code I write.

Recently, I was writing a simple IF EXISTS check and from a purely academic standpoint, wondered if it’s ISO equivalent (SOME or ANY) can be used in SQL Server. Today’s post is based on the confirmatory test I did.

What does EXISTS do?

Very briefly, the EXISTS keyword checks if a particular sub-query returns any records or not. The sub-query to be evaluated is typically fetching records matching a particular condition defined by the business. For example, an HR function may want to identify if any employees exist who have accumulated more than n number of vacation days. A query using the EXISTS keyword will look like the following:

USE AdventureWorks2012;

GO

DECLARE @minimumUnusedVacationHours INT = 40;

IF EXISTS (SELECT * FROM HumanResources.Employee WHERE VacationHours > @minimumUnusedVacationHours)

    BEGIN

        PRINT ‘Found using EXISTS’;

    END

ELSE

    BEGIN

        PRINT ‘Not Found using EXISTS’;

    END

GO

The ISO equivalent – SOME or ANY clause

If we simplify the sub-query that is typically used for an EXISTS evaluation, what happens is that a scalar value is compared with a set of values. A special case would be to compare the scalar value with a single-column set, which is the most common condition found in production systems today.

The ISO equivalent to EXISTS is the SOME or ANY clause. The query shown above can therefore also be written as:

USE AdventureWorks2012;

GO

DECLARE @minimumUnusedVacationHours INT = 40;

IF @minimumUnusedVacationHours < SOME (SELECT VacationHours FROM HumanResources.Employee)

    BEGIN

        PRINT ‘Found using SOME’;

    END

ELSE

    BEGIN

        PRINT ‘Not Found using SOME’;

    END

GO

Conclusion

While it may be quite difficult to find production code that uses these ISO equivalents (my own code does not use SOME or ANY), it is still interesting to know about the basic standards that drive a vendor specific implementation.

A tip for all the students out there: These standards are the building blocks of any SQL-based RDBMS system in the market today – knowing them makes understanding SQL Server a lot easier.

Please note that it is important to keep in mind that ISO equivalents for all T-SQL operators may not be available (e.g. MERGE is a Microsoft SQL Server specific keyword and will not have an ISO equivalent).

Further Reading:

• SOME or ANY: http://msdn.microsoft.com/en-us/library/ms175064.aspx
  
• Comparison operators modified by ANY, SOME or ALL: http://msdn.microsoft.com/en-us/library/ms187074(v=sql.105).aspx

Until we meet next time,

Be courteous. Drive responsibly.