Detailed example of MySQL subquery

Subquery Classification

Classification by returned result set

Subqueries are divided into four types according to the different result sets returned: table subqueries, row subqueries, column subqueries, and scalar subqueries. Table subquery: The returned result set is a set of rows, N rows and N columns (N>=1). Table subqueries are often used in the FROM clause of a parent query. Row subquery: The returned result set is a set of columns, one row with N columns (N>=1). Row subqueries can be used in the FROM clause and WHERE clause of a query. Column subquery: The returned result set is a set of rows, with N rows and one column (N>=1). Scalar subquery: The returned result set is a scalar set, one row and one column, that is, a scalar value. A scalar subquery can be used anywhere a scalar expression can be specified.

By definition, every scalar subquery is also a row subquery and a column subquery, but not vice versa; and every row subquery and column subquery is also a table subquery, but not vice versa.

According to the calling method of returning the result

Subqueries can be divided into where-type subqueries, from-type subqueries, and exists-type subqueries according to the calling method for returning result sets.

Where-type subquery: (using the inner query result as the comparison condition of the outer query) Definition: Where-type subquery uses the inner query result as the condition of the outer query. From subquery: (the inner query result is used for the outer query again) Definition: From subquery treats the result of the subquery (a table in memory) as a temporary table and then processes it. Exists subquery: (take the outer query result to the inner layer to see if the inner query is established) Definition: The exists subquery loops through the outer table and then performs an inner query on the inner table. Similar to in(), but there are some differences. It mainly depends on the size difference between the two tables. If the subquery table is large, use exists (inner index); if the subquery table is small, use in (outer index);

Using subquery principles

1. A subquery must be enclosed in parentheses.

2. Place the subquery to the right of the comparison condition to increase readability. The subquery does not contain an ORDER BY clause. Only one ORDER BY clause can be used for a SELECT statement, and if specified it must be placed last in the main SELECT statement.

3. Two types of comparison conditions can be used in subqueries: single-row operators (>, =, >=, <, <>, <=) and multi-row operators (IN, ANY, ALL).

Example Analysis

Create a test table

CREATE TABLE PLAYERS
  (PLAYERNO INTEGER NOT NULL,
  NAME CHAR(15) NOT NULL,
  INITIALS CHAR(3) NOT NULL,
  BIRTH_DATE DATE ,
  SEX CHAR(1) NOT NULL,
  JOINED SMALLINT NOT NULL,
  STREET VARCHAR(30) NOT NULL,
  HOUSENO CHAR(4) ,
  POSTCODE CHAR(6) ,
  TOWN VARCHAR(30) NOT NULL,
  PHONENO CHAR(13) ,
  LEAGUENO CHAR(4) ,
  PRIMARY KEY (PLAYERNO));
 
CREATE TABLE PENALTIES
    (PAYMENTNO INTEGER NOT NULL,
     PLAYERNO INTEGER NOT NULL,
     PAYMENT_DATE DATE NOT NULL,
     AMOUNT DECIMAL(7,2) NOT NULL,
     PRIMARY KEY (PAYMENTNO));

Note: Table players is the basic table of player information, and table penalties is a list of player information that has a record of fines.

Insert test data

INSERT INTO PLAYERS VALUES (2, 'Everett', 'R', '1948-09-01', 'M', 1975, 'Stoney Road','43', '3575NH', 'Stratford', '070-237893', '2411');
INSERT INTO PLAYERS VALUES (6, 'Parmenter', 'R', '1964-06-25', 'M', 1977, 'Haseltine Lane','80', '1234KK', 'Stratford', '070-476537', '8467');
INSERT INTO PLAYERS VALUES (7, 'Wise', 'GWS', '1963-05-11', 'M', 1981, 'Edgecombe Way','39', '9758VB', 'Stratford', '070-347689', NULL);
INSERT INTO PLAYERS VALUES (8, 'Newcastle', 'B', '1962-07-08', 'F', 1980, 'Station Road','4', '6584WO', 'Inglewood', '070-458458', '2983');
INSERT INTO PLAYERS VALUES (27, 'Collins', 'DD', '1964-12-28', 'F', 1983, 'Long Drive','804', '8457DK', 'Eltham', '079-234857', '2513');
INSERT INTO PLAYERS VALUES (28, 'Collins', 'C', '1963-06-22', 'F', 1983, 'Old Main Road','10', '1294QK', 'Midhurst', '010-659599', NULL);
INSERT INTO PLAYERS VALUES (39, 'Bishop', 'D', '1956-10-29', 'M', 1980, 'Eaton Square','78', '9629CD', 'Stratford', '070-393435', NULL);
INSERT INTO PLAYERS VALUES (44, 'Baker', 'E', '1963-01-09', 'M', 1980, 'Lewis Street','23', '4444LJ', 'Inglewood', '070-368753', '1124');
INSERT INTO PLAYERS VALUES (57, 'Brown', 'M', '1971-08-17', 'M', 1985, 'Edgecombe Way','16', '4377CB', 'Stratford', '070-473458', '6409');
INSERT INTO PLAYERS VALUES (83, 'Hope', 'PK', '1956-11-11', 'M', 1982, 'Magdalene Road','16A', '1812UP', 'Stratford', '070-353548', '1608');
INSERT INTO PLAYERS VALUES (95, 'Miller', 'P', '1963-05-14', 'M', 1972, 'High Street','33A', '5746OP', 'Douglas', '070-867564', NULL);
INSERT INTO PLAYERS VALUES (100, 'Parmenter', 'P', '1963-02-28', 'M', 1979, 'Haseltine Lane','80', '6494SG', 'Stratford', '070-494593', '6524');
INSERT INTO PLAYERS VALUES (104, 'Moorman', 'D', '1970-05-10', 'F', 1984, 'Stout Street','65', '9437AO', 'Eltham', '079-987571', '7060');
INSERT INTO PLAYERS VALUES (112, 'Bailey', 'IP', '1963-10-01', 'F', 1984, 'Vixen Road','8', '6392LK', 'Plymouth', '010-548745', '1319');
 
INSERT INTO PENALTIES VALUES (1, 6, '1980-12-08',100);
INSERT INTO PENALTIES VALUES (2, 44, '1981-05-05', 75);
INSERT INTO PENALTIES VALUES (3, 27, '1983-09-10',100);
INSERT INTO PENALTIES VALUES (4,104, '1984-12-08', 50);
INSERT INTO PENALTIES VALUES (5, 44, '1980-12-08', 25);
INSERT INTO PENALTIES VALUES (6, 8, '1980-12-08', 25);
INSERT INTO PENALTIES VALUES (7, 44, '1982-12-30', 30);
INSERT INTO PENALTIES VALUES (8, 27, '1984-11-12', 75);

Table subquery example

For example: Get the number of male players whose number is less than 10

mysql> select playerno from (
  select playerno, sex 
  from players 
  where playerno < 10) 
as players10 where sex='M';

Row Subquery Example

For example: Get the player number of player No. 100 who has the same gender and lives in the same city.

mysql> select playerno 
from players 
where (sex, town) = (
  select sex, town 
  from players 
  where playerno = 100);

Explanation: The result of the subquery is a row with two values: ('M','stratford'). This value is compared to a row expression (sex, town).

Scalar subquery example

Almost anywhere you can specify a scalar expression, you can use a scalar subquery. For example: Get the number of the player who was born in the same year as player No. 27

mysql> select playerno 
from players 
where year(birth_date) = 
  (select year(birth_date) 
  from players 
  where playerno = 27) 
and playerno <> 27;

The above statement is equivalent to:

mysql> select playerno from players where year(birth_date) = 1964 and playerno <> 27;

Subquery Example

Because the result set returned by the column subquery is one column with N rows, operators such as = > < >= <= <> that compare scalar results cannot be used directly. The IN, ANY (SOME), and ALL operators can be used in column subqueries: IN: within the specified items, same as IN (item 1, item 2, ...). ANY: Used in conjunction with a comparison operator, the ANY keyword must be followed by a comparison operator, indicating that if the comparison with any value returned by the subquery is TRUE, TRUE is returned. SOME: An alias for ANY, less commonly used. ALL: Used in conjunction with a comparison operator, the ALL keyword must be placed after a comparison operator, indicating that if all values ​​returned by the subquery are compared to TRUE, TRUE is returned.

Example 1 (in): Get the player number, name and city of all players whose gender is female.

mysql> select playerno, name, town 
from players 
where player no in 
  (select playerno 
  from players 
  where sex = 'F');

Example 2 (any): Get the number, date and city of residence of all players who are at least one player younger than the same city.

mysql> select playerno, birth_date, town 
from players as p1 
where birth_date > any 
  (select birth_date 
  from players as p2 
  where p1.town = p2.town);

Example 3 (all): Get the oldest player's number, name and birthday. (i.e. players whose birthdate values ​​are less than or equal to all other players)

mysql> select playerno, name, birth_date 
from players 
where birth_date <= all 
  (select birth_date 
  from players);

The XISTS keyword indicates existence. When the EXISTS keyword is used, the inner query statement does not return the queried records, but returns a true or false value. If the inner query statement finds records that meet the conditions, the value of the EXISTS statement is True as long as the subquery returns at least one value. Returns true, otherwise returns false. When the returned value is true, the outer query statement will perform a query, otherwise it will not perform a query. NOT EXISTS is just the opposite. The usage of exists is similar to in (), but there are still differences between them. It mainly depends on the size difference between the two tables. If the subquery table is large, use exists (inner index); if the subquery table is small, use in (outer index);

Example 1 (exists): Get the names and initials of players who have paid at least one fine.

mysql> select name, initials 
from players 
where exists 
  (select * from penalties 
  where playerno = players.playerno);

Example 2 (not exists): Get the names and initials of players who have never been fined.

mysql> select name, initials 
from players 
where not exists 
  (select * from penalties 
  where playerno = players.playerno);

The above is a detailed example of MySQL subquery. For more information about MySQL subquery, please pay attention to other related articles on 123WORDPRESS.COM!