WEBVTT 00:00.280 --> 00:01.020 Already. 00:01.080 --> 00:08.100 So next up we're talking about a new operator called in N and what it does is it allows us to provide 00:08.130 --> 00:19.020 a set of values to three ten fifty different values and then check if a given column is in that set. 00:19.020 --> 00:22.080 So basically like put that in the easier to understand way. 00:22.290 --> 00:30.540 So we wanted to do this on a select all the books written by Carver Raymond Carver or Lahiri Hillary 00:31.140 --> 00:32.430 or Smith. 00:32.460 --> 00:36.710 In our case Patti Smith So we know how to do that right now. 00:36.880 --> 00:42.730 I mean if you listen to how I just said that with these oars we would say she liked all books written 00:42.730 --> 00:48.140 by Carver or either here or by Smith who look something like this. 00:48.240 --> 00:55.750 It's like title An author last name from books where author last name is equal to Carver or author last 00:55.750 --> 00:59.950 name is equal to the history or the last name is equal to Smith. 00:59.950 --> 01:00.840 So this works. 01:00.940 --> 01:02.730 Come on over to cloud nine. 01:02.740 --> 01:07.080 First thing you'll need to do is switch databases. 01:07.260 --> 01:10.850 I'm using the new testing D-B. 01:10.870 --> 01:15.040 I mean a used bookshop again. 01:15.040 --> 01:23.170 OK so now I'm going to use this select title and author L name from these 3 or from where author is 01:23.170 --> 01:25.690 one of these three if I paste that in. 01:25.870 --> 01:31.480 You can see we get all the books by the Harry Smith and Carver perfect. 01:31.720 --> 01:35.620 However as you probably guess there's a better way to do it in. 01:35.710 --> 01:37.980 Make it much easier and much shorter. 01:38.560 --> 01:40.200 So it looks like this. 01:40.240 --> 01:43.270 So like title an author from the first book or from books. 01:43.270 --> 01:52.180 Same thing where author last name is in and then in parentheses we pass in a comma separated list of 01:52.180 --> 01:53.340 values. 01:53.380 --> 02:00.470 So if author name matches anything in here it will be that the row will be select. 02:00.520 --> 02:03.540 So it's really straightforward very simple to use. 02:03.550 --> 02:09.470 It basically goes from this multiple lines and look how much repetition we have. 02:09.610 --> 02:17.720 Author L name equals or author L name equals author L name equals and condenses it down to this. 02:17.740 --> 02:22.180 So let's try to paste this down here so you can just compare 02:25.170 --> 02:26.000 repasted in. 02:26.170 --> 02:28.220 You can see it does the exact same thing. 02:28.630 --> 02:35.650 And the other good thing is that you know as we add in more conditions or more authors in our case this 02:35.650 --> 02:37.930 continues to get much longer every time. 02:37.930 --> 02:39.880 This is really just a very simple change. 02:39.890 --> 02:44.950 If we also want to add in another author very easy. 02:45.610 --> 02:46.630 So it's pretty useful. 02:46.630 --> 02:49.210 You're not limited just to working with text. 02:49.480 --> 02:52.750 So I'll just duplicate this just to show you we could do. 02:52.750 --> 03:07.920 Select title and release your where release year is in let's say 2017 or 1985 just like that. 03:08.080 --> 03:12.700 And if we pace that it and we get these two books. 03:12.850 --> 03:13.120 All right. 03:13.130 --> 03:20.140 So just to show you it doesn't have to be strings you can work with new numbers as well as you probably 03:20.140 --> 03:21.860 guessed maybe it didn't. 03:21.880 --> 03:22.260 I don't know. 03:22.390 --> 03:29.790 But there's also a knot in and it works just like not between worked compared to between. 03:29.890 --> 03:32.350 It will do the opposite of it. 03:33.250 --> 03:39.250 So let's say this is a really dumb hypothetical but let's say I'm really superstitious and I have a 03:39.250 --> 03:45.670 problem with even numbers I go into the bookstore and I tell the court this even though I promise I'm 03:45.670 --> 03:48.790 not superstitious and I don't have a problem with even numbers. 03:48.790 --> 03:52.720 If anything I really like even numbers if you can. 03:53.080 --> 04:01.090 But I go into the store and I say I want to get all books that were not published an even number of 04:01.090 --> 04:03.620 years 2000 until 2016. 04:04.470 --> 04:08.400 So I want to exclude 2002 to 2004 and so on. 04:09.030 --> 04:17.560 Well I can do this which is kind of a nightmare where I'm basically using logical ends and not equals. 04:18.030 --> 04:25.050 So remember we have to go back a few slides here when we were doing this version here before we talked 04:25.050 --> 04:26.570 to it in I was doing. 04:26.600 --> 04:33.370 Equals and or so author equals cover or author I'll name it here. 04:33.630 --> 04:35.990 But when we negate it and do the opposite. 04:36.150 --> 04:38.800 I need to negate that equals with a not equals. 04:38.820 --> 04:47.130 And I flip that or two in and which when I say it that way I realize it may sound a bit more intimidating 04:47.130 --> 04:53.100 than it is basically logically if we step through this what I'm saying is I want to select all the books 04:53.400 --> 04:59.110 where really steer is not 2000 and it's not 2002 and it's not 2004 and it's not any of these. 04:59.400 --> 05:04.340 So anything other than these values and or wouldn't work here it has to be and. 05:05.010 --> 05:05.310 OK. 05:05.340 --> 05:07.750 But the point is is that this is a mess. 05:07.810 --> 05:10.970 It's noxious It's gigantic. 05:11.030 --> 05:15.300 I do copy it over and I tried running it. 05:15.300 --> 05:18.120 You'll see we get all books from odd number years. 05:21.650 --> 05:27.290 But there's a better way we can use not in which works just like you'd expect. 05:27.290 --> 05:29.860 Rather than saying in we just say not in. 05:29.900 --> 05:33.060 And we provide that list that set of values. 05:33.170 --> 05:34.820 And in this case have just passed them all. 05:34.830 --> 05:39.020 And notice the difference there massive difference. 05:39.260 --> 05:40.280 And if we run this 05:45.970 --> 05:47.370 we get the exact same result. 05:49.010 --> 05:54.360 But much much shorter and much easier to add onto or change. 05:54.380 --> 05:58.320 Now some of you might be thinking well this still seems really silly. 05:58.490 --> 06:00.380 There's a couple of things that we can change. 06:00.380 --> 06:05.510 One is that I only want books released after 2000. 06:05.690 --> 06:12.020 So right now I'm basically saying I don't want any even years in the 2000s but we're still getting even 06:12.020 --> 06:16.030 years 96 is there any others. 06:16.070 --> 06:17.770 I guess it's just 5:51. 06:18.200 --> 06:26.650 So what I could do is say that I only want books released after 2000 and it would just look like this. 06:26.660 --> 06:32.390 So the only reason I'm showing you this is so that you can see how to use logical and in conjunction 06:32.390 --> 06:33.320 with something like not. 06:33.340 --> 06:41.270 And so all that we're doing here is selecting books where the release after the year 2000 including 06:41.270 --> 06:43.450 2000 greater than or equal to two thousand. 06:43.670 --> 06:45.920 So that's going to narrow it down significantly. 06:46.310 --> 06:53.200 And then within that subset we only want ones that were released in years that are not contained here. 06:53.240 --> 06:59.660 So the odd numbers the odd 2000 2001 2003 and so on. 06:59.670 --> 07:05.670 So as far as why you would do this let's say that I have taste in literature and I only want recent 07:05.670 --> 07:07.720 books from odd years. 07:08.130 --> 07:12.760 And let's do an order by. 07:13.550 --> 07:14.240 There we go. 07:14.320 --> 07:17.120 So we get 2001 all the way up to 2017. 07:17.470 --> 07:22.090 All the modern books with odd years but there's still something more. 07:22.720 --> 07:25.770 And it doesn't require us to use in or not in. 07:25.840 --> 07:28.150 Because if you look at this this is pretty repetitive. 07:28.170 --> 07:30.280 Well repetitive is the wrong word. 07:30.490 --> 07:33.120 It's long and there's a pattern here. 07:33.130 --> 07:33.980 Right. 07:34.000 --> 07:37.110 So there has to be a better way to express this. 07:37.420 --> 07:40.860 And what I'm going to show you here has nothing to do with in or not in. 07:40.870 --> 07:46.640 So it's a bit of a tangent but I feel that it's worth talking about if you're familiar with programming. 07:46.930 --> 07:50.690 The solution here is to use an operator called Maggiolo. 07:51.100 --> 07:54.970 And basically this is also known as the remainder operator. 07:55.240 --> 07:58.670 What it will allow us to do is test if something is even. 07:59.140 --> 08:01.420 So how do we know if something is even in general. 08:02.020 --> 08:04.560 Well it's divisible evenly by 2 right. 08:04.570 --> 08:07.570 We can divide 2 into a number and there's no remainder. 08:07.570 --> 08:14.210 That means that it's even so 4 divided by two is two and it's just a whole number. 08:14.270 --> 08:17.530 So it's perfect but 3 or let's say seven divided by two. 08:17.540 --> 08:20.110 It's not a clean even division. 08:20.360 --> 08:22.330 So that means that it's not an even number. 08:22.640 --> 08:24.750 So there's a way to do that. 08:24.810 --> 08:25.790 It looks like this. 08:26.950 --> 08:28.660 So the percent sign is known. 08:28.690 --> 08:31.190 As I said modulo the remainder operator. 08:31.240 --> 08:36.800 So if we just focus on that part this is saying release year divided by 2. 08:37.060 --> 08:39.320 The remainder is not equal to zero. 08:41.110 --> 08:49.140 So if we plug in 2000 right here 2000 divided by two the remainder of that is zero. 08:49.540 --> 08:52.670 But if we plug in 2001 the remainder is not zero. 08:52.690 --> 08:55.250 The remainder is 1 meaning that it's odd. 08:55.720 --> 08:58.580 So again this is above and beyond what you need to know. 08:58.600 --> 09:03.970 But I just figured it would come up in the comments if someone saw this gigantic mess here someone would 09:03.970 --> 09:05.840 probably say there's a better way. 09:06.100 --> 09:07.000 And they're right. 09:07.270 --> 09:11.730 It just happens to not use in or not and which is the whole point of this video. 09:11.740 --> 09:14.290 But I figured it is still worth talking about. 09:14.710 --> 09:19.350 So what we're doing is selecting the same stuff title really square from books. 09:19.420 --> 09:26.110 First thing is the same only selecting books that are released after the year 2000 or Including the 09:26.110 --> 09:37.000 year 2000 and then they also have to be pod years so release year is not released year maade 2 is not 09:37.030 --> 09:42.500 equal to zero two does not divide cleanly and paste that interest. 09:42.510 --> 09:44.470 We have a record. 09:44.470 --> 09:50.200 Run it and you can see now I only get the same exact results right. 09:50.520 --> 09:57.140 Let me order it so that we can compare. 09:57.490 --> 09:58.200 No. 09:58.460 --> 10:01.710 Type of OK. 10:02.210 --> 10:08.930 So we get the exact same results that we got earlier but we didn't have to write all these years out 10:08.930 --> 10:09.800 explicitly. 10:09.800 --> 10:16.280 And the other advantage is this solution works when we're in 2018 and 2020 and whatever other even numbers 10:16.280 --> 10:21.720 we have as long as they are even and greater than 2000 it will be filtered up. 10:21.770 --> 10:26.990 So this is a in my opinion a better solution it grows and it expands more. 10:26.990 --> 10:30.130 The other thing there is a bit of a performance difference. 10:30.130 --> 10:39.670 It's not major but when we look at this basically what if we're pretending less personify or anthropomorphized 10:39.920 --> 10:48.810 might as well and it's taking a year like 2013 when we have not in it's going to try. 10:48.980 --> 10:51.630 Is 2013 equal to 2000. 10:51.680 --> 10:52.300 No. 10:52.310 --> 10:53.620 Is it equal to 2002. 10:53.810 --> 10:56.630 No it's well 2004 and going to keep going down. 10:56.720 --> 11:02.150 It has to try every option there's no way to know unless it actually matches it or tries to match it 11:02.150 --> 11:04.820 against every year we have in this set. 11:04.850 --> 11:10.880 So that could potentially take a lot more time than doing it a single mathematical operation like we 11:10.880 --> 11:11.990 have here. 11:12.650 --> 11:18.140 We're just checking is the release year model 2 not equal to zero. 11:18.440 --> 11:22.820 And it's really not a big difference but it's just something that's nice to be aware of. 11:23.060 --> 11:23.410 OK. 11:23.460 --> 11:25.430 So big tangent here. 11:25.460 --> 11:28.610 This video was really about in not in. 11:28.810 --> 11:32.720 So just to go back to that were very useful. 11:32.870 --> 11:39.500 Allow us to pass in a set of numbers or strings or values dates or whatever it is which then are rows 11:39.500 --> 11:45.020 are checked against to see if they either contained or not contained depending on if we're using in 11:45.020 --> 11:46.210 or not in. 11:46.340 --> 11:46.790 All right.