Understanding Subqueries

Carlos from Standout-Dev Academy
A free video tutorial from Carlos from Standout-Dev Academy
Software Development Director & Chief Educator
4.5 instructor rating • 2 courses • 79,939 students

Lecture description

In this lecture you will be introduced to subqueries and will understand what they are and how they work in Oracle SQL

Learn more from the full course

The Ultimate Oracle SQL Course: SQL Made Practical

From beginner to Oracle SQL genius: Become a professional SQL Developer with this Hands-On Oracle SQL training.

12:45:02 of on-demand video • Updated September 2021

  • You will be able to use Oracle SQL to retrieve, filter, analyze, format and present information from Oracle databases.
  • You will be able to use SQL to insert, modify and delete information from Oracle databases.
  • You will understand Oracle SQL code written by other people and feel confident to modify it.
  • You will be able to write the SQL code needed to solve the most common problems found in real work situations and academic tests.
  • Please note that PL/SQL is not covered in this course.
English So, what is a subquery? A subquery is a SELECT statement that is included or nested within another SQL statement, which can be another SELECT or an INSERT, UPDATE or DELETE, and they are always enclosed within parentheses. In some cases subqueries are executed prior to the execution of the main statement where they are nested in, and sometimes they are executed once for every row in the potential resultset of the main query. I’m going to show you a couple of examples: Take a look at this query: The subquery is this SELECT statement you see here, which as I mentioned before, is enclosed in parenthesis. And the statement where this subquery is nested in is usually called the MAIN query, or the MAIN statement. In this case, the subquery is executed once at the very beginning, and once the results are ready, it is used to evaluate the condition in the WHERE clause of the main query. So, what does this subquery return? So, once this result is ready, the main query is executed like this: Now, when I have a subquery like this one, whose result is going to be compared in the main query by using an equality operator, or a less than or greater than or other similar comparison operator, it must return only one column and only one row, or in other words, it must return a single value. This type of subquery is usually called a SCALAR SUBQUERY. Now, let’s look at another example. This subquery returns only one column but it can return more than one row, because here I’m using the IN operator, which accepts a list of values, not a single value like equal to or greater than operators. You probably remember that I mentioned in the operators section that the IN operator could also be used with a subquery, instead of having to provide the list of expressions at design time. So, this is the great thing about using subqueries this way. You don’t have to provide a static list of expressions. Instead you can provide a subquery, which will provide the list of values to be used in the evaluation at runtime. In this case, the subquery is executed only once at the beginning as well, and its results are kept in memory to be used when evaluating the condition in the main query’s WHERE condition. This is what this subquery returns: So, the main query is now executed as if I had written it this way: Now, let’s look at another type of subquery. Again, since we are using a less than operator here, the subquery must return only one row and only one column, but there is something different with this subquery: Look at the WHERE clause. This condition compares the department id with this thing here, but what is this? "d" is the alias I assigned to the department table in the main query, so this condition is comparing the department id in the employee table to the id column in the department table, and this has two consequences for us: First, we cannot test this query as a stand alone statement. It would give us an error. And second, since this subquery is related to one row in the potential result set from the main query, it cannot be executed only once at the beginning. It needs to be executed once FOR EACH ROW in the list of rows that the main query is evaluating to provide its final result. Since this type of subquery is related to the main query by means of this condition, this type of subquery is called a CORRELATED subquery, and correlated subqueries are usually less efficient than the previous type of subqueries we saw, which are called NON CORRELATED subqueries. In these examples I have put the subqueries in the WHERE clause of the main query, but they can also appear in other parts of the statement, such as in the select list as in this example: As you can see here, this is a correlated subquery too, and as always, it is enclosed in parentheses. In this case the subquery is executed once for each row in the resultset of the main query as well, and as you see, the result of the subquery is returned as an additional column in the final result set, so, since it is must return one value for each row, a subquery in the SELECT list must always be a scalar subquery. There is a situation that involves the use of subqueries, which can give you incorrect results and could drive you crazy when trying to figure out why it doesn’t work the way you want. Pause the video for a moment and go to the resources section to copy the query that is listed as statement number 1 in the resource file. This time you just have to copy the query that is included in the file, even if you are using LiveSQL. Okay, now please run the query you copied from the script. How many rows did you get? Now, write a query to list all the rows in the department table, and take a look at the results. How many rows do you see with a monthly budget greater than 25000? So, if only Information Technology has a monthly budget greater than 25000, why are you getting all of the employees in the previous query? I don’t know either! Now, copy the subquery and run it as a stand alone statement. Go ahead, pause the video and run it. What happened? Pretty weird, don’t you think? This is what happens: Since the department_id column doesn't exist in the department table, Oracle "thinks" that I am referring to the department_id column from the employee table (which is used in the main query), and treats this as a "correlated subquery". So, as you see, I don’t even need to qualify this column with the alias of the table from the main statement, because if Oracle notices that I’m using a column that doesn’t exist in the tables of the subquery, it will look for it in the tables from the main statement, and if it exists there, it will treat the suqbuery as a correlated one, and will not raise an error. So, this is the tip: When writing statements that involve subqueries, make sure to test the subqueries individually when possible, especially when you are getting incorrect results without an apparent reason. To conclude this lesson, let me show you another example. I have a query here that I want to show you. There are two things I want to bring to your attention here: The first one is this WHERE clause. As you can see I’m using the IN operator, but I have 2 columns here, and in consequence my subquery has to return 2 columns as well. So, you can have any number of expressions in this part of the condition, not just one as we have seen so far, but your subquery must return the same number of columns. Otherwise you will get an error. This is very nice, but many people don’t know it. And the second thing I wanted to show you is that I have another subquery here, inside of this subquery, which is inside the main query. So, this is what is called, nested subqueries, and Oracle allows you to have a big number of levels when nesting subqueries. You can nest up to 255 levels in subqueries in the WHERE clause. Yeah, the number is ridiculous. And one final thing I haven’t mentioned so far: When you have subqueries like these ones which appear in the where clause, they cannot have an order by clause. If you add an order by clause to a subquery like these ones, you will get an error, but it just wouldn’t make sense to add an order by clause here anyway, because we don’t care about the order of the results of this kind of subqueries. We care about the whole set of rows they return, regardless of the order of the rows. Okay, time for your task. See you later.