SQL Example 9--sub-query

<title>SQL Example 9– subquery</title> SQL Example 9– subquery

Database Preparation

Create Table Student(IDintAuto_incrementPrimary Key,name varchar(10));Create Table scores(IDintAuto_incrementPRIMARY Key, scoreint default0,Foreign Key(ID)ReferencesStudent (ID) on Delete Cascade  on Update Cascade);

A subquery can return a scalar, a row, a column, or a subquery

SELECT * from t1 where column1 = (select Column1 from T2);

The first select is called an outer query, and the second query is called an inner query, also called a subquery

Insert  intoStudent (name)Values(' A '),(' B '),(' C '),(' d '),(' E '),(' F '),(' G '),(' h '),(' I '),(' J '),(' K '),(' l '),(' m '),(' n ');Insert  intoScores (score)Values(60), (65), (71), (76), (88), (94), (68), (94), (66), (91), (61), (63), (92), (86);

Select avg  from scores;

| AVG (Score) |+------------+|    76.7857 |

Select Round (avg from scores;

| Round (AVG (score), 2) |+----------------------+|                76.79 |

Select  from where score >= 76.79;

| ID |+----+|  5 | |  6 | |  8 | | 10 | | 13 | | 14 |

Now let's merge the top two queries into one

Select  from where score >= (select Round (avg from scores);

| ID |+----+|  5 | |  6 | |  8 | | 10 | | 13 | | 14 |

This is a subquery.

A subquery returns multiple values using any, some, and all modifiers (any and some equivalents)

Select  from where score >= (selectfromwhere ID >= 9);

ERROR 1242 (21000): subquery returns more than 1 row

Select  from where ID >= 9;

| Score |+-------+|    | |    | |    | |    | |    | |    86 |

Select  from where  All (Selectfromwhere ID >= 9);

| ID | Score |+----+-------+|  6 |    94 | |  8 |    94 | | |    92 |

Select  from where  any (Selectfromwhere ID >= 9);

| ID | Score |+----+-------+|  2 |    | |  3 |    | |  4 |    | |  5 |    | |  6 |    94 | |  7 |    | |  8 |    94 | |  9 |    66 | | Ten |    91 | | One |    61 | | |    63 | | |    92 | | |    86 |

select  * from  Scores where  score select  score from  scores where  ID >= 9); 

Select  from where  not inch (Selectfromwhere ID >= 9); Select  from where  All (Selectfromwhere ID >= 9);

| ID | Score |+----+-------+|  1 |    | |  2 |    | |  3 |    | |  4 |    | |  5 |    | |  6 |    94 | |  7 |    | |  8 |    94 |

Select exists (Selectfromwhere ID >= 9);         # results returned 1selectnotexists (selectfromwhere ID >= 9);     # no results returned 1

SQL Example 9--sub-query