Introduces wildcards in query filters, plus using the IN keyword to match multiple values
- [Instructor] This query here returns us 10 rows. But if you want to save yourself a bit of typing you could use the IN statement when adding WHERE clauses to the same field. So instead of all these rows here we could delete that and say WHERE first_name IN and then we use parentheses or brackets and then we list all of the allowable values. Like so. And if we click go on that we get the same 10 rows that we had before. So the IN statement saves you more time the more values you have.
And it should be read as matches any of the following. You can also use NOT IN to identify the values you want to exclude. And we see that's returned as 190 rows which is what we'd expect. Note that we've put single quote marks around each value because each one of these is a string or text. You can put as many values as you like into the brackets here as long as they're all separated by commas. SQL will also let you use wild cards to match part of a text.
Let's say we want to select any actor with a name starting with the letters J-O-H-N. Then we would use the LIKE keyword. The wild card we are going to use is the percent sign. Like so. So if we run that we see that we've returned a John and a couple of Johnnys there. The percent sign will match any value including no value or multiple values. There is another wild card character for matching just a single letter.
And that's the underscore. So we can say JA_NE with an underscore in the middle of it. And that's returned us lots of Jaynes with a Y. The underscore will match any kind of character but there must be one there. It won't match a blank. So in this case it's returned us a few Jaynes with a Y but in fact I happen to know there's a Jane spelled J-A-N-E in the table and it hasn't returned us this. If we modified that to be the percent sign we'd see we've now got four rows instead of three.
One thing to note here is that you can't combine LIKE with IN. It feels quite tempting to write something like IN Jane John like so. But if we run that we get no results and that's because there aren't any actors in the table whose name is John%. Using a wild card without using the LIKE keyword means that the database software takes any special characters to be part of the string.
Now if you're using Oracle or Microsoft there is a way of combining LIKE with IN effectively and that's to use a function that's called CONTAINS. Sadly this is not part of my sequel. Now we're going to look at combining ANDs with ORs. We're going to run this on the address table which I've just clicked on. So you can see a number of the fields here like address and district and so on. Actually you can run a query on a table without clicking on it. We could have run it from the previous screen but I wanted to give you the chance to see the field names down here.
So what we want is SELECT * FROM 'address' WHERE district equals Buenos Aires AND address LIKE El OR address LIKE Al If we run that we see we returned three rows there.
All of the have a district of Buenos Aires and in the address field they either contain El or Al. Notice that the case I used in my WHERE clause was irrelevant. So I used a capital A and a lower case l there but it's matched with Dallas in which both a and l are lowercase. So these sorts of enquiries are case insensitive. Notice also that we used parentheses around the OR statement. This is to help the database software read the query as we intend it.
Without brackets the database will read and apply the filters in the order they appear. So let's try it. This returns us 73 rows instead of the three that we had previously. And if we look we see many of the rows are not in the district of Buenos Aires. So what's happened? The database has locked together WHERE with AND. So it's returned any row with a district of Buenos Aires and an address like El.
After returning those rows the database will also then match on any address like Al. Irrespective of whether the district is Buenos Aires. So you can see Lillydale has an Al. Sal Drive, Bhopal, all of these have Als but no district. Parentheses are invaluable to tell the database software precisely what you mean in your filtering clause.
Join Emma Saunders as she shows you how to design and write simple SQL queries for data reporting and analysis. Review the different types of SQL, and then learn how to filter, group, and sort data, using built-in SQL functions to format or calculate results. Learn a bit about data types and database design. Discover how to perform more complex queries, such as joining data together from different database tables. Last but not least, Emma shows how to save your queries as views, so you can run them again and again.
- Using different versions of SQL
- Retrieving data with SELECT statements
- Filtering and sorting your results
- Transforming results with built-in SQL functions
- Grouping SQL results
- Merging data from multiple tables
- Identifying data types, and how to make sense of your database design
- Saving SQL queries