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 🙂
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.
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.
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?
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!
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.
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?
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
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
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.
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 🙂
same here! 👍 so useful
Very clear and to the point. Thanks a lot.
Finally, someone answered the nagging question in my head, "Why not just use a join?" Thanks Doug!
exists are not for beginners xD
love it
Thx for the explanation. Do you mean exist will dedup and same with IN statement it will dedup too?
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.
Thanks a lot!
Thanks a lot. What does the query return if WHERE EXISTS returns false?
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.
Thank you for the explanation. Very helpful.
Great explanation, thank you
much thanks for your video! great explanation on this topic!
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?
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!
Thank you very much for such great content.
Very good explanation! Thanks!
this was awesome
Thank you for making it understandable.
in subquey, can I select * or any random columns ? does it have to follow any patterns of select in subquery?
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.
@@DatabasebyDoug tks u
Great Video. Thank you for posting.
Thanks for your lesson that's easy to understand.
Very good explanation. Tank you so much!
Thanks, really helpful
Great, thanks man
Very useful and detailed. Thank you!
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?
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
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
because of the use cases these keywords support
Fantastic video and exactly what I needed!
Glad it was helpful!
which software is that? ty
+Chethan Prabhu I'm using Microsoft SQL Server.
Very nicely explained 👍TY
That is very helpful
Very clear!
Glad you think so!
you the MAN!
It cleared my doubts. Very very helpful.
Good video!
can we use where exists. or where not exists with insert query ?
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.
thanks 😃
Very nicely explained
Thank you Sir.
You are most welcome