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 🙂
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.
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.
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
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.
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.
Thank you for the explanation. Very helpful.
Finally, someone answered the nagging question in my head, "Why not just use a join?" Thanks Doug!
Great explanation, thank you
much thanks for your video! great explanation on this topic!
Thank you very much for such great content.
Fantastic video and exactly what I needed!
Glad it was helpful!
Very good explanation! Thanks!
Thank you for making it understandable.
Great Video. Thank you for posting.
Very useful and detailed. Thank you!
Thanks for your lesson that's easy to understand.
Very good explanation. Tank you so much!
Very nicely explained 👍TY
It cleared my doubts. Very very helpful.
this was awesome
Very clear!
Glad you think so!
Thanks, really helpful
That is very helpful
Great, thanks man
Very nicely explained
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!
Good video!
love it
you the MAN!
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
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.
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
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 Sir.
You are most welcome
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
thanks 😃
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.
which software is that? ty
+Chethan Prabhu I'm using Microsoft SQL Server.
exists are not for beginners xD