Understanding ALL ANY or SOME in SQL || Lesson 89 || DBMS || Learning Monkey ||

Here we will try to discuss Understanding ALL, ANY or SOME in SQL.
Understanding ALL ANY or SOME in SQL
Definitions of ALL, ANY or SOME: ALL, ANY or SOME are keywords. These keywords are used with WHERE or HAVING Clause.
ALL, ANY or SOME operates on the subqueries that return multiple values. ALL returns true if all subquery values meet the condition. ANY or SOME returns true if any of the subquery values meet the condition.
To understand this let's consider the employee table of the company database
Understanding ALL Keyword:
Now consider the query with the use of ALL " SELECT fname FROM employee WHERE salary ALL (20000, 25000, 30000); ".
What do the above query states?
The query is going to find the fname of all the employees whose salary is greater than ALL these values 20000, 25000, and 30000.
How this query works is it takes the salary value in each and every row and is compared with all the values given (20000, 25000, 30000).
If the salary in the row is greater than ALL the values then the fnames will be displayed.
Understanding ANY or SOME Keyword:
Consider the same query used with ANY " SELECT fname FROM employee WHERE salary ANY (20000, 25000, 30000); ".
What do the above query states?
The query is going to find the fname of all the employees whose salary is greater than ANY of these values 20000, 25000, and 30000.
How this query works is it takes the salary value in each and every row and is compared with all the values given (20000, 25000, 30000).
If the salary in the row is greater than ANY the values then the fnames will be displayed.
In place of we can use SOME also.
Example Query on ALL, ANY or SOME:
Find the fnames and lnames of all the employees whose salary is greater than all the employees in department number 5.
In order to execute this query, we have to find the salaries of all the employees in department number 5. Then those values are to be compared with every employee salary.
The answer to the query is " SELECT fname, lname FROM employee WHERE salary ALL (SELECT salary FROM employee WHERE dno = 5); ".
The output of the inner query is the salaries of the employees who are working in department number 5 (30000, 40000, 38000, 25000).
Now every employee's salary is compared with ALL the employee's salary of the department number 5.
If the value of the salary is true for all the values then those fname and lname are considered.
Link for our website: learningmonkey.in
Follow us on Facebook @ / learningmonkey
Follow us on Instagram @ / learningmonkey1
Follow us on Twitter @ / _learningmonkey
Mail us @ [e-mail]

[@adityakhedekar9669]

best video, searched everywhere for understanding the concept of all and any but no-one was better than you. Understood 100%

[@LearningMonkey]

Thanks for your wonderful words. It's good to hear that.
Have a great learning.

[@katchie242]

Just perfect!!! Exactly what I have been looking for. Thanks a lot

[@arvindabd1901]

Very good explanation! Thank you !

[@emmanuelramos5158]

Great video, loved your series ! Thanks for sharing !!!

[@Victoria-dh9jp]

God bless you, you are a life saviour. Good and clear explication.
You are a good teacher sir.
Continue teaching 👌

[@yuhmin5365]

Great example and Lecture :) Thank you so much!

[@Gripex]

Great work, thank you !

[@swathikota]

Nice explanation. Searched many training materials to understand ALL and ANY Operators concept, but found this video to have exactly the right amount of information to understand the concept. Great work.

[@LearningMonkey]

Thank you have a great learning in CSE

[@nknareshkumar]

Beautiful explanation, thanks for the help mate.

[@dhilipmaharish]

Nice video you have saved my time in understanding this concept

[@ruwanrathnayaka9963]

Dear Sir, You are the best teacher I have ever met. Thank you so much for solving a problem that has been a big problem for me. love from Sri Lanka

[@LearningMonkey]

Welcome good to hear that

[@uwizeyejackson5836]

Thank you very much.
Tomorrow I have a class presentation on these particular topics

[@prathamgenius]

Thanks a lot..... U cleared all of my doubts.... Thank u

[@LearningMonkey]

Thanks for your feedback.

[@AbdifitahAbdulkadir]

Thanks you, I have searched and watched more videos but this one is completely different and I understand 100% thakns u for ur good expalination and simple example elaboration thakns yu

