SQL WHERE ANY, ALL Clause

The SQL WHERE ANY and ALL syntax

The general ANY syntax is:

  1. SELECT columnnames
  2. FROM tablename
  3. WHERE columnname operator ANY
  4. (SELECT columnname
  5. FROM tablename
  6. WHERE condition)

The general ALL syntax is:

  1. SELECT columnnames
  2. FROM tablename
  3. WHERE columnname operator ALL
  4. (SELECT columnname
  5. FROM tablename
  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

“SQL WHERE ANY, ALL Clause”의 157개의 댓글

  1. I simply want to tell you that I am new to blogs and truly liked you’re blog. Very likely I’m want to bookmark your blog . You amazingly have incredible stories. Thanks for revealing your website page.

  2. 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!

  3. 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.

  4. 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.

  5. 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.

  6. 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.

  7. 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.

  8. 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!

  9. 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.

  10. 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.

  11. I?d need to talk to you here. Which is not something I generally do! I delight in reading a post that will certainly make individuals assume. Also, many thanks for permitting me to comment!

  12. 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!

  13. 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!

  14. 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!

  15. Aw, this was a truly nice blog post. In suggestion I would like to put in creating similar to this additionally? taking some time and real initiative to make a very good short article? yet what can I claim? I hesitate alot as well as never seem to get something done.

  16. Oh my goodness! an outstanding write-up dude. Thanks However I am experiencing problem with ur rss. Don?t know why Not able to register for it. Exists any individual obtaining the same rss trouble? Any individual who understands kindly respond. Thnkx

  17. Heya i am for the first time here. I found this board and I in finding It really useful & it helped me out much.
    I hope to offer something back and help others such as you aided me.

  18. I simply want to say I am just very new to blogging and certainly loved your blog. Likely I’m going to bookmark your blog post . You actually have outstanding articles. Thanks for revealing your web site.

  19. Greetings from California! I’m bored to tears at work so I decided to browse your blog on my iphone during lunch break. I enjoy the knowledge you present here and can’t wait to take a look when I get home. I’m surprised at how fast your blog loaded on my mobile .. I’m not even using WIFI, just 3G .. Anyways, amazing blog!

  20. Thanks for revealing your ideas. I would also like to convey that video games have been actually evolving. Modern tools and innovative developments have assisted create sensible and active games. These kinds of entertainment games were not really sensible when the actual concept was first being attempted. Just like other styles of technological know-how, video games as well have had to grow through many generations. This is testimony towards fast growth and development of video games.

  21. I just want to tell you that I am new to weblog and definitely enjoyed your website. Most likely I’m want to bookmark your blog . You actually come with great articles. Thanks for sharing with us your web page.

  22. I have seen a great deal of useful issues on your website about pc’s. However, I’ve got the opinion that notebook computers are still less than powerful adequately to be a sensible choice if you typically do projects that require loads of power, like video touch-ups. But for world wide web surfing, word processing, and majority of other prevalent computer work they are okay, provided you cannot mind your little friend screen size. Many thanks sharing your ideas.

  23. hi!,I really like your writing very much! percentage we keep up a correspondence extra approximately your article on AOL? I require an expert in this space to unravel my problem. Maybe that is you! Having a look forward to look you.

  24. I think this is one of the most important info for me. And i’m glad reading your article. But should remark on few general things, The web site style is perfect, the articles is really nice : D. Good job, cheers

  25. Very nice post. I just stumbled upon your weblog and wanted to mention that I’ve truly loved browsing your blog posts. In any case I’ll be subscribing on your feed and I’m hoping you write once more very soon!

  26. I simply want to say I am just all new to blogs and certainly enjoyed you’re website. Likely I’m planning to bookmark your website . You amazingly have outstanding well written articles. Thanks a lot for sharing with us your blog site.

  27. Hey there I am so delighted I found your site, I really found you by accident, while I was looking on Digg for something else, Nonetheless I am here now and would just like to say thanks for a tremendous post and a all round thrilling blog (I also love the theme/design), I don’t have time to read through it all at the moment but I have book-marked it and also added in your RSS feeds, so when I have time I will be back to read a great deal more, Please do keep up the superb work.

  28. Great goods from you, man. I’ve understand your stuff previous to and you’re just extremely excellent. I really like what you’ve acquired here, certainly like what you are stating and the way in which you say it. You make it entertaining and you still take care of to keep it wise. I cant wait to read much more from you. This is really a great site.

  29. Aw, this was an incredibly good post. Spending some time and actual effort to produce a good articleÖ but what can I sayÖ I put things off a lot and never manage to get nearly anything done.

  30. Unquestionably believe that which you said. Your favorite justification appeared to be on the internet the easiest thing to be aware of. I say to you, I definitely get annoyed while people think about worries that they plainly do not know about. You managed to hit the nail upon the top and defined out the whole thing without having side effect , people could take a signal. Will likely be back to get more. Thanks

  31. Many thanks for your time to have had these things together on this web site. Emily and i also very much loved your suggestions through your own articles with certain things. I realize that you have numerous demands on your own timetable hence the fact that an individual like you took just as much time like you did to steer people really like us by this article is also highly loved.

  32. Good day! I could have sworn I’ve visited your blog before but after going through many of the articles I realized it’s new to me. Anyways, I’m definitely happy I discovered it and I’ll be bookmarking it and checking back frequently.

  33. I actually wanted to write a quick comment to thank you for these fabulous solutions you are placing on this site. My particularly long internet look up has finally been recognized with useful facts and techniques to talk about with my relatives.

  34. Right here is the right blog for anybody who hopes to understand this topic. You know so much its almost tough to argue with you (not that I actually would want to횜HaHa). You definitely put a new spin on a topic which has been written about for many years. Wonderful stuff, just wonderful!

  35. After looking at a handful of the blog posts on your site, I truly appreciate your way of writing a blog. I saved as a favorite it to my bookmark site list and will be checking back soon. Please visit my website as well and let me know what you think.

  36. I loved up to you will obtain performed proper here. The cartoon is attractive, your authored subject matter stylish. however, you command get got an edginess over that you wish be delivering the following. ill no doubt come more beforehand again as precisely the similar nearly a lot ceaselessly inside of case you shield this hike.

  37. I do like the manner in which you have presented this specific situation plus it does offer us some fodder for thought. However, because of what precisely I have observed, I only wish as the feed-back stack on that people today stay on point and in no way get started on a tirade regarding some other news of the day. Anyway, thank you for this outstanding piece and even though I do not really go along with this in totality, I respect your viewpoint.

  38. obviously like your web-site but you have to check the spelling on quite a few of your posts. A number of them are rife with spelling problems and I find it very bothersome to tell the truth nevertheless I’ll surely come back again.

  39. Spot on with this write-up, I really feel this site needs far more attention. I’ll probably be back again to see more, thanks for the information.

  40. Howdy! This blog post could not be written any better! Looking through this post reminds me of my previous roommate! He always kept talking about this. I most certainly will forward this article to him. Fairly certain he’s going to have a good read. I appreciate you for sharing!

  41. naturally like your web site but you have to test the spelling on several of your posts. A number of them are rife with spelling issues and I find it very troublesome to tell the reality on the other hand I will surely come again again.

  42. Just wish to say your article is as astounding. The clarity in your post is just nice and i could assume you are an expert on this subject. Well with your permission allow me to grab your feed to keep updated with forthcoming post. Thanks a million and please keep up the gratifying work.

  43. What i do not understood is if truth be told how you’re no longer actually a lot more well-favored than you may be right now. You’re so intelligent. You recognize therefore significantly relating to this matter, produced me for my part believe it from numerous varied angles. Its like men and women aren’t interested except it’s one thing to do with Lady gaga! Your individual stuffs excellent. At all times handle it up!

  44. I seriously love your website.. Very nice colors & theme. Did you develop this site yourself? Please reply back as I’m hoping to create my own site and want to find out where you got this from or what the theme is called. Appreciate it!

  45. The very next time I read a blog, I hope that it does not disappoint me just as much as this particular one. I mean, Yes, it was my choice to read through, nonetheless I actually thought you would have something helpful to talk about. All I hear is a bunch of complaining about something you can fix if you weren’t too busy looking for attention.

  46. I’m amazed, I must say. Seldom do I come across a blog that’s equally educative and engaging, and let me tell you, you’ve hit the nail on the head. The issue is something that not enough people are speaking intelligently about. I’m very happy I found this in my search for something regarding this.

  47. Great blog! Do you have any suggestions for aspiring writers? I’m planning to start my own website soon but I’m a little lost on everything. Would you recommend starting with a free platform like WordPress or go for a paid option? There are so many choices out there that I’m totally confused .. Any tips? Thanks a lot!

  48. With the whole thing which seems to be building throughout this specific subject material, a significant percentage of perspectives are very stimulating. Nonetheless, I beg your pardon, because I do not give credence to your whole plan, all be it radical none the less. It would seem to me that your comments are not completely rationalized and in fact you are yourself not wholly confident of the assertion. In any case I did appreciate looking at it.

  49. I have to thank you for the efforts you’ve put in writing this website. I’m hoping to see the same high-grade content from you later on as well. In truth, your creative writing abilities has encouraged me to get my very own website now 😉

  50. Good – I should certainly pronounce, impressed with your website. I had no trouble navigating through all tabs as well as related information ended up being truly simple to do to access. I recently found what I hoped for before you know it at all. Quite unusual. Is likely to appreciate it for those who add forums or anything, site theme . a tones way for your customer to communicate. Excellent task..

  51. I have been browsing on-line more than three hours these days, but I never found any attention-grabbing article like yours. It¡¦s beautiful value sufficient for me. In my view, if all website owners and bloggers made good content as you probably did, the net shall be much more helpful than ever before.

  52. Hi, i feel that i saw you visited my blog so i came to “return the desire”.I’m attempting to find issues to enhance my site!I guess its adequate to use a few of your ideas!!

  53. Aw, this was a very nice post. In idea I want to put in writing like this moreover – taking time and precise effort to make a very good article… however what can I say… I procrastinate alot and in no way appear to get something done.

  54. Hey there this is kinda of off topic but I was wondering if blogs use WYSIWYG editors or if you have to manually code with HTML. I’m starting a blog soon but have no coding know-how so I wanted to get advice from someone with experience. Any help would be enormously appreciated!

  55. I have been browsing online more than three hours today, yet I never found any interesting article like yours. It is pretty worth enough for me. Personally, if all site owners and bloggers made good content as you did, the internet will be much more useful than ever before.

  56. Nice post. I be taught one thing tougher on completely different blogs everyday. It’ll always be stimulating to learn content from different writers and practice somewhat something from their store. I’d want to make use of some with the content on my weblog whether you don’t mind. Natually I’ll give you a link on your net blog. Thanks for sharing.

  57. Today, I went to the beach front with my children. I found a sea shell and gave it to my 4 year old daughter and said “You can hear the ocean if you put this to your ear.” She placed the shell to her ear and screamed. There was a hermit crab inside and it pinched her ear. She never wants to go back! LoL I know this is entirely off topic but I had to tell someone!

  58. I must get across my affection for your generosity giving support to those individuals that must have guidance on that situation. Your special dedication to getting the message all over had been amazingly beneficial and has in most cases encouraged folks just like me to reach their dreams. Your personal informative report implies a great deal to me and especially to my colleagues. Thanks a lot; from each one of us.

댓글 남기기