#0260-SQL Server-SOME or ANY-ISO versions of EXISTS


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:



Until we meet next time,


Be courteous. Drive responsibly.

Advertisements

One thought on “#0260-SQL Server-SOME or ANY-ISO versions of EXISTS

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s