The SQL EXISTS clause

Поділитися
Вставка
  • Опубліковано 13 гру 2024
  • How to use the EXISTS clause in SQL. For beginners.

КОМЕНТАРІ • 52

  • @vyvianspipes
    @vyvianspipes 2 роки тому +2

    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
      @hadibq 2 роки тому +1

      same here! 👍 so useful

  • @babybob8823
    @babybob8823 6 років тому +4

    Very clear and to the point. Thanks a lot.

  • @adreamer9999
    @adreamer9999 6 років тому +6

    Thank you for the explanation. Very helpful.

  • @kc123456789101112
    @kc123456789101112 Рік тому

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

  • @voodoochili12
    @voodoochili12 7 років тому +6

    Great explanation, thank you

  • @bagindamirza
    @bagindamirza Рік тому +1

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

  • @MethodOverRide
    @MethodOverRide 4 роки тому +1

    Fantastic video and exactly what I needed!

  • @z40140110
    @z40140110 3 роки тому +1

    Thank you very much for such great content.

  • @bruville
    @bruville 5 років тому +2

    Very good explanation! Thanks!

  • @hadibq
    @hadibq 2 роки тому

    Very nicely explained 👍TY

  • @Sonny0276
    @Sonny0276 7 років тому +1

    Great Video. Thank you for posting.

  • @VincenzoCorvaglia
    @VincenzoCorvaglia 2 роки тому

    Very good explanation. Tank you so much!

  • @hieptrinh2308
    @hieptrinh2308 4 роки тому +1

    Thank you for making it understandable.

  • @dfence1985
    @dfence1985 9 років тому +4

    Very useful and detailed. Thank you!

  • @ИванКузнецов-ж3к
    @ИванКузнецов-ж3к 7 років тому +1

    Thanks for your lesson that's easy to understand.

  • @debayan89
    @debayan89 8 років тому +2

    It cleared my doubts. Very very helpful.

  • @VeronicaAngryPolak
    @VeronicaAngryPolak Рік тому

    this was awesome

  • @Tgarth99
    @Tgarth99 4 роки тому +1

    Very clear!

  • @erlendstlie4431
    @erlendstlie4431 8 років тому +1

    Thanks, really helpful

  • @r0cketRacoon
    @r0cketRacoon Рік тому

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

    • @DatabasebyDoug
      @DatabasebyDoug  Рік тому +1

      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
      @r0cketRacoon Рік тому

      @@DatabasebyDoug tks u

  • @malekimilad4245
    @malekimilad4245 6 років тому +1

    Great, thanks man

  • @padminimurthy7160
    @padminimurthy7160 8 років тому +2

    Very nicely explained

  • @gbting1988
    @gbting1988 5 років тому +1

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

    • @DatabasebyDoug
      @DatabasebyDoug  6 місяців тому

      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.

  • @denyskulpa6241
    @denyskulpa6241 4 роки тому

    you the MAN!

  • @christianrodier3381
    @christianrodier3381 2 роки тому

    That is very helpful

  • @Tgarth99
    @Tgarth99 5 років тому

    Good video!

  • @SuperAaronsun
    @SuperAaronsun 3 роки тому

    love it

  • @aspanon1560
    @aspanon1560 5 років тому

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

    • @DatabasebyDoug
      @DatabasebyDoug  5 років тому

      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.

  • @yeoshuabenzaken7736
    @yeoshuabenzaken7736 9 місяців тому

    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
      @DatabasebyDoug  6 місяців тому

      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!

  • @sergeykurk
    @sergeykurk 7 років тому

    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
      @ntuninerlo 7 років тому

      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

  • @chethanprabhu4475
    @chethanprabhu4475 8 років тому +1

    which software is that? ty

    • @DatabasebyDoug
      @DatabasebyDoug  8 років тому

      +Chethan Prabhu I'm using Microsoft SQL Server.

  • @laurafosci
    @laurafosci 5 років тому

    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
      @vaibhavkumar38 4 роки тому

      because of the use cases these keywords support

  • @oksanaveretiuk1653
    @oksanaveretiuk1653 9 років тому +2

    Thanks a lot!

  • @semikolon4229
    @semikolon4229 4 роки тому

    Thank you Sir.

  • @abdulsami1982
    @abdulsami1982 7 років тому

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

    • @DatabasebyDoug
      @DatabasebyDoug  6 років тому

      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.

  • @Sinha.ritesh
    @Sinha.ritesh 7 років тому

    thanks 😃

  • @seanangeluasmolo1471
    @seanangeluasmolo1471 2 роки тому +2

    exists are not for beginners xD