1 00:00:00,450 --> 00:00:05,320 We saw in the previous video how we can use a new keyword to reduce the complexity 2 00:00:05,320 --> 00:00:07,250 of some of our queries. 3 00:00:07,250 --> 00:00:11,290 We reduced multiple OR conditions to a single in condition. 4 00:00:11,290 --> 00:00:12,940 Here's another example. 5 00:00:12,940 --> 00:00:16,972 Let's say we wanted to find all books in our library from the 19th century. 6 00:00:16,972 --> 00:00:19,680 Let's remind ourselves of the book tables schema. 7 00:00:21,000 --> 00:00:26,693 We could do something like, 8 00:00:26,693 --> 00:00:29,777 SELECT title, 9 00:00:29,777 --> 00:00:34,522 author FROM books WHERE 10 00:00:34,522 --> 00:00:41,880 first_published >= 1800 AND 11 00:00:41,880 --> 00:00:49,013 first_published <= 1899. 12 00:00:49,013 --> 00:00:51,660 What we've built it on is known as a range. 13 00:00:51,660 --> 00:00:54,830 There's a minimum value and a maximum value. 14 00:00:54,830 --> 00:00:58,660 If the value fits in this range, the condition is met. 15 00:00:58,660 --> 00:01:03,373 There's a way to express a range in clearer terms by using 16 00:01:03,373 --> 00:01:05,248 the BETWEEN keyword. 17 00:01:05,248 --> 00:01:10,518 Here's a syntax of using the BETWEEN keyword. 18 00:01:10,518 --> 00:01:12,034 In the WHERE clause, 19 00:01:12,034 --> 00:01:16,676 you first state the column you want to test in a particular range. 20 00:01:16,676 --> 00:01:21,580 Then you type the keyword BETWEEN, followed by the first value. 21 00:01:21,580 --> 00:01:25,280 Then the AND keyword, and then the second value. 22 00:01:26,570 --> 00:01:32,945 In our case we want our first published BETWEEN 1800 AND 1899. 23 00:01:32,945 --> 00:01:35,230 And it returns the same results. 24 00:01:35,230 --> 00:01:37,720 The lower value has to be first. 25 00:01:37,720 --> 00:01:39,357 Swapping these like this. 26 00:01:46,582 --> 00:01:48,860 Will yield no results. 27 00:01:48,860 --> 00:01:51,980 BETWEEN can be used for more than numeric values. 28 00:01:51,980 --> 00:01:54,444 It's often used for date ranges too. 29 00:01:54,444 --> 00:02:00,470 For example, in our loans table we can set for all loans in a particular week. 30 00:02:00,470 --> 00:02:06,110 Let's see all loans from a week in December 2015, starting Sunday the 13th. 31 00:02:06,110 --> 00:02:08,230 Let's remind ourself of the loan schema. 32 00:02:10,790 --> 00:02:15,583 We can write, SELECT * FROM 33 00:02:15,583 --> 00:02:20,978 loans WHERE loaned_on BETWEEN 34 00:02:20,978 --> 00:02:25,973 "2015-12-13 AND 35 00:02:25,973 --> 00:02:31,177 "2015-12-19". 36 00:02:39,265 --> 00:02:43,000 And those are the two loans that needed to be returned that week. 37 00:02:43,000 --> 00:02:43,810 As you can see, 38 00:02:43,810 --> 00:02:49,300 the BETWEEN keyword simplifies queries where you're dealing with ranges. 39 00:02:49,300 --> 00:02:52,160 You don't have to think about operators at all. 40 00:02:52,160 --> 00:02:56,660 You can just say BETWEEN and the minimum and maximum values of your range.