The SQL EXISTS clause

[@vyvianspipes]

Doug, you have helped me tremendously! When the part about NOT EXISTS was explained, this in itself shed light onto the exact problem I needed to mitigate. Thank you 🙂

[@hadibq]

same here! 👍 so useful

[@kc123456789101112]

Finally, someone answered the nagging question in my head, "Why not just use a join?" Thanks Doug!

[@adreamer9999]

Thank you for the explanation. Very helpful.

[@babybob8823]

Very clear and to the point. Thanks a lot.

[@bagindamirza]

much thanks for your video! great explanation on this topic!

[@gbting1988]

Thx for the explanation. Do you mean exist will dedup and same with IN statement it will dedup too?

[@DatabasebyDoug]

That's an interesting way to look at it. Yes! EXISTS will avoid looking at duplicates once it finds one that matches. Similarly with IN. But, I'm not sure whether it *efficiently* finds the first match.

[@hadibq]

Very nicely explained 👍TY

[@voodoochili12]

Great explanation, thank you

[@z40140110]

Thank you very much for such great content.

[@MethodOverRide]

Fantastic video and exactly what I needed!

[@DatabasebyDoug]

Glad it was helpful!

[@r0cketRacoon]

in subquey, can I select * or any random columns ? does it have to follow any patterns of select in subquery?

[@DatabasebyDoug]

Technically, you can use any column - EXISTS does not check any column values, only for the existence/nonexistence of a row. For code clarity, * is good because it is succinct and doesn't lead a reader of your code to think you have some other intention.

[@r0cketRacoon]

@@DatabasebyDoug tks u

[@bruville]

Very good explanation! Thanks!

[@aspanon1560]

Thanks a lot. What does the query return if WHERE EXISTS returns false?

[@DatabasebyDoug]

Hi and thanks for the question. As with any WHERE clause, if the WHERE clause evaluates to false for a record, the record is not shown. So if WHERE EXISTS returns false for a record, that record is not shown. If WHERE EXISTS returns false for *every* record in the outer query, then there will be no records in the result.

[@debayan89]

It cleared my doubts. Very very helpful.

[@hieptrinh2308]

Thank you for making it understandable.

[@Sonny0276]

Great Video. Thank you for posting.

[@VincenzoCorvaglia]

Very good explanation. Tank you so much!

[@ИванКузнецов-ж3к]

Thanks for your lesson that's easy to understand.

[@yeoshuabenzaken7736]

in the first example (select 17) the exists is acting like a Boolean since the subquery is true so it brings everything in the outer query but in the 2nd example (the correlated query) exists is acting like "in" . how is that?

[@DatabasebyDoug]

Sorry for the delayed response. You are right EXISTS is an operator that returns a boolean value. In the first "trivial" example, SELECT 17 always returns exactly one record, so EXISTS (SELECT 17) is always True. In the second example (correlated subquery), the subquery's results change based on a record in the outer query. So EXISTS() returns true or false, based on a record in the outer query, i.e., it is *correlated* with the outer query. You can phrase the same intended result using IN. In English, you could say "Categories where a Product exists in that category" or "Categories that are in a Category list drawn from the Products table". Both end up with the same result. I hope that helps!

[@chethanprabhu4475]

which software is that? ty

[@DatabasebyDoug]

+Chethan Prabhu I'm using Microsoft SQL Server.

[@sergeykurk]

i don't understand. in the first example we get ALL CategoryNames from Categories if there is at least one row in subquery ? or only CategoryNames that have products?

[@ntuninerlo]

in the first statement the subquery is true, so the query returns all records. in the second, because of the correlated subquery, only individual records correlated with each true instance are returned. at least thats what i believe to be true

[@dfence1985]

Very useful and detailed. Thank you!

[@VeronicaAngryPolak]

this was awesome

[@laurafosci]

May I ask you why Joins are more popular than the Exists clause? At least when I go to interviews or I speak to people they always ask me about Joins but never about the Exists clause

[@vaibhavkumar38]

because of the use cases these keywords support

[@Tgarth99]

Good video!

[@Tgarth99]

Very clear!

[@DatabasebyDoug]

Glad you think so!

[@christianrodier3381]

That is very helpful

[@padminimurthy7160]

Very nicely explained

[@erlendstlie4431]

Thanks, really helpful

[@abdulsami1982]

can we use where exists. or where not exists with insert query ?

[@DatabasebyDoug]

Hi Abdul. Sounds like you want to insert a record only if it doesn't exist? You can use WHERE NOT EXISTS in the SELECT that creates the records to be inserted, and you can reference the same table you are inserting into in your SELECT. You might also take a look at the MERGE statement - it might do everything you want.

[@denyskulpa6241]

you the MAN!

[@malekimilad4245]

Great, thanks man

[@SuperAaronsun]

love it

[@semikolon4229]

Thank you Sir.

[@DatabasebyDoug]

You are most welcome

[@oksanaveretiuk1653]

Thanks a lot!

[@seanangeluasmolo1471]

exists are not for beginners xD

[@Sinha.ritesh]

thanks 😃