In your Oracle SQL SELECT statement, you know what columns and expressions you want to return in your report, but you want your results to be more along the lines of a soft drink through a straw instead of a deluge from a fire hose. Learn how your WHERE clause can carefully extract only the data you need in a report by using comparison operators. You can compare columns to each other in the same table, to constants, and to values in another table, all in the same WHERE clause.
- [Instructor] The Where Clause and How to Put the What in Where. Let's take a deeper dive into where. Where? Here. The where clause helps you filter rows. You rarely want to return every single row in a table. Orders table, employees table, history table. Usually you want to see the last day or a certain employees and so forth. Of course you can filter it by just about everything. By date value strings. Numeric values. Wildcard strings and so forth.
Even a new feature of Oracle 12c, limiting rows with a row limiting clause We'll see an example of that in our demo later. Here's some logical expressions you might see in a where clause. Checking for a customer ID in a certain range or in a list. Or you can search within the product description that has the string dim in the middle with anything before it and anything after it. Order totals between two numeric values ans so forth.
There are cases where you might use a subquery in your where clause instead of a join. We'll dive deeper into that in another video, but sometimes you might want to use subquery in a select statement just for readability purposes. It might generate the same Oracle execution plan but it's just easier for the next developer to read when they have to make changes later. Another reason you might use a subquery is when you don't need to return any columns from the table and the subquery. You're just checking for the existence of a row. Again we'll see examples of that in our demo later.
The row limiting clause. Here's the syntax in a nutshell. You can start at a particular row number. Get a certain number of rows. You can also specify the rule for ties. For example, if you have a thousand rows and most of them have the value 50, you might want to return the first 100 or you might want to return all the 50s. That's what the last clause is for only or with ties. As a result, you might get more rows than you expect but that's why you have the option to specify only or with ties.
As you notice in these syntax you can also just get a percentage of the rows. If you don't really know how many rows are in the table, you want to just return the first 10 percent. Of course you have an order by clause. It's part of a select statement and you can include that as well when you use a row limiting clause. Here are a couple of quick examples of using the row limiting clause. In the first example, you're fetching the first 10 rows from the oe.orders table, but you're using an order by clause. First Oracle is going to sort the rows in your select statement and then return the first 10.
In the second example, you're picking a list of customers from the customers table and you're ordering it by date of birth and then taking the top five percent of that result set, whatever it is. Before we start our examples, let me show you a couple of ways to launch SQL developer. It will be in your applications or start menu under programming, SQL developer. Go ahead and start it that way. Another way to start it is at the OS prompt.
Just type SQL developer. What you'll see when you start SQL developer are the normal icons you'd see in most applications. Create a new window, open any existing file, save a file and so forth. To open a file, click on the folder icon, pick the file you want to open, usually it's some SQL code and there you go. In the upper left hand section are all the database connections you've created. It makes it easy to get to multiple databases in one place. You'll have a reports window, where you have some handy reports that you can run against the data dictionary to do data modeling and so forth.
Those come by default with SQL developer. The other useful menu is the dba menu, where if you have connections to databases with much higher privileges you put them there. Once you open up a file or create a new one, to run a SQL statement you just highlight the SQL statement, press the Go button like so. If you don't have the connections selected in advance, you go ahead and select it in the dropdown. In the upper right hand corner you can switch connections if you want to run that SQL statement against a different database.
Another way to run the statement is to highlight it and press the F9 key or control + enter does the same thing. Up above each window are a number of other useful functions such as commit, rollback, do and explain plan, and so forth. Here are a couple of examples. The first query is much like a validation query. We're making sure that there are no order items that don't have a corresponding order ID.
We do that with a subquery in the where clause. As you can see here where the order ID is not in the list of order IDs in the order items table. In other words, there are no order item rows. They'd have an order ID that doesn't exist in the the orders table and this query will validate that. When I run it, there aren't any. Well that's good because that's what you're checking for. If there were some problem with rows being returned from this query, you'd want to check the logic of your application because you should never have an order item that doesn't have a corresponding master order.
Let's look at those other two queries. This first query retrieves the first 10 rows from the orders table after it was sorted by order date. In this second example we have a promotion going on. We want to take the customers that have been with us the longest, but only the top five percent of those. We're selecting the customer IDs, names, and date of birth from the customers table. Ordering it by date of birth, meaning the older customers are showing up on the list first.
We only want the first five percent. If we run this one we get that list of customer IDs and customer names. Our oldest customer is Ridley Hackman, who was born in 1950. We don't know how many total customers we have. We just want the first five percent. In this case, it ends up being 16 customers. We're going to give them some kind of special prize, or reward, or a special bonus for being a customer so long. Here's what we found out.
The where clause another one of your friends just like nulls are not your friend. You have to filter your rows. You need to reduce the size of your report. You can't show your users all the rows all the time. You can use expressions, other column names, comparison operators and so forth to help you filter in the where clause. You can use a subquery. If you can't use a join or if the logic is too complicated, put another select statement in your where clause and that will help you limit the rows in the result set.
Finally, we have the row limiting clause so that if you only want the top five percent of the rows, you only want an exact number of rows then you use the row limiting clause. You can the order by clause in conjunction with that to make sure that you get just the percentage or number of rows that you're looking for.
- Design methodology and tools
- SQL query basics
- Advanced data manipulation
- Modifying and creating a database object
- Database architecture
- DBA configuration tools
- Managing tablespaces and disk space
- Table compression
- Manage system-related tablespaces
- Database monitoring