Home > Functions and DMVs, SQL Queries, SQL Server, T-SQL > “ALL” : the transact SQL statement

“ALL” : the transact SQL statement

The T-SQL statement “All” is used very rarely by developers. Usually, we don’t get requirements where “All” can be used. The statement can be very useful in some cases and can reduce a huge query to a single liner. “All” is used to check a record from a table against all the values in a column of another table.The syntax is as given below

scalar_expression { = | <> | != | > | >= | !> | < | <= | !< } ALL ( subquery )

For example you need to get the lists of items in the table “ITEMLIST1” where the cost is lesser than or equal to the cost all the items in the table “ITEMLIST2”. The requirement can be easily achieved by using “All” as


SELECT ITEMNAME
FROM ITEMLIST2 WITH(NOLOCK)
WHERE   ITEMCOST <= ALL(    SELECT ITEMCOST
FROM
ITEMLIST2 WITH(NOLOCK)
)

Go ahead and use this simple statement whereever you can.

Advertisements
  1. September 2, 2009 at 7:57 pm

    Authoritative answer, funny …

  2. October 11, 2009 at 10:43 pm

    Hello from Russia)

  1. No trackbacks yet.

Leave a Reply

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

%d bloggers like this: