I struggled because I did not understand the logic of how outer query recognizes the conditions in the where clause of the subquery. So this has helped me understanding the concept: 1. First look at the outer query and think it as "Each row is about to be returned!" 2. But in order for a row to be returned, "where exists" clause must be true. 3. Now we look at the where clause inside the subquery. Here, usually we make outerquery.id = subquery.id because "we are determining whether the current row should be returned or not". And this current row has its own id. Furthermore, usually, you put 1 more condition such as subquery.quantity > 2. 4. Now, the subquery will look for the id that is about to be returned in the outer query inside the subquery, and see if there's any row that meets the second condition (quantity >2). 5. If there's any row that matches the condition inside the subquery, outer query can return the current row. Otherwise, it skips and the same process repeats for the next row.
Thank you! I have struggled with this for a while. No one (teacher nor book) ever emphasized that the outer query would or would not run based on the subquery.
What is the advantage of using EXIST func in your example over setting up the query like: SELECT * FROM professor WHERE eid IN (SELECT adviser FROM student WHERE sex = 'F'); ??
IN returns an array. EXIST returns a simple TRUE or FALSE. So EXIST is more efficient in this particular example. But you cannot EXIST where you need the entire array of employee ids as an output.
Use of exists function is cleared. But we can write sub queries instead of this. That will be easy to write. Then what is the purpose of using exists. Can anybody clarify on this
Dear Sir, I guess "EXIST" is actually "EXISTS"... I am a beginner in this field this is the reason why I am asking. Thank You for your answer in advance. Best regards, G.Sz.
Thank you very much, I had been struggling with this for quite a while now I guess you are the one that has explained it the best> I was wondering how the outer query has recognized the condition of the inner query but now i get it
he uses at least one for the EXISTS function however if such a scenario happens then we use groupby clause along with the Having Clause for conditions.
atleast 1 means:- 1 or more than 1 (i guess we need to use this in inner query using group by and having ) The video explained answer is:- find out the name of professor who has female student ! correct me if im wrong !
A question sir, What would happen if instead of P.Eid =S. Advisor we just say Eid=Advisor without renaming as P and as S. What would happen then? Is that wrong?
I have a question. A colleague wrote averysimilarly structured query, except in the subquery instead of writing "Select RollNo From....", they wrote "Select 1 From". Could one speculate this was done for efficiency? Or Could you provide your thoughts why this might be? I commonly see "Select 1" and "EXISTS" together quite frequently and am curious why it is used.
You only need the table after the EXISTS clause to exist (i.e. contain any value). The value itself does not matter. Hence, you can use SELECT 1 ... to save time, or show that you do not care about the value.
I struggled because I did not understand the logic of how outer query recognizes the conditions in the where clause of the subquery. So this has helped me understanding the concept:
1. First look at the outer query and think it as "Each row is about to be returned!"
2. But in order for a row to be returned, "where exists" clause must be true.
3. Now we look at the where clause inside the subquery. Here, usually we make outerquery.id = subquery.id because "we are determining whether the current row should be returned or not". And this current row has its own id. Furthermore, usually, you put 1 more condition such as subquery.quantity > 2.
4. Now, the subquery will look for the id that is about to be returned in the outer query inside the subquery, and see if there's any row that meets the second condition (quantity >2).
5. If there's any row that matches the condition inside the subquery, outer query can return the current row. Otherwise, it skips and the same process repeats for the next row.
Thank you so much!
Thank you! I have struggled with this for a while. No one (teacher nor book) ever emphasized that the outer query would or would not run based on the subquery.
same with me. glad i found this video
Same here. Others teach like it was obvious
Difference of an effective teacher :)
How can you forget SEX? Unbelievable!
What is the advantage of using EXIST func in your example over setting up the query like:
SELECT * FROM professor
WHERE eid IN (SELECT adviser FROM student WHERE sex = 'F');
??
Is this an alternative?
IN returns an array. EXIST returns a simple TRUE or FALSE. So EXIST is more efficient in this particular example. But you cannot EXIST where you need the entire array of employee ids as an output.
Use of exists function is cleared. But we can write sub queries instead of this. That will be easy to write. Then what is the purpose of using exists. Can anybody clarify on this
Are you writing with mouse...!!! Coz your onscreen writing is so clear and flawless
You explained it so well, thank you so much!
How is this different from using IN ? In the above example wouldn't IN be a better option and take care of the same logic?
is it compulsory to select ROLLNO in subquey, can I select * or any random columns ? do it have to follow any patterns of select in subquery?
Very clear. Thank your for the instruction!
Are the results produced by replacing "exists" with "not exists" in a query always mutually exclusive?
super clear explaination of the exists concept,
i was struggling to understand this concept
Thank you!!
Dear Sir,
I guess "EXIST" is actually "EXISTS"...
I am a beginner in this field this is the reason why I am asking.
Thank You for your answer in advance.
Best regards,
G.Sz.
+Gábor Szepes
You are absolutely correct. The video should be improved ASAP
Thank you very much,
I had been struggling with this for quite a while now
I guess you are the one that has explained it the best>
I was wondering how the outer query has recognized the condition of the inner query but now i get it
will there be any value returned if only the inner query is executed? (ignore the exist, outer query)
What if I want
Professers with at least 2 female students? Do I do a
groupby prof, count >=2
in the internal query?
he uses at least one for the EXISTS function however if such a scenario happens then we use groupby clause along with the Having Clause for conditions.
atleast 1 means:- 1 or more than 1
(i guess we need to use this in inner query using group by and having )
The video explained answer is:- find out the name of professor who has female student !
correct me if im wrong !
Doubts cleared at a time immense need ♥️
Sir why arent we using join in the inner query as two tablesare being joined
Thank you very much for your lesson..Looks easy but I do not know why I am finding it difficult
what if the condition changes to "for at least 2 female students" ?
Can this whole Exist can be used with IN?
obvious but super helpful
thanks to You I bypass my problem in BigQuery :)
thx:)
A question sir,
What would happen if instead of P.Eid =S. Advisor we just say Eid=Advisor without renaming as P and as S.
What would happen then? Is that wrong?
It will work s well as EID and Advisor are different column name but having same datatype and you will get the same result
Sukanta Sen ya correct
U should use Professor.Eid=Student.Advisor because ( P is for Professor table and S is for student)
Thanks mate, that's actually a good vid
Thank you so much for this video. You demystified this concept in-toto! I appreciate you.
thank u so much.........i understood this concept now.
Amazing explanation! Thank you so much!
All the best coding tutorials on RUclips are made with Indian accent, lol. Thank from Ukraine, btw
Hi sir,
Which one fast exists or in?why?
"In" is faster because the outer query is run once while in EXISTS, the outer query is run for each inner query element.
can we write select name , AVG(sal)
from employee
group by department
having sal>AVG(sal) ??
of course ;)
Thank You so much sir, i have been searching for a good video for the long time, and i finally discovered this. It is actually6 fruitful for me.
Exists: IF Subquery is empty, then not execute outer-query
I have a question. A colleague wrote averysimilarly structured query, except in the subquery instead of writing "Select RollNo From....", they wrote "Select 1 From".
Could one speculate this was done for efficiency? Or Could you provide your thoughts why this might be? I commonly see "Select 1" and "EXISTS" together quite frequently and am curious why it is used.
You only need the table after the EXISTS clause to exist (i.e. contain any value).
The value itself does not matter.
Hence, you can use SELECT 1 ... to save time, or show that you do not care about the value.
Really nice handwriting ;)
Are you the guy who made coding school series?
No
THANNNKS YOU ARE THE BEST!
THANNKSS A LOOOOOOOOOOT!!! YOU SAVE MY LIFE DUDE!!
Thanks!
thank you
thank u very helpful
Thanks..
It's EXIST or EXISTS? I'm confused rn
With 'S'
I didnt understand
thank u
very well explained
thank
great
Thank you sir, that was helpful :)
#ASMR ;)
okay
fking genius
Atleast do your homework first...its existS not Exist...plural...
Sexuality and Gender are not same
Gender and sex are the same, however, what we call sexuality these days are nothing more than fetishes people identify with.
Thank you
And thank god for Google Auto Generated Subtitles
Thank you
okay