EXIST Function in SQL

[@lowkeygo]

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.

[@mathemagic516]

Thank you so much!

[@brontesaurus1]

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.

[@luke6350]

same with me. glad i found this video

[@captoori]

Same here. Others teach like it was obvious

[@KatRollo]

Difference of an effective teacher :)

[@trollanddrump5326]

How can you forget SEX? Unbelievable!

[@sujaysshenoy247]

super clear explaination of the exists concept, i was struggling to understand this concept Thank you!!

[@oscarv6907]

You explained it so well, thank you so much!

[@lorraineliu8422]

Very clear. Thank your for the instruction!

[@Avict-18]

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

[@tanishktripathi8773]

Doubts cleared at a time immense need ♥️

[@stephenokon3227]

Thank you so much for this video. You demystified this concept in-toto! I appreciate you.

[@angelthedecentgirl7181]

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.

[@ŁukaszSmykał]

obvious but super helpful thanks to You I bypass my problem in BigQuery :) thx:)

[@amirtaghavy7647]

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'); ??

[@hechbkee2220]

Is this an alternative?

[@rss1322]

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.

[@hechbkee2220]

Thank you very much for your lesson..Looks easy but I do not know why I am finding it difficult

[@gaborszepes8107]

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.

[@Edredo]

+Gábor Szepes You are absolutely correct. The video should be improved ASAP

[@r0cketRacoon]

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?

[@sandeepagri2218]

Are you writing with mouse...!!! Coz your onscreen writing is so clear and flawless

[@shadaflorida4248]

Amazing explanation! Thank you so much!

[@sthk1998]

What if I want Professers with at least 2 female students? Do I do a groupby prof, count >=2 in the internal query?

[@siddharthasharma9316]

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.

[@sidharthmandal9957]

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 !

[@vasanthavasu8696]

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

[@zhijieshi692]

will there be any value returned if only the inner query is executed? (ignore the exist, outer query)

[@unnikrishnanvannadil]

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?

[@pradeepkumar.5172]

thank u so much.........i understood this concept now.

[@nikolasioakimidis3003]

Thanks mate, that's actually a good vid

[@VanBhardwaj]

Are the results produced by replacing "exists" with "not exists" in a query always mutually exclusive?

[@nickyevdokymov5526]

All the best coding tutorials on KZbin are made with Indian accent, lol. Thank from Ukraine, btw

[@Renegade_rm56]

Really nice handwriting ;)

[@SamChan-p4j]

what if the condition changes to "for at least 2 female students" ?

[@MarceloAgostonSchrotlin]

THANNKSS A LOOOOOOOOOOT!!! YOU SAVE MY LIFE DUDE!!

[@prernasemwal3016]

Sir why arent we using join in the inner query as two tablesare being joined

[@muhammadkemalghifarirais4470]

THANNNKS YOU ARE THE BEST!

[@caitancorreia8379]

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?

[@sukantasen1]

It will work s well as EID and Advisor are different column name but having same datatype and you will get the same result

[@pallavi4501]

Sukanta Sen ya correct

[@lordmefisto255]

U should use Professor.Eid=Student.Advisor because ( P is for Professor table and S is for student)

[@superfreiheit1]

Exists: IF Subquery is empty, then not execute outer-query

[@juniorocana7171]

Thanks!

[@sureshk1099]

thank you

[@jackdaniel1579]

Can this whole Exist can be used with IN?

[@ahmedelshenawy6357]

Thanks..

[@codestorywithMIK]

very well explained

[@nicofrancisco3216]

thank u very helpful

[@lokeshphoenix]

can we write select name , AVG(sal) from employee group by department having sal>AVG(sal) ??

[@lordmefisto255]

of course ;)

[@Ebiru2387]

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.

[@archidar1]

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.

[@praveenpalina6591]

Hi sir, Which one fast exists or in?why?

[@yashmishra12]

"In" is faster because the outer query is run once while in EXISTS, the outer query is run for each inner query element.

[@maheshmenta6078]

thank u

[@vohuuthang7217]

great

[@azra8366]

Thank you sir, that was helpful :)

[@p.m8042]

Are you the guy who made coding school series?

[@muhammadsafiullah8428]

No

[@wordsthatcount8077]

thank

[@adaseth]

#ASMR ;)

[@ericzheng4815]

It's EXIST or EXISTS? I'm confused rn

[@MiauMichigan]

With 'S'

[@jordan5253]

okay

[@wajdihammami7452]

fking genius

[@hemantkilanje8865]

I didnt understand

[@dude789d]

Atleast do your homework first...its existS not Exist...plural...

[@ofek5365]

Thank you And thank god for Google Auto Generated Subtitles

[@ProcessOverProfitHQ]

Sexuality and Gender are not same

[@tobicipher9300]

Gender and sex are the same, however, what we call sexuality these days are nothing more than fetishes people identify with.

[@mariasilverdale6444]

Thank you

[@vaibhavmehta3424]

okay