Select all rows for girls born in New York in the year 2014 or, all rows for babies named 'Madison' in the year 2011. Here's one way to organize the previous SQL statement (note how I use whitespace to make it even more readable): SELECT * FROM baby_names WHERE ( year = 2014 OR state = 'NY' AND sex = 'F' ) OR ( name = 'Madison' AND year = 2011 ) For example, the above arithmetic expression is equivalent to this: 10 + (9 * 2) This is similar to how in standard arithmetic, there is a rule that states that the multiplication operation happens before the addition operation: 10 + 9 * 2īut rather than memorize how the rules of order in SQLite, just use parentheses, for readability, if nothing else. whether the OR conditions are evaluated after the AND conditions. So the SQLite syntax does define the order of operations here, i.e. It's possible to create very complex combinations of conditions even with just AND and OR: SELECT * FROM baby_names WHERE year = 2014 OR state = 'NY' AND sex = 'F' OR name = 'Madison' AND year = 2011 all girl names and the relatively few rows that correspond to boys named 'Sarah': SELECT * FROM baby_names WHERE name = 'Sarah' OR sex = 'F' Use parentheses to explicitly state the order ![]() The following query would return all rows with a name of 'Sarah' or all rows with a sex of 'F' – i.e. Unlike AND, the more OR conditions you include, the more the result set could potentially expand. In the example below, I'll include an ORDER BY and LIMIT clause, both of which follow the WHERE clause, to show the first 5 results by largest COUNT: SELECT * FROM baby_names WHERE name = 'Sarah' OR name = 'Alyssa' ORDER BY count DESC LIMIT 5 While it's impossible for a row to have a name be 'Sarah' and 'Daniel', it is perfectly reasonable for a row to be either 'Sarah' or 'Daniel'. ![]() So when the result of a query is 0 – and you are not expecting it to be 0 – take a careful look at the logic of your conditional expressions. The SQL syntax is perfectly fine in both of the above examples, so the interpreter won't complain. What's very important to realize – so important I'll reiterate the point at the end of this lesson – is that a logically impossible query will not raise an error message. Likewise, the following query would return 0 rows: SELECT * FROM baby_names WHERE name = 'Dan' AND name = 'Daniel' Įven though we often think of people named 'Daniel' as also being 'Dan', the database considers them to be two completely separate values (though in a later chapter, we'll see how to match on partial values). This is because each row can only have one name value thus, while it is possible to find rows in which sex is M and name is Sarah, it is logically impossible to have rows in which the name is both 'Sarah' and 'Daniel'. If you execute the above query, it will return 0 rows. With the AND keyword, it's very easy to unintentionally set conditions that are impossible for any row to meet: SELECT * FROM baby_names WHERE name = 'Sarah' AND name = 'Daniel' Mutually exclusive and logically impossible conditions Though, of course, the more required conditions we add, the fewer the results that will meet all of the conditions: SELECT * FROM baby_names WHERE name = 'Sarah' AND year = 2012 AND state = 'USA' AND sex = 'M' state We can add as many conditions as we'd like: SELECT * FROM baby_names WHERE name = 'Sarah' AND year = 2012 AND state = 'USA' state For example, if we want only rows that have a name of 'Sarah' and a year of 2012: SELECT * FROM baby_names WHERE name = 'Sarah' AND year = 2012 The AND keyword is used to specify multiple requirements. ![]() But no matter how convoluted the WHERE clause can get, the SQLite database will test all of its conditions for each row, and only the rows that pass all the conditions will be returned. As we'll see in the next sections, the WHERE clause can be stacked with as many conditional expressions as we want. Otherwise, it is filtered out.Īnd that's the gist of it. If true, then the row is included in the result. A condition is some kind of expression that evaluates to either true or false.įor example, the following query will return all rows in which the name column is equal to the text string of 'Sarah': SELECT * FROM baby_names WHERE name = 'Sarah' įor each row in the database, the value in the column name is checked to see if it is equal to 'Sarah'. The WHERE clause allows us to set the conditions by which rows are fetched from data tables. ![]() Differentiate between single- and double- quotes.Use parentheses to explicitly state the order.Mutually exclusive and logically impossible conditions.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |