airplanes<\/code>. However, the sample data I use for this article is different from the last article, so I recommend that you once again rebuild the database and tables to keep things simple for this article\u2019s examples. You can set up the database by running the following script:<\/p>\n\n\n\nDROP DATABASE IF EXISTS travel;\n\nCREATE DATABASE travel;\nUSE travel;\nCREATE TABLE manufacturers (\n manufacturer_id INT UNSIGNED NOT NULL AUTO_INCREMENT,\n manufacturer VARCHAR(50) NOT NULL,\n create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,\n last_update TIMESTAMP NOT NULL \n DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n PRIMARY KEY (manufacturer_id) )\nENGINE=InnoDB AUTO_INCREMENT=1001;\n\nCREATE TABLE airplanes (\n plane_id INT UNSIGNED NOT NULL AUTO_INCREMENT,\n plane VARCHAR(50) NOT NULL,\n manufacturer_id INT UNSIGNED NOT NULL,\n engine_type VARCHAR(50) NOT NULL,\n engine_count TINYINT NOT NULL,\n max_weight MEDIUMINT UNSIGNED NOT NULL,\n wingspan DECIMAL(5,2) NOT NULL,\n plane_length DECIMAL(5,2) NOT NULL,\n parking_area INT GENERATED ALWAYS AS ((wingspan * plane_length)) STORED,\n icao_code CHAR(4) NOT NULL,\n create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,\n last_update TIMESTAMP NOT NULL \n DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n PRIMARY KEY (plane_id),\n CONSTRAINT fk_manufacturer_id FOREIGN KEY (manufacturer_id) \n REFERENCES manufacturers (manufacturer_id) ) \nENGINE=InnoDB AUTO_INCREMENT=101;<\/pre>\n\n\n\nAfter you\u2019ve created the database, you can add the sample data so you can follow along with the exercises in this article. Start by running the following INSERT<\/code> statement to add data to the manufacturers<\/code> table:<\/p>\n\n\n\nINSERT INTO manufacturers (manufacturer)\nVALUES ('Airbus'), ('Beagle Aircraft Limited'), ('Beechcraft'), ('Boeing'), \n ('Bombardier'), ('Cessna'), ('Dassault Aviation'), ('Embraer'), ('Piper');<\/pre>\n\n\n\nThe statement adds nine rows to the manufacturers<\/code> table, which you can confirm by querying the table. The manufacturer_id<\/code> value for the first row should be 1001<\/code>. After you confirm the data in the manufacturers<\/code> table, you can run the following INSERT<\/code> statement to populate the airplanes<\/code> table, using the manufacturer_id<\/code> values from the manufacturers<\/code> table:<\/p>\n\n\n\nINSERT INTO airplanes \n (plane, manufacturer_id, engine_type, engine_count, \n wingspan, plane_length, max_weight, icao_code)\nVALUES\n ('A340-600',1001,'Jet',4,208.17,247.24,837756,'A346'),\n ('A350-800 XWB',1001,'Jet',2,212.42,198.58,546700,'A358'),\n ('A350-900',1001,'Jet',2,212.42,219.16,617295,'A359'),\n ('A380-800',1001,'Jet',4,261.65,238.62,1267658,'A388'),\n ('A380-843F',1001,'Jet',4,261.65,238.62,1300000,'A38F'),\n ('A.109 Airedale',1002,'Piston',1,36.33,26.33,2750,'AIRD'),\n ('A.61 Terrier',1002,'Piston',1,36,23.25,2400,'AUS6'),\n ('B.121 Pup',1002,'Piston',1,31,23.17,1600,'PUP'),\n ('B.206',1002,'Piston',2,55,33.67,7500,'BASS'),\n ('D.5-108 Husky',1002,'Piston',1,36,23.17,2400,'D5'),\n ('Baron 56 TC Turbo Baron',1003,'Piston',2,37.83,28,5990,'BE56'),\n ('Baron 58 (and current G58)',1003,'Piston',2,37.83,29.83,5500,'BE58'),\n ('Beechjet 400 (same as MU-300-10 Diamond II)',1003,'Jet',2,43.5,48.42,15780,'BE40'),\n ('Bonanza 33 (F33A)',1003,'Piston',1,33.5,26.67,3500,'BE33'),\n ('Bonanza 35 (G35)',1003,'Piston',1,32.83,25.17,3125,'BE35'),\n ('747-8F',1004,'Jet',4,224.42,250.17,987000,'B748'),\n ('747-SP',1004,'Jet',4,195.67,184.75,696000,'B74S'),\n ('757-300',1004,'Jet',2,124.83,178.58,270000,'B753'),\n ('767-200',1004,'Jet',2,156.08,159.17,315000,'B762'),\n ('767-200ER',1004,'Jet',2,156.08,159.17,395000,'B762'),\n ('Learjet 24',1005,'Jet',2,35.58,43.25,13000,'LJ24'),\n ('Learjet 24A',1005,'Jet',2,35.58,43.25,12499,'LJ24'),\n ('Challenger (BD-100-1A10) 350',1005,'Jet',2,69,68.75,40600,'CL30'),\n ('Challenger (CL-600-1A11) 600',1005,'Jet',2,64.33,68.42,36000,'CL60'),\n ('Challenger (CL-600-2A12) 601',1005,'Jet',2,64.33,68.42,42100,'CL60'),\n ('414A Chancellor',1006,'Piston',2,44.17,36.42,6750,'C414'),\n ('421C Golden Eagle',1006,'Piston',2,44.17,36.42,7450,'C421'),\n ('425 Corsair-Conquest I',1006,'Turboprop',2,44.17,35.83,8600,'C425'),\n ('441 Conquest II',1006,'Turboprop',2,49.33,39,9850,'C441'),\n ('Citation CJ1 (Model C525)',1006,'Jet',2,46.92,42.58,10600,'C525'),\n ('EMB 175 LR',1008,'Jet',2,85.33,103.92,85517,'E170'),\n ('EMB 175 Standard',1008,'Jet',2,85.33,103.92,82673,'E170'),\n ('EMB 175-E2',1008,'Jet',2,101.67,106,98767,'E170'),\n ('EMB 190 AR',1008,'Jet',2,94.25,118.92,114199,'E190'),\n ('EMB 190 LR',1008,'Jet',2,94.25,118.92,110892,'E190');<\/pre>\n\n\n\nThe manufacturer_id<\/code> values from the manufacturers<\/code> table provide the foreign key values needed for the manufacturer_id<\/code> column in the airplanes<\/code> table. After you run the second INSERT<\/code> statement, you can query the airplanes<\/code> table to confirm that 35 rows have been added. The first row should have been assigned 101<\/code> for the plane_id<\/code> value, and the plane_id<\/code> values for the other rows should have been incremented accordingly.<\/p>\n\n\n\nBuilding a basic scalar subquery<\/h2>\n\n\n\n A scalar subquery is one that returns only a single value, which is then passed into the outer query through one of its clauses. The subquery is used in place of other possible expressions, such as a constants or column names. For example, the following SELECT<\/code> statement (the outer query) includes a subquery in search condition of the WHERE<\/code> clause:<\/p>\n\n\n\nSELECT plane_id, plane\nFROM airplanes\nWHERE manufacturer_id = \n (SELECT manufacturer_id FROM manufacturers \n WHERE manufacturer = 'Beechcraft');<\/pre>\n\n\n\nThe subquery is the expression on the right side of the equal sign, enclosed in parentheses. A subquery must always be enclosed in parentheses, no matter where it\u2019s used in the outer statement.<\/p>\n\n\n\n
Make certain that your subquery does indeed return only one value, if that\u2019s what it\u2019s supposed to do. If the subquery were to return multiple values and your WHERE<\/code> clause is not set up to handle them (as in this example), MySQL will return an error letting you know that you messed up.<\/p>\n\n\n\nIn this case, the subquery is a simple SELECT<\/code> statement that returns the manufacturer_id<\/code> value for the manufacturer named Beechcraft. This value, 1003<\/code>, is then passed into the WHERE<\/code> clause as part of its search condition. If a row in the airplanes<\/code> table contains a manufacturer_id<\/code> value that matches 1003<\/code>, the row is included in the query results, which are shown in the following figure.<\/p>\n\n\n\n<\/p>\n\n\n\n
The subquery in this example retrieves data from a second table, in this case, manufacturers<\/code>. However, a subquery can also retrieve data from the same table, which can be useful if the data must be handled in different ways. For example, the subquery in the following SELECT<\/code> statement retrieves the average max_weight<\/code> value from the airplanes<\/code> table:<\/p>\n\n\n\nSELECT plane_id, plane, max_weight\nFROM airplanes\nWHERE max_weight > \n (SELECT AVG(max_weight) FROM airplanes);<\/pre>\n\n\n\nThe WHERE<\/code> clause search condition in the outer statement uses the average to return only rows with a max_weight<\/code> value greater than that average. If you were to run the subquery on its own, you would see that the average maximum weight is 227,499 pounds. As a result, the outer SELECT<\/code> statement returns only those rows with a max_weight<\/code> value that exceeds 227,499 pounds, as shown in the following figure.<\/p>\n\n\n\n<\/p>\n\n\n\n
As I mentioned earlier in the article, you can use subqueries in statements other than SELECT<\/code>. One of those statements is the SET<\/code> statement, which lets you assign variable values. You can use a SET<\/code> statement to define a value that can then be passed into other statements. For example, the following SET<\/code> and SELECT<\/code> statements implement the same logic as the previous example and return the same results:<\/p>\n\n\n\nSET @avg_weight = \n (SELECT ROUND(AVG(max_weight)) FROM airplanes);\nSELECT plane_id, plane, max_weight\nFROM airplanes WHERE max_weight > @avg_weight;<\/pre>\n\n\n\nThe SET<\/code> statement defines a variable named @avg_weight<\/code> and uses a subquery to assign a value to that variable. This is the same subquery that is in the previous example. The SELECT<\/code> statement then uses that variable in its WHERE<\/code> clause (in place of the original subquery) to return only those rows with a max_weight<\/code> value greater than 227,499 pounds.<\/p>\n\n\n\nThe examples in this section focused on using scalar subqueries in SELECT<\/code> statements, but be aware that you can also use them in UPDATE<\/code> and DELETE<\/code> statements, as well as the SET<\/code> clause of the UPDATE<\/code>.<\/p>\n\n\n\nOne of the most valuable features of a subquery is its ability to reference the outer query from within the subquery. Referred to as a correlated subquery, this type of subquery can return data that is specific to the current row being evaluated by the outer statement. For example, the following SELECT<\/code> statement uses a correlated subquery to calculate the average weight of the planes for each manufacturer, rather than for all planes:<\/p>\n\n\n\nSELECT m.manufacturer_id, m.manufacturer, \n a.plane_id, a.plane, a.max_weight\nFROM airplanes a INNER JOIN manufacturers m\n ON a.manufacturer_id = m.manufacturer_id\nWHERE a.max_weight > \n (SELECT ROUND(AVG(max_weight)) FROM airplanes a2\n WHERE a.manufacturer_id = a2.manufacturer_id);<\/pre>\n\n\n\nUnlike the previous two examples, the subquery now includes a WHERE<\/code> clause that limits the returned rows to those with a manufacturer_id<\/code> value that matches the current manufacturer_id<\/code> value in the outer query. This is accomplished by assigning an alias (a<\/code>) to the table in the outer query and using that alias when referencing the table\u2019s manufacturer_id<\/code> column within the subquery.<\/p>\n\n\n\nIn this case, I also assigned an alias (a2<\/code>) to the table referenced within the subquery, but strictly speaking, you do not need to do this. I like to include an alias for consistency and code readability, but certainly take whatever approach works for you.<\/p>\n\n\n\nTo better understand how the subquery works logically (as opposed to how the optimizer might actually execute it), consider the first row in the airplanes<\/code> table, which has a manufacturer_id<\/code> value of 1001<\/code>.<\/p>\n\n\n\nWhen the outer query evaluates the first row, it compares the max_weight<\/code> value to the value returned by the subquery. To carry out this comparison, the database engine first matches the manufacturer_id<\/code> value in the outer query to the manufacturer_id<\/code> values returned by the subquery’s SELECT<\/code> statement. It then finds all rows associated with the current manufacturer and returns the average max_weight<\/code> value for that manufacturer, repeating the process for each manufacturer returned by the outer query.<\/p>\n\n\n\nThe following figure shows the results returned by the outer SELECT<\/code> statement. The max_weight<\/code> values are now compared only with the manufacturer-specific averages.<\/p>\n\n\n\n<\/p>\n\n\n\n
You can also use this same subquery in the SELECT<\/code> clause as one of the column expressions. In the following example, I added the subquery after the max_weight<\/code> column and assigned the alias avg_weight<\/code> to the new column:<\/p>\n\n\n\nSELECT manufacturer_id, plane_id, plane, max_weight,\n (SELECT ROUND(AVG(max_weight)) FROM airplanes a2\n WHERE a.manufacturer_id = a2.manufacturer_id) AS avg_weight\nFROM airplanes a\nWHERE max_weight > \n (SELECT ROUND(AVG(max_weight)) FROM airplanes a2\n WHERE a.manufacturer_id = a2.manufacturer_id);<\/pre>\n\n\n\nThe new subquery works the same as in the preceding example. It returns the average weight only for the planes from the current manufacturer, as shown in the following figure.<\/p>\n\n\n\n<\/p>\n\n\n\n
In the previous example, I used a subquery to create a generated column. However, you can use a subquery when creating an even more complex generated column. In the following example, I\u2019ve added a generated column named amt_over<\/code>, which subtracts the average weight returned by the subquery from the weight in the max_weight<\/code> column:<\/p>\n\n\n\nSELECT manufacturer_id, plane_id, plane, max_weight,\n (SELECT ROUND(AVG(max_weight)) FROM airplanes a2\n WHERE a.manufacturer_id = a2.manufacturer_id) AS avg_weight,\n -- subtracts average weight from max weight\n (max_weight - (SELECT ROUND(AVG(max_weight)) FROM airplanes a2\n WHERE a.manufacturer_id = a2.manufacturer_id)) AS amt_over\nFROM airplanes a\nWHERE max_weight > \n (SELECT ROUND(AVG(max_weight)) FROM airplanes a2\n WHERE a.manufacturer_id = a2.manufacturer_id);<\/pre>\n\n\n\nThe avg_weight<\/code> column in the SELECT<\/code> list is a generated column that uses a subquery to return the average weight of the current manufacturer. The amt_over<\/code> column is also a generated column and it uses the same subquery to return the average weight. Only this time, the column subtracts that average from the max_weight<\/code> column to return the amount that exceeds the average.<\/p>\n\n\n\nThe following figure shows the results now returned by the outer SELECT<\/code> statement. As you can see, they include the amt_over<\/code> generated column, which shows the differences in the weights.<\/p>\n\n\n\n<\/p>\n\n\n\n
If you want, you can also retrieve the name of the manufacturer from the manufacturers<\/code> table and include that in your SELECT<\/code> clause, as shown in the following example:<\/p>\n\n\n\nSELECT manufacturer_id, \n (SELECT manufacturer FROM manufacturers m \n WHERE a.manufacturer_id = m.manufacturer_id) manufacturer,\n plane_id, plane, max_weight,\n (SELECT ROUND(AVG(max_weight)) FROM airplanes a2\n WHERE a.manufacturer_id = a2.manufacturer_id) AS avg_weight,\n (max_weight - (SELECT ROUND(AVG(max_weight)) FROM airplanes a2\n WHERE a.manufacturer_id = a2.manufacturer_id)) AS amt_over\nFROM airplanes a\nWHERE max_weight > \n (SELECT ROUND(AVG(max_weight)) FROM airplanes a2\n WHERE a.manufacturer_id = a2.manufacturer_id);<\/pre>\n\n\n\nThis statement is similar to the previous statement except that it adds the manufacturer<\/code> column, a generated column. The new column uses a subquery to retrieve the name of the manufacturer from the manufacturers<\/code> table, based on the manufacturer_id <\/code>value returned by the outer query.<\/p>\n\n\n\nIn some cases, a subquery might not perform as well as other types of constructs. For example, MySQL can often optimize a left outer join better than a subquery that carries out comparable logic. If you\u2019re using a subquery to perform an operation that can be achieved in another way and are concerned about performance, you should consider testing both options under a realistic workload to determine which is the best approach.<\/p>\n\n\n\n
The following figure shows the results with the additional column, which I\u2019ve named manufacturer<\/code>.<\/p>\n\n\n\n<\/p>\n\n\n\n
One other detail I want to point out about using subqueries in the SELECT<\/code> list is that you can include them even if you\u2019re grouping and aggregating data. For instance, the following SELECT<\/code> statement includes a subquery that retrieves the name of the manufacturer associated with each group:<\/p>\n\n\n\nSELECT manufacturer_id, \n (SELECT manufacturer FROM manufacturers m \n WHERE a.manufacturer_id = m.manufacturer_id) manufacturer,\n COUNT(*) AS plane_amt\nFROM airplanes a\nWHERE engine_type = 'piston'\nGROUP BY manufacturer_id\nORDER BY plane_amt DESC;<\/pre>\n\n\n\nThe subquery in this statement is similar to those you\u2019ve seen in other examples, except that now you\u2019re dealing with aggregated data, so the subquery must use a column that is mentioned in the GROUP<\/code> BY<\/code> clause of the outer statement, which it does (the manufacturer_id<\/code> column). The statement returns the results shown in the following figure.<\/p>\n\n\n\n<\/p>\n\n\n\n
As you can see, the data has been grouped based on the manufacturer_id<\/code> column, and the name of the manufacturer is included with each ID. In addition, the number of airplanes with piston engines is provided for each manufacturer, with the results sorted in descending order, based on that amount.<\/p>\n\n\n\nWorking with a row of data<\/h2>\n\n\n\n So far, all the examples in this article have returned scalar values, but your subqueries can also return multiple values, as noted earlier. For example, it might be useful to use a subquery to aggregate a table\u2019s data, calculate specific averages in that data (returned as a single row), and then retrieve rows from the same table that exceed those averages, which is what I\u2019ve done in the following SELECT<\/code> statement:<\/p>\n\n\n\nSELECT \n (SELECT manufacturer FROM manufacturers m \n WHERE a.manufacturer_id = m.manufacturer_id) manufacturer,\n\tplane_id, plane, max_weight, parking_area,\n (SELECT ROUND(AVG(max_weight)) FROM airplanes a2\n WHERE a.manufacturer_id = a2.manufacturer_id) AS avg_weight,\n (SELECT ROUND(AVG(parking_area)) FROM airplanes a2\n WHERE a.manufacturer_id = a2.manufacturer_id) AS avg_area\nFROM airplanes a\nWHERE (max_weight, parking_area) > \n (SELECT ROUND(AVG(max_weight)), ROUND(AVG(parking_area)) \n FROM airplanes a2\n WHERE a.manufacturer_id = a2.manufacturer_id);<\/pre>\n\n\n\nNotice that the WHERE<\/code> clause in the outer statement includes the max_weight<\/code> and parking_area<\/code> columns in parentheses. In MySQL, this is how you create what is called a row constructor,<\/em> a structure that supports simultaneous comparisons of multiple values. In this case, the row constructor is compared to the results from the subquery, which returns two corresponding values. The first value is the average weight, as you saw earlier. The second value returns the average parking area, which is based on the values in the parking_area<\/code> column.<\/p>\n\n\n\nIn both cases, the averages are specific to the current manufacturer_id<\/code> value in the outer query. For the outer query to return a row, the two values in the row constructor must be greater than both corresponding values returned by the subquery. Notice also that the SELECT<\/code> list now includes both the max_weight<\/code> and parking<\/code>_area<\/code> columns, along with the average for each one. The outer statement returns the results shown in the following figure.<\/p>\n\n\n\n<\/p>\n\n\n\n
One thing you might have noticed about the subqueries in the preceding examples, particularly those used in the WHERE<\/code> clauses, is that all the search conditions in those clauses use basic comparison operators, either equal (=<\/code>) or greater than (><\/code>). However, you can use any of the other comparison operators, including special operators such as IN<\/code> and EXISTS<\/code>, as you\u2019ll see in the next section.<\/p>\n\n\n\nWorking with a column of data<\/h2>\n\n\n\n The previous section covered row subqueries. A row subquery can return only a single row, although that row can include one or more columns. This is in contrast to a scalar subquery , which returns only a single row and single column. In this section, we\u2019ll look at the column subquery, which returns only a one column with one or more rows.<\/p>\n\n\n\n
As with row subqueries, column subqueries are often used in the WHERE<\/code> clause when building your search conditions. Also like row subqueries, your search condition must take into account that the subquery is returning more than one value.<\/p>\n\n\n\nFor example, the WHERE<\/code> clause in the following SELECT<\/code> statement uses the IN<\/code> operator to compare current the manufacturer_id<\/code> value in the outer statement with the list of manufacturer_id<\/code> values returned by the subquery:<\/p>\n\n\n\nSELECT manufacturer_id, manufacturer \nFROM manufacturers\nWHERE manufacturer_id IN \n (SELECT DISTINCT manufacturer_id FROM airplanes\n WHERE engine_type = 'piston');<\/pre>\n\n\n\nThe column data returned by the subquery includes only those manufacturers that offer planes with piston engines (as reflected in the current data set). The IN<\/code> operator determines whether the current manufacturer_id<\/code> value is included in that list. If it is, that row is returned, as shown in the following figure.<\/p>\n\n\n\n<\/p>\n\n\n\n
As with many operations in MySQL, you can take different approaches to achieve the same results. For example, you can replace the IN<\/code> operator with an equal comparison operator, followed by the ANY<\/code> keyword, as shown in the following example:<\/p>\n\n\n\nSELECT manufacturer_id, manufacturer \nFROM manufacturers\nWHERE manufacturer_id = ANY \n (SELECT DISTINCT manufacturer_id FROM airplanes\n WHERE engine_type = 'piston');<\/pre>\n\n\n\nYou could have instead used another comparison operator, such as greater than (><\/code>) or lesser than (<<\/code>) or even ALL<\/code> instead of ANY<\/code>. The statement returns the same results as the previous example. In fact, you can also achieve the same results by rewriting the entire statement as an inner join:<\/p>\n\n\n\nSELECT DISTINCT m.manufacturer_id, m.manufacturer\nFROM manufacturers m INNER JOIN airplanes a\n ON m.manufacturer_id = a.manufacturer_id\nWHERE a.engine_type = 'piston';<\/pre>\n\n\n\nI\u2019m not going to go into joins here, but as I mentioned earlier, joins can sometimes provide performance benefits over subqueries, so you should be familiar with how they work and how they differ from subqueries (a topic that could easily warrant its own article).<\/p>\n\n\n\n
With that in mind, be aware that you can also use the NOT<\/code> keyword with some operators to return different results. For example, the WHERE<\/code> clause in the following SELECT<\/code> statement uses the NOT<\/code> IN<\/code> operator ensure the current manufacturer_id<\/code> value is not in the list of values returned by the subquery:<\/p>\n\n\n\nSELECT manufacturer_id, manufacturer \nFROM manufacturers\nWHERE manufacturer_id NOT IN \n (SELECT DISTINCT manufacturer_id FROM airplanes);<\/pre>\n\n\n\nIn this case, the subquery returns a distinct list of all manufacturer_id<\/code> values in the airplanes<\/code> table. The list is then compared to each manufacturer_id<\/code> value in the manufacturers<\/code> table, as specified by the outer query. If the value is not in the list, the search condition evaluates to true and the row is returned. In this way, you can determine which manufacturers are in the manufacturers<\/code> table but are not in the airplanes<\/code> table. The query results are shown in the following figure.<\/p>\n\n\n\n<\/p>\n\n\n\n
Be very careful when using NOT<\/code> IN<\/code> with your subqueries. If the subquery returns a NULL<\/code> value, your WHERE<\/code> expression will never evaluate to true.<\/p>\n\n\n\nAnother operator you can use in the WHERE<\/code> clause is EXISTS<\/code> (and its counterpart NOT<\/code> EXISTS<\/code>). The EXISTS<\/code> operator simply checks whether the subquery returns any rows. If it does, the search condition evaluates to true, otherwise it evaluates to false. For example, the following SELECT<\/code> statement defines similar logic as the preceding example, except that it checks for which manufacturers are included in both tables:<\/p>\n\n\n\nSELECT manufacturer_id, manufacturer \nFROM manufacturers m\nWHERE EXISTS\n (SELECT * FROM airplanes A\n WHERE a.manufacturer_id = m.manufacturer_id);<\/pre>\n\n\n\nNotice that you need only specify the EXISTS<\/code> operator followed by the subquery. If the subquery returns a row for the current manufacturer_id<\/code> value, the search condition evaluates true and the outer query returns a row for that manufacturer. The following figure shows the results returned by the statement.<\/p>\n\n\n\n<\/p>\n\n\n\n
When working with column subqueries, it\u2019s important to understand how to use comparison operators such as IN<\/code>, NOT<\/code> IN<\/code>, ANY<\/code>, ALL<\/code>, and EXISTS<\/code>. If you\u2019re not familiar with them, be sure to refer to the MySQL documentation to learn more.<\/p>\n\n\n\nUsing subqueries in the FROM<\/strong><\/code> clause<\/h2>\n\n\n\nIn addition to rows, columns, and scalar values, subqueries can also return tables, which are referred to as derived tables. Like other subqueries, a table subquery must be enclosed in parentheses. In addition, it must also be assigned an alias, similar to specifying a table alias when building correlated subqueries. For example, the following SELECT<\/code> statement includes a table subquery named total_planes<\/code>, which is included in the FROM<\/code> clause of the outer statement:<\/p>\n\n\n\nSELECT ROUND(AVG(amount), 2) avg_amt\nFROM \n (SELECT manufacturer_id, COUNT(*) AS amount \n FROM airplanes\n GROUP BY manufacturer_id) AS total_planes;<\/pre>\n\n\n\nNotice that the outer statement does not specify a table other than the derived table returned by the subquery. The subquery itself groups the data in the airplanes<\/code> table by the manufacturer_id<\/code> values and then returns the ID and total number of planes in each group. The outer statement then finds the average number of planes across all groups. In this case, the statement returns a value of 5.00<\/code>.<\/p>\n\n\n\nNow let\u2019s look at another example of a table subquery. Although this next example is similar to the previous one in several ways, it includes something you have not seen yet, one subquery nested within another. In this case, I\u2019ve nested a table subquery within another table subquery to group data based on custom categories and then find the average across those groups:<\/p>\n\n\n\n
-- outer SELECT calculates average count across all categories\nSELECT\n ROUND(AVG(amount), 2) AS avg_amt\nFROM\n -- outer subquery aggregates categories and calculates count for each one\n (SELECT category, COUNT(*) amount \n FROM \n -- inner subquery categorizes planes based on parking area\n (SELECT CASE\n WHEN parking_area > 50000 THEN 'A'\n WHEN parking_area >= 20000 THEN 'B'\n WHEN parking_area >= 10000 THEN 'C'\n WHEN parking_area >= 5000 THEN 'D'\n WHEN parking_area >= 1000 THEN 'E'\n ELSE 'F'\n END AS category\n FROM airplanes) AS plane_size\n GROUP BY category\n ORDER BY category) AS plane_cnt;<\/pre>\n\n\n\nThe innermost subquery\u2014the one with the CASE<\/code> expression\u2014assigns one of five category values (A<\/code>, B<\/code>, C<\/code>, D<\/code>, E<\/code>, and
![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/11\/word-image-5.jpeg\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/11\/word-image-6.jpeg\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/11\/word-image-8.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/11\/word-image-7.jpeg\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/11\/word-image-8.jpeg\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/11\/word-image-9.jpeg\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/11\/word-image-10.jpeg\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/11\/word-image-11.jpeg\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/11\/word-image-12.jpeg\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/11\/word-image-13.jpeg\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/11\/word-image-14.jpeg\")
<\/figure>\n\n\n\n