Bummer! This is just a preview. You need to be signed in with a Basic account to view the entire video.

Preview

Start a free Basic trial
to watch this video

Sign up for Treehouse

Elective Teachers

2:53 with Ben Deitch

Which teachers teach elective courses?

  • 0:00

    Perfect, it's always nice to show someone they're appreciated.

  • 0:04

    Lastly, the school is looking for ways to get students to take more electives.

  • 0:07

    And they'd like to start by getting the opinions of the elective teachers.

  • 0:11

    Could you generate a list of the elective teachers?

  • 0:14

    This query is going to be a lot like the previous two queries.

  • 0:17

    We're going to need to start with the TEACHERS or SUBJECTS table and

  • 0:20

    use the CLASSES table to join to the other one.

  • 0:22

    So let's start by selecting DISTINCT.

  • 0:27

    And it's TEACHERS.ID and FIRST_NAME and LAST_NAME.

  • 0:34

    FROM TEACHERS JOIN CLASSES ON TEACHERS.ID

  • 0:41

    = CLASSES.TEACHER_ID.

  • 0:45

    And JOIN to SUBJECTS on SUBJECTS.ID

  • 0:51

    = CLASSES.SUBJECT_ID.

  • 0:56

    Then from here we only wanna select the teachers who are teaching the electives.

  • 1:01

    So if we look at the SUBJECTS table,

  • 1:03

    the electives are represented as classes that don't have a grade level.

  • 1:08

    So, back in the query, let's add WHERE GRADE = NULL and let's run it.

  • 1:18

    And we get nothing.

  • 1:19

    And to see what it is, let's take a second and talk about null.

  • 1:23

    In SQL, nothing can ever equal null, ever.

  • 1:27

    You can't do it.

  • 1:27

    You cannot equal null.

  • 1:29

    Think of null as more of an I don't know.

  • 1:33

    If we asked two people what they had for breakfast this morning and

  • 1:36

    they both said I don't know, we can't just conclude that they had the same thing.

  • 1:41

    In SQL, when you compare two things, It could be true or

  • 1:45

    false or it can also be unknown.

  • 1:49

    Let's try a few examples.

  • 1:51

    I'm going to cut this out, but you should probably leave it.

  • 1:55

    So, let's SELECT 1 = 1..

  • 1:58

    And if we run this, we get 1, which means true.

  • 2:02

    If we SELECTt 1 = 0, we get 0, which means false.

  • 2:07

    Then if we check if 1 = NULL, we get NULL because we don't know if 1 can equal NULL,

  • 2:13

    cuz we don't know what NULL is.

  • 2:16

    And even worse, if we check NULL = NULL, we get null because we just don`t know.

  • 2:23

    So when you`re checking with null, you want to use IS.

  • 2:28

    And there we get 1.

  • 2:29

    Meaning null is null.

  • 2:31

    Cool.

  • 2:32

    So, pasting back in the query, Instead of saying

  • 2:37

    WHERE GRADE = NULL we wanna say WHERE GRADE IS NULL.

  • 2:43

    And if we run this, we get our list of electives teachers.

  • 2:48

    Great work on your first day.

  • 2:50

    It looks like you're really getting the hang of this.

  • 2:52

    See you tomorrow.

You need to sign up for Treehouse in order to download course files.

Sign up