The SQL WHERE ANY and ALL syntax

The general ANY syntax is:

  1. SELECT column–names
  2. FROM table–name
  3. WHERE column–name operator ANY
  4. (SELECT column–name
  5. FROM table–name
  6. WHERE condition)

The general ALL syntax is:

  1. SELECT column–names
  2. FROM table–name
  3. WHERE column–name operator ALL
  4. (SELECT column–name
  5. FROM table–name
  6. WHERE condition)

 

 

SQL ANY Example

Problem: Which products were sold by the unit (i.e. quantity = 1)

  1. SELECT ProductName
  2. FROM Product
  3. WHERE Id = ANY
  4. (SELECT ProductId
  5. FROM OrderItem
  6. WHERE Quantity = 1)

Results: 17 records

ProductName
Chef Anton’s Cajun Seasoning
Grandma’s Boysenberry Spread
Uncle Bob’s Organic Dried Pears
Ikura
Konbu
Tofu
Teatime Chocolate Biscuits
Sir Rodney’s Marmalade

 

 

SQL ALL Example

Problem: List customers who placed orders that are
larger than the average of each customer order

  1. SELECT DISTINCT FirstName + ‘ ‘ + LastName as CustomerName
  2. FROM Customer, [Order]
  3. WHERE Customer.Id = [Order].CustomerId
  4. AND TotalAmount > ALL
  5. (SELECT AVG(TotalAmount)
  6. FROM [Order]
  7. GROUP BY CustomerId)

Results: 22 records

CustomerName
Art Braunschweiger
Christina Berglund
Elizabeth Lincoln
Frédérique Citeaux
Georg Pipps
Horst Kloss
Howard Snyder
You Might Like
28 Comments
  • Google
    says:

    Right here is the perfect web site for anybody who really wants to find out about this topic. You understand so much its almost hard to argue with you (not that I personally would want to…HaHa). You certainly put a brand new spin on a topic which has been written about for years. Great stuff, just great!

    응답
  • Continued
    says:

    An outstanding share, I just given this onto a coworker who was doing a little evaluation on this. As well as he actually acquired me breakfast because I located it for him. smile. So let me rephrase that: Thnx for the treat! But yeah Thnkx for spending the time to review this, I feel highly about it and enjoy learning more on this topic. Preferably, as you become expertise, would you mind updating your blog site with even more details? It is very practical for me. Large thumb up for this blog post!

    응답
  • i thought about this
    says:

    Youre so trendy! I do not suppose Ive check out anything similar to this before. So nice to discover somebody with some initial thoughts on this subject. realy thank you for starting this up. this web site is something that is needed online, someone with a little originality. beneficial job for bringing something brand-new to the internet!

    응답
  • Maryanna Lusane
    says:

    Aw, this was a truly good blog post. In idea I would love to place in creating like this in addition? requiring time and also actual effort to make a great post? yet what can I claim? I put things off alot and by no means seem to get something done.

    응답
  • James Adelblue
    says:

    I?m impressed, I need to say. Actually seldom do I experience a blog that?s both educative and amusing, and let me inform you, you have struck the nail on the head. Your idea is outstanding; the problem is something that insufficient individuals are talking smartly about. I am extremely satisfied that I came across this in my look for something associating with this.

    응답
  • this contact form
    says:

    When I initially commented I clicked the -Alert me when new comments are added- checkbox and also currently each time a remark is added I get four e-mails with the same remark. Exists any way you can eliminate me from that solution? Many thanks!

    응답
  • can dogs be allergic to cbd?
    says:

    Great article. I discover something a lot more difficult on different blog sites day-to-day. It will always be stimulating to review content from various other authors and also exercise a something from their shop. I?d choose to utilize some with the content on my blog whether you don?t mind. Natually I?ll provide you a web link on your web blog site. Many thanks for sharing.

    응답
  • cbd oil for dogs in seattle
    says:

    I?m impressed, I must say. Truly hardly ever do I run into a blog that?s both instructional as well as enjoyable, as well as let me tell you, you have hit the nail on the head. Your idea is outstanding; the problem is something that inadequate individuals are speaking smartly around. I am really delighted that I stumbled across this in my search for something connecting to this.

    응답
  • at bing
    says:

    The following time I read a blog site, I wish that it doesn’t disappoint me as high as this set. I suggest, I recognize it was my choice to read, but I really thought youd have something intriguing to claim. All I hear is a lot of yawping about something that you can take care of if you werent as well hectic seeking interest.

    응답
  • John Deere Diagnostic and Test Manuals
    says:

    Can I just now say such a relief to find one who truly knows what theyre dealing with on the internet. You definitely know how to bring a challenge to light and produce it crucial. Workout . need to look at this and see why side of your story. I cant think youre less well-liked simply because you absolutely contain the gift.

    응답
  • John Deere Technical Manuals
    says:

    Good – I should definitely pronounce, impressed with your web site. I had no trouble navigating through all the tabs as well as related information ended up being truly easy to do to access. I recently found what I hoped for before you know it at all. Reasonably unusual. Is likely to appreciate it for those who add forums or something, web site theme . a tones way for your client to communicate. Nice task.

    응답
  • aluminium security doors
    says:

    Thanks for ones marvelous posting! I certainly enjoyed reading it, you’re a great author.I will ensure that I bookmark your blog and will come back down the road. I want to encourage yourself to continue your great posts, have a nice day!

    응답
댓글 남기기