EXIST Function in SQL

Join Discussion: www.techtud.com...
IMPORTANT LINKS:
1) Official Website: www.techtud.com/
2) Virtual GATE: virtualgate.in/...
Both of the above mentioned platforms are COMPLETELY FREE, so feel free to Explore, Learn, Practice & Share!
Our Social Media Links:
Facebook Page: / techtuduniversity
Facebook Group: / virtualgate
Google+ Page: plus.google.co...
Last but not the least, SUBSCRIBE our KZbin channel to stay updated about the regularly uploaded new videos.

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

[@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!!

[@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

[@oscarv6907]

You explained it so well, thank you so much!

[@lorraineliu8422]

Very clear. Thank your for the instruction!

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

[@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

[@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

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

[@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?

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

[@ŁukaszSmykał]

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

[@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

[@VanBhardwaj]

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

[@hechbkee2220]

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

[@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 !

[@shadaflorida4248]

Amazing explanation! Thank you so much!

[@gaydolfhitler6310]

Thank you. excellent tutorial

[@prernasemwal3016]

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

[@pradeepkumar.5172]

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

[@nickyevdokymov5526]

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

[@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)

[@nikolasioakimidis3003]

Thanks mate, that's actually a good vid

[@jackdaniel1579]

Can this whole Exist can be used with IN?

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

[@lokeshphoenix]

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

[@lordmefisto255]

of course ;)

[@superfreiheit1]

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

[@mariasilverdale6444]

Thank you

[@Renegade_rm56]

Really nice handwriting ;)

[@juniorocana7171]

Thanks!

[@MarceloAgostonSchrotlin]

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

[@codestorywithMIK]

very well explained

[@ahmedelshenawy6357]

Thanks..

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

[@nicofrancisco3216]

thank u very helpful

[@muhammadkemalghifarirais4470]

THANNNKS YOU ARE THE BEST!

[@wordsthatcount8077]

thank

[@maheshmenta6078]

thank u

[@azra8366]

Thank you sir, that was helpful :)

[@adaseth]

#ASMR ;)

[@ericzheng4815]

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

[@MiauMichigan]

With 'S'

[@vaibhavmehta3424]

okay

[@p.m8042]

Are you the guy who made coding school series?

[@muhammadsafiullah8428]

No

[@hemantkilanje8865]

I didnt understand

[@wajdihammami7452]

fking genius

[@dude789d]

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

[@ofek5365]

Thank you And thank god for Google Auto Generated Subtitles

[@binaydhakal1731]

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.

[@sureshk1099]

thank you

[@jordan5253]

okay