WEBVTT 1 00:00:00.000 --> 00:00:01.270 Hi guys, I hope you're doing well. 2 00:00:01.270 --> 00:00:04.800 In this video, we're going to learn about the WHERE clause 3 00:00:04.800 --> 00:00:07.470 in SQL that we can use to filter results 4 00:00:07.470 --> 00:00:09.453 from a SELECT statement. 5 00:00:10.610 --> 00:00:15.230 So the WHERE clause goes after SELECT * FROM users 6 00:00:15.230 --> 00:00:17.480 or SELECT your columns from users. 7 00:00:17.480 --> 00:00:20.350 So you can do something like SELECT * FROM users, 8 00:00:20.350 --> 00:00:25.350 where first name is John, or where age is greater than 18, 9 00:00:25.390 --> 00:00:27.440 Or where salaries less than 35,000 10 00:00:27.440 --> 00:00:29.910 or where surname is not equal to Smith. 11 00:00:29.910 --> 00:00:32.710 We're going to look at these operators in a bit more detail. 12 00:00:32.710 --> 00:00:36.070 But I just wanted to show you where the clause goes 13 00:00:36.070 --> 00:00:38.640 which is after the table name, 14 00:00:38.640 --> 00:00:42.480 so you select then you put your columns from your table 15 00:00:42.480 --> 00:00:45.300 and at the end, goes your WHERE clause. 16 00:00:45.300 --> 00:00:47.920 So like you would say in English. 17 00:00:47.920 --> 00:00:49.280 For our comparison operators, 18 00:00:49.280 --> 00:00:52.950 we've got lower than, we've got greater than, 19 00:00:52.950 --> 00:00:57.840 then less than equal to, or greater than or equal to. 20 00:00:57.840 --> 00:00:59.900 Notice that the order of the operators 21 00:00:59.900 --> 00:01:01.270 with the crocodile clip first 22 00:01:01.270 --> 00:01:03.580 and the equal sign later does matter, 23 00:01:03.580 --> 00:01:05.700 so make sure to keep it that way. 24 00:01:05.700 --> 00:01:08.530 A single equal sign means exactly equal to 25 00:01:08.530 --> 00:01:10.500 so this is different from Python 26 00:01:10.500 --> 00:01:12.290 where we use two equal signs 27 00:01:12.290 --> 00:01:13.500 to mean exactly equal to. 28 00:01:13.500 --> 00:01:15.090 In SQL, we only use one. 29 00:01:15.090 --> 00:01:16.290 That's because normally in SQL, 30 00:01:16.290 --> 00:01:18.080 we're not actually assigning anything. 31 00:01:18.080 --> 00:01:22.710 So there's no point to having the double equal sign in here. 32 00:01:22.710 --> 00:01:25.590 The exclamation mark equal means not equal to 33 00:01:25.590 --> 00:01:29.380 just like in Python, we can use AND and OR 34 00:01:29.380 --> 00:01:31.420 to chain comparisons logically. 35 00:01:31.420 --> 00:01:33.400 So here for example, the first example 36 00:01:33.400 --> 00:01:36.300 where years experience is greater than 10. 37 00:01:36.300 --> 00:01:38.410 And then AND being the key word 38 00:01:38.410 --> 00:01:41.650 in SQL salary less than 35,000. 39 00:01:41.650 --> 00:01:44.010 So this would give you the rows 40 00:01:44.010 --> 00:01:46.250 that have a number greater than 10 41 00:01:46.250 --> 00:01:48.730 for the years on the scored experience column, 42 00:01:48.730 --> 00:01:52.380 and the number less than 35,000 for the salary column. 43 00:01:52.380 --> 00:01:55.290 Or you can do where age less than or equal to 18 44 00:01:55.290 --> 00:01:57.660 or age greater than or equal to 65. 45 00:01:57.660 --> 00:01:58.690 So this would give you people 46 00:01:58.690 --> 00:02:02.763 that are not in their working age, you could say. 47 00:02:03.750 --> 00:02:05.300 You can use more than one chaining. 48 00:02:05.300 --> 00:02:07.190 So you can have two ORs like in here 49 00:02:07.190 --> 00:02:09.200 where age less than or equal to 18, 50 00:02:09.200 --> 00:02:13.100 or age greater than or equal to 65, or salary equals zero. 51 00:02:13.100 --> 00:02:14.380 This may tell you some information 52 00:02:14.380 --> 00:02:17.303 about people who are currently not working maybe. 53 00:02:18.140 --> 00:02:20.320 So let's take a look at this statement 54 00:02:20.320 --> 00:02:24.160 in a bit more detail with these three OR statements, 55 00:02:24.160 --> 00:02:27.270 these would match at least one of these must be true 56 00:02:27.270 --> 00:02:29.510 for the the WHERE clause to match. 57 00:02:29.510 --> 00:02:33.180 The person is 18 or under, or the person is 65 58 00:02:33.180 --> 00:02:36.469 or over, or the person salary is zero. 59 00:02:36.469 --> 00:02:39.020 So, somebody who is 50 years old 60 00:02:39.020 --> 00:02:41.260 and with a salary of zero would match, 61 00:02:41.260 --> 00:02:44.010 they are not 18 or under or 65 or over, 62 00:02:44.010 --> 00:02:45.400 but their salary is zero. 63 00:02:45.400 --> 00:02:47.750 So, therefore, the clause matches. 64 00:02:47.750 --> 00:02:51.000 Similarly, someone who is 17 with a salary of 10,000 65 00:02:51.000 --> 00:02:53.490 would match as well because their age 66 00:02:53.490 --> 00:02:57.073 is matching the first condition in this WHERE clause. 67 00:02:57.970 --> 00:03:01.032 You can group comparisons with brackets. 68 00:03:01.032 --> 00:03:02.580 For example, you've got here 69 00:03:02.580 --> 00:03:04.940 three examples of using brackets. 70 00:03:04.940 --> 00:03:07.460 You've got where age less than or equal to 18, 71 00:03:07.460 --> 00:03:09.770 or age greater than or equal to 65 72 00:03:09.770 --> 00:03:12.810 and salary greater than zero. 73 00:03:12.810 --> 00:03:16.410 So here, in this first comparison, 74 00:03:16.410 --> 00:03:19.120 and this could be read as 75 00:03:20.050 --> 00:03:23.070 you have to be less than or equal to 18, 76 00:03:23.070 --> 00:03:25.470 Or you have to be 65 or more 77 00:03:25.470 --> 00:03:28.340 and your salary be zero or greater than zero. 78 00:03:28.340 --> 00:03:31.050 However, this is not really what that means. 79 00:03:31.050 --> 00:03:35.620 So using brackets is going to make the intent much clearer. 80 00:03:35.620 --> 00:03:38.880 Here we should do and that's the age is less than 81 00:03:38.880 --> 00:03:41.900 or equal to 18, or the age greater than 65. 82 00:03:41.900 --> 00:03:43.700 This will evaluate first 83 00:03:43.700 --> 00:03:47.530 and it will either be true or false depending on their age, 84 00:03:47.530 --> 00:03:49.540 And the salary must be greater than zero. 85 00:03:49.540 --> 00:03:53.360 So they must be less than 18 or greater than 65. 86 00:03:53.360 --> 00:03:55.080 And their salary must be greater than zero 87 00:03:55.080 --> 00:03:57.330 for this to match. 88 00:03:57.330 --> 00:04:01.870 But in this example down here, they can be 18 or less, 89 00:04:01.870 --> 00:04:06.550 or they can be 65 or more with a salary greater than zero. 90 00:04:06.550 --> 00:04:08.270 So I know that these are a bit difficult 91 00:04:08.270 --> 00:04:10.440 to explain with words, I'm just saying the same thing 92 00:04:10.440 --> 00:04:12.650 with different intonation. 93 00:04:12.650 --> 00:04:14.116 But hopefully the brackets 94 00:04:14.116 --> 00:04:15.950 help you understand 95 00:04:15.950 --> 00:04:18.220 which one is going to evaluate first. 96 00:04:18.220 --> 00:04:20.740 So how about think about how these three could be different 97 00:04:20.740 --> 00:04:22.653 depending on what data you feed them. 98 00:04:23.870 --> 00:04:24.703 But do remember 99 00:04:24.703 --> 00:04:26.900 that they do mean completely different things. 100 00:04:26.900 --> 00:04:28.670 So we've got some more examples 101 00:04:28.670 --> 00:04:30.670 and information in the e-book. 102 00:04:30.670 --> 00:04:32.480 Remember, you've got access to that. 103 00:04:32.480 --> 00:04:34.330 And you can always access that at any point 104 00:04:34.330 --> 00:04:38.763 and the URL is pysql.tecladocode.com. 105 00:04:40.170 --> 00:04:43.010 All right, I'm here in DB Browser for SQLite. 106 00:04:43.010 --> 00:04:46.090 And we're going to start filtering our data 107 00:04:46.090 --> 00:04:48.120 that comes back from the SELECT statement. 108 00:04:48.120 --> 00:04:51.270 What I've got here is SELECT * FROM users. 109 00:04:51.270 --> 00:04:53.590 This select all the data from the users table 110 00:04:53.590 --> 00:04:54.800 and shows it to us. 111 00:04:54.800 --> 00:04:56.370 And you can see I've got these five rows 112 00:04:56.370 --> 00:04:59.270 that we had earlier on in a previous video as well. 113 00:04:59.270 --> 00:05:02.040 Now after SELECT * FROM users, we can type 114 00:05:02.040 --> 00:05:05.630 a WHERE clause that filters the data coming back. 115 00:05:05.630 --> 00:05:08.670 So it will remove some results that don't match 116 00:05:08.670 --> 00:05:11.210 the conditions that we set. 117 00:05:11.210 --> 00:05:14.830 For example, we can get data only where 118 00:05:14.830 --> 00:05:17.773 the surname is equal to Smith. 119 00:05:18.860 --> 00:05:22.450 Here, surname equal Smith is the condition. 120 00:05:22.450 --> 00:05:26.630 And we will only get data that satisfies this condition. 121 00:05:26.630 --> 00:05:29.060 Notice that it's very important the WHERE clause 122 00:05:29.060 --> 00:05:31.470 goes after SELECT * FROM users. 123 00:05:31.470 --> 00:05:33.320 So this goes after that. 124 00:05:33.320 --> 00:05:34.810 If we run this, you can see that 125 00:05:34.810 --> 00:05:38.270 we only get two rows back, Rolf Smith and John Smith, 126 00:05:38.270 --> 00:05:39.700 like we saw in the presentation, 127 00:05:39.700 --> 00:05:41.000 we can use other conditionals 128 00:05:41.000 --> 00:05:42.920 like greater than or less than 129 00:05:42.920 --> 00:05:45.750 to filter via those types of condition. 130 00:05:45.750 --> 00:05:47.880 We can also use not equal to, 131 00:05:47.880 --> 00:05:48.870 to filter the values 132 00:05:48.870 --> 00:05:51.550 that don't match a specific one we provide. 133 00:05:51.550 --> 00:05:54.150 For example, surname not equal, Smith 134 00:05:54.150 --> 00:05:57.160 can be used to remove the people that have a Smith surname 135 00:05:57.160 --> 00:06:00.233 and only get those that don't, just like that. 136 00:06:01.870 --> 00:06:04.310 A lot of the other conditionals, including AND and OR 137 00:06:04.310 --> 00:06:07.150 we saw in the presentation, so we won't go into them here. 138 00:06:07.150 --> 00:06:10.070 But I wanted to mention something about long queries, 139 00:06:10.070 --> 00:06:12.660 because as you start writing more and more conditionals, 140 00:06:12.660 --> 00:06:14.170 your queries can get a bit long. 141 00:06:14.170 --> 00:06:16.750 So, sometimes it's good to split them out 142 00:06:16.750 --> 00:06:17.660 into multiple lines. 143 00:06:17.660 --> 00:06:21.010 And that's something that SQL totally allows. 144 00:06:21.010 --> 00:06:22.700 So, for example, if you were selecting 145 00:06:22.700 --> 00:06:24.930 many different columns that could grow. 146 00:06:24.930 --> 00:06:26.890 If you were selecting from long table names, 147 00:06:26.890 --> 00:06:27.860 this could grow. 148 00:06:27.860 --> 00:06:31.520 And if you had long WHERE clauses with many ANDs and ORs, 149 00:06:31.520 --> 00:06:32.730 then this could grow as well. 150 00:06:32.730 --> 00:06:37.080 In three separate lines, we'll make it a bit easier to read. 151 00:06:37.080 --> 00:06:40.950 You can normally split these queries wherever you want, 152 00:06:40.950 --> 00:06:43.050 but it is quite common to leave 153 00:06:43.050 --> 00:06:46.070 the key words SELECT from WHERE and so forth, 154 00:06:46.070 --> 00:06:47.090 at the start of the line. 155 00:06:47.090 --> 00:06:48.360 I think it just makes it a little bit easier 156 00:06:48.360 --> 00:06:50.390 to read as well and to understand 157 00:06:50.390 --> 00:06:53.110 where different parts of the query go. 158 00:06:53.110 --> 00:06:55.210 So this is my recommended structure 159 00:06:55.210 --> 00:06:56.733 as your queries get longer. 160 00:06:57.650 --> 00:06:59.270 All right, thanks for watching this video. 161 00:06:59.270 --> 00:07:00.350 I hope you've learned something 162 00:07:00.350 --> 00:07:01.913 I'll see you in the next one.