 Dear students, a sub-select is a very powerful feature of MySQL. What is sub-select? Sub-select is nested selects. So it is a select within a select, nested within a select. And in this module, we not only look at sub-selects, but how they are implemented using not in and in using exists and not exists. And of course, we will see how to work with matching values, how to look for the missing values using this nested. And what will be the impact if a single row is returned? And what is the problem if multiple rows are returned? So this will be elaborated using examples. And again, I would suggest you to try them to actually type them, execute them and convince yourself and read the notes. I will show you the code and the results, but you have to run them yourself to convince yourself. So let's look at the outline of this module. So we have these sub-selects to produce the reference value. And we will talk about exists and not exist in and not in and rewriting as joins for matching, matching values and for missing values. It will be clear. Trust me, let's proceed ahead. So what we are looking at over here, the following is an example that looks up the IDs for event records corresponding to test T and uses them to select scores for these tests. Okay, and there are many ways to write the sub-selects. Select a static from score. Okay, we're over here. This is one approach. Okay, and this is using the in, right? In some cases, sub-selects can be rewritten as joins. We'll show how to do that later. You may find sub-select rewriting techniques useful depending upon the version of MySQL you are working with. There are several forms you can use to write sub-selects. This section, this module surveys just a few of them, not all of them. Using a sub-select to produce a reference value, you want the inner select to identify a single value to be used in the comparisons. Okay, this is the inner one. For example, to identify the scores for the quiz that took place on certain date, this date, use an inner select to determine the quiz event ID and then match score records against it in the outer select. Inner one. Okay, and this is the outer one. All right, now let's look at one problem which you are going to face and you might be tempted to write a certain code. Let's look at that example over here. In this form of sub-select where the inner query is preceded by a comparison operator, it's necessary that the inner join produce no more than a single value. Okay, multiple records will create a problem, will create a problem. If it produces multiple, the query will fail. In some cases, it may be appropriate to satisfy this constraint by limiting the inner query result with limit one. Okay, limit one, because the query is going to fail. This form of sub-select can be handy for situations where you would be tempted to use an aggregate function in a where clause. For example, to determine which student was born first, you might try to do this following. You might be tempted, tempted to write this query, but it's going to fail. That doesn't work because you can't use aggregate in the where clause. Okay, the where clause determines which records to select, but the value of min isn't known until after the records have already been selected. However, you can use a sub-select to produce the minimum birth date as follows. So you can use this sub-select. Okay, and now you will get the answer. This is the outer one. Now it's going to work. See, this is the power of the sub-select, which is not obvious until you actually run the query and come across the issue. Exists and not exist sub-selects. These forms of sub-selects work by passing values from the outer query to the inner one to see whether they match the conditions specified in the inner query. For this reason, you will need to qualify column names with table names if they're ambiguous, appear in more than one table. Exists and not exist sub-selects are useful for finding records. In one table that match or don't match records under table. So these are the two tables we were looking at. Not exist identifies non-matches. Values in one table that are not present in the other non-matches. With these forms of sub-selects, the inner query uses a steric as the output column list. There's no need to name columns explicitly because the inner query is assessed as true or false based on whether or not it returns rows, not based on the particular values that the rows may contain. Based upon true or false. Understand? In MySQL, you can actually write almost anything for the column selection list. But if you want to make it explicit that you are returning a true value, when the inner select succeeds, you might write the queries like this. Like this and like this. So let's look at the in and out sub-selects. The in and out forms of sub-selects should return a single column of values from the inner select to be evaluated in a comparison in the outer select. Okay? Single column of values. Single column of values. For example, the preceding exists and not exist queries can be written using in and out syntax as follows. The previous one, you can write them using in and not in. Okay? And if you go back, you can see that these results, if you go back to the previous module, which I'll show you, these are the same results which we were getting using the exist and not exist, which we can get using in and out. Now let's look at finding matching and missing values. For the versions of MySQL prior to 4.1 sub-selects were not available. However, it's often possible to rephrase a query that uses a sub-select in terms of a join. In fact, even if you have MySQL 4.1 or later, it's not a bad idea to examine queries that you might be inclined to write in terms of sub-selects. A join is sometimes more efficient than a sub-select. So you have this query over here, okay? And using the in and the same query can be written without a sub-select. Without a sub-select by convert into a simple join. Okay? Similarly, we have another query over here. Okay? With sub-select. This is sub-select over here. And you can use it using a join and have the same results for the matching values. And for the missing values, what you do is that for the missing values, you use the not in. And they are the same equivalent. So this is the power of the sub-select and writing them instead using joins. So that's all I have for this module.