[@LearningMonkey]

Welcome good to hear that

[@AbdifitahAbdulkadir]

@@LearningMonkey is there any courses you buy like anad javafX

[@tarunpatel1457]

Great sir ..you cleared my doubt🔥🔥

[@LearningMonkey]

Have a great learning

[@redshift3639]

explanation at 8:58 explains this really well! the three ANDs!

[@prasispoudel7353]

Very good explanation.

[@osamanawas1597]

well explained !

[@bestchoices7694]

Great work man!!!

[@LearningMonkey]

Thank you

[@tamilkumarang]

really nice explaination bro

[@pratusaria5113]

it is a good video. Thanks!

[@dp921]

Thanks for the informative video

[@shaiknafeesaanjum5223]

Woooaahhh....best video ❤

[@LearningMonkey]

Have a great learning in CSE

[@SB_Roy_Vlogs]

Nice video...

[@eliy5550]

AWESOME!!

[@vinspatel3944]

Thanks for your hard work

[@LearningMonkey]

Hi Vins Patel,
Thanks for your feedback.

[@nitinsharma4121]

Good 👍

[@mukundacs7154]

Don't keep your channel name anywhere in the below, possible please keep upside anywhere,

[@lakshmann9825]

awsm

[@rutujamane1924]

Helpfull

[@withafni135]

Thank you!

[@tejasporwal2321]

great work

[@LearningMonkey]

Thank You

[@Venus_on_fire]

Exellent explanation

[@sarika3778]

Thank u anna

[@kashish9285]

Hi Sir, very well explained :) Thank you very much! although i have a (silly) question - if ANY and SOME behave in identical manner, why bother having an extra operator called 'SOME'? should be just ALL or ANY. Hope that makes sense :)

[@satyadharkumarchintagunta3793]

Sir, thank You soo much!

[@LearningMonkey]

Have a great learning in CSE

[@KumarPrince21]

Sir what will be the output if we apply >= All operator instead of only >All ?

[@pratyushverma6959]

It will take all those values which would be greater

[@shreeya4638]

Very nice video apreciateable

[@LearningMonkey]

Thank you

[@rocketleague2136]

thanks

[@NikhilKumarAngadi-sp6is]

Nice explanation

[@LearningMonkey]

Thank you

[@ajinzrathod]

3:08 Kya sahi time pr facebook wala ad dala hai :)

[@GameAndCartoonLover]

Good One !!

[@supileshsukumaran2469]

Hi,
SELECT fname, lastname
FROM [dbo].[EMPLOYEE] AS E
WHERE E.SALARY = ALL (SELECT EM.SALARY FROM EMPLOYEE AS EM WHERE EM.DNAME =5 )
why this returns no data.?

[@divyasingh5612]

Thank you sir

[@LearningMonkey]

Welcome, thanks for the feedback.

[@higa4541]

Thank you so much sir ❤️🥺

[@LearningMonkey]

Most welcome 😊

[@bebeniang1752]

Thank you

[@NishantKadam1201]

What's the use if you put your channel logo right over where the important point is!?

[@echoecho249]

good job sir

[@LearningMonkey]

Thankyou

[@ginnimalik7625]

It was written that in dept no. 5 so the values of dept no. 5 would also return

[@ginnimalik7625]

Pls answer

[@arifulislam1155]

tnx💕💕💕💕

[@gowthame2001]

😘🥰

[@spacejam75]

This was looking great until the facebook advertisement banner covered the query, making it impossible to follow. So a really good video until about the 3:05 mark, then essentially useless.

[@jayasudharajendran5209]

Super

[@LearningMonkey]

Thank You

[@shiamysasa986]

Okay, But James and Jennifer do not belong to Dno 5. Could you pls clarify the doubt?

[@LearningMonkey]

The outer query check the entire table.

[@munusamy9347]

Hi , I have a doubt james and jeniffer not belongs to dno 5

[@Kid-vx9uh]

wtf not bad

[@ashirkhan445]

not helpful, your facebook ad blocked the query, absurd

[@vaishnavis9473]

Thank you