MySQL Subqueries

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

MySQL Subqueries

Subqueries

A subquery is a SQL query nested inside a larger query.

  • A subquery may occur in :
    • - A SELECT clause
    • - A FROM clause
    • - A WHERE clause
  • In MySQL subquery can be nested inside a SELECT, INSERT, UPDATE, DELETE, SET, or DO statement or inside another subquery.
  • A subquery is usually added within the WHERE Clause of another SQL SELECT statement.
  • You can use the comparison operators, such as >, <, or =. The comparison operator can also be a multiple-row operator, such as IN, ANY, SOME, or ALL.
  • A subquery can be treated as an inner query, which is a SQL query placed as a part of another query called as outer query.
  • The inner query executes first before its parent query so that the results of the inner query can be passed to the outer query.

Contents:

  • Subquery Syntax
  • MySQL Subquery Example
  • Subqueries : Guidelines and Types of Subqueries
  • MySQL Subquery as Scalar Operand
  • MySQL Subqueries : Using Comparisons
  • MySQL Subqueries with ALL, ANY, IN, or SOME
  • MySQL Row Subqueries

MySQL Subqueries with EXISTS or NOT EXISTS

MySQL Correlated Subqueries

MySQL Subqueries in the FROM Clause

Subquery Syntax :

mysql subquery syntax

  • The subquery (inner query) executes once before the main query (outer query) executes.
  • The main query (outer query) use the subquery result.

Subquery syntax as specified by the SQL standard and supported in MySQL

DELETE FROM t1 

    WHERE s11 > ANY  

     (SELECT COUNT(*) /* no hint */ FROM t2   

       WHERE NOT EXISTS    

        (SELECT * FROM t3     

          WHERE ROW(5*t2.s1,77)=      

            (SELECT 50,11*s1 FROM t4 UNION SELECT 50,77 FROM 

              (SELECT * FROM t5) AS t5)));

A subquery can return a scalar (a single value), a single row, a single column, or a table (one or more rows of one or more columns). These are called scalar, column, row, and table subqueries.

MySQL Subquery Example :

Using a subquery, list the name of the employees, paid more than 'Alexander' from emp_details .

EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
100 Steven King SKING 515.123.4567 17-Jun-87 AD_PRES 24000     90
101 Neena Kochhar NKOCHHAR 515.123.4568 21-Sep-89 AD_VP 17000   100 90
102 Lex De Haan LDEHAAN 515.123.4569 13-Jan-93 AD_VP 17000   100 90
103 Alexander Hunold AHUNOLD 590.423.4567 3-Jan-90 IT_PROG 9000   102 60
104 Bruce Ernst BERNST 590.423.4568 21-May-91 IT_PROG 6000   103 60
105 David Austin DAUSTIN 590.423.4569 25-Jun-97 IT_PROG 4800   103 60
106 Valli Pataballa VPATABAL 590.423.4560 5-Feb-98 IT_PROG 4800   103 60
107 Diana Lorentz DLORENTZ 590.423.5567 7-Feb-99 IT_PROG 4200   103 60
108 Nancy Greenberg NGREENBE 515.124.4569 17-Aug-94 FI_MGR 12000   101 100
109 Daniel Faviet DFAVIET 515.124.4169 16-Aug-94 FI_ACCOUNT 9000   108 100
110 John Chen JCHEN 515.124.4269 28-Sep-97 FI_ACCOUNT 8200   108 100
111 Ismael Sciarra ISCIARRA 515.124.4369 30-Sep-97 FI_ACCOUNT 7700   108 100
112 Jose Manuel Urman JMURMAN 515.124.4469 7-Mar-98 FI_ACCOUNT 7800   108 100
113 Luis Popp LPOPP 515.124.4567 7-Dec-99 FI_ACCOUNT 6900   108 100
114 Den Raphaely DRAPHEAL 515.127.4561 7-Dec-94 PU_MAN 11000   100 30
115 Alexander Khoo AKHOO 515.127.4562 18-May-95 PU_CLERK 3100   114 30
116 Shelli Baida SBAIDA 515.127.4563 24-Dec-97 PU_CLERK 2900   114 30
117 Sigal Tobias STOBIAS 515.127.4564 24-Jul-97 PU_CLERK 2800   114 30
118 Guy Himuro GHIMURO 515.127.4565 15-Nov-98 PU_CLERK 2600   114 30
119 Karen Colmenares KCOLMENA 515.127.4566 10-Aug-99 PU_CLERK 2500   114 30
120 Matthew Weiss MWEISS 650.123.1234 18-Jul-96 ST_MAN 8000   100 50
121 Adam Fripp AFRIPP 650.123.2234 10-Apr-97 ST_MAN 8200   100 50
122 Payam Kaufling PKAUFLIN 650.123.3234 1-May-95 ST_MAN 7900   100 50
123 Shanta Vollman SVOLLMAN 650.123.4234 10-Oct-97 ST_MAN 6500   100 50
124 Kevin Mourgos KMOURGOS 650.123.5234 16-Nov-99 ST_MAN 5800   100 50
125 Julia Nayer JNAYER 650.124.1214 16-Jul-97 ST_CLERK 3200   120 50
126 Irene Mikkilineni IMIKKILI 650.124.1224 28-Sep-98 ST_CLERK 2700   120 50
127 James Landry JLANDRY 650.124.1334 14-Jan-99 ST_CLERK 2400   120 50
128 Steven Markle SMARKLE 650.124.1434 8-Mar-00 ST_CLERK 2200   120 50
129 Laura Bissot LBISSOT 650.124.5234 20-Aug-97 ST_CLERK 3300   121 50
130 Mozhe Atkinson MATKINSO 650.124.6234 30-Oct-97 ST_CLERK 2800   121 50
131 James Marlow JAMRLOW 650.124.7234 16-Feb-97 ST_CLERK 2500   121 50
132 TJ Olson TJOLSON 650.124.8234 10-Apr-99 ST_CLERK 2100   121 50
133 Jason Mallin JMALLIN 650.127.1934 14-Jun-96 ST_CLERK 3300   122 50
134 Michael Rogers MROGERS 650.127.1834 26-Aug-98 ST_CLERK 2900   122 50
135 Ki Gee KGEE 650.127.1734 12-Dec-99 ST_CLERK 2400   122 50
136 Hazel Philtanker HPHILTAN 650.127.1634 6-Feb-00 ST_CLERK 2200   122 50
137 Renske Ladwig RLADWIG 650.121.1234 14-Jul-95 ST_CLERK 3600   123 50
138 Stephen Stiles SSTILES 650.121.2034 26-Oct-97 ST_CLERK 3200   123 50
139 John Seo JSEO 650.121.2019 12-Feb-98 ST_CLERK 2700   123 50
140 Joshua Patel JPATEL 650.121.1834 6-Apr-98 ST_CLERK 2500   123 50
141 Trenna Rajs TRAJS 650.121.8009 17-Oct-95 ST_CLERK 3500   124 50
142 Curtis Davies CDAVIES 650.121.2994 29-Jan-97 ST_CLERK 3100   124 50
143 Randall Matos RMATOS 650.121.2874 15-Mar-98 ST_CLERK 2600   124 50
144 Peter Vargas PVARGAS 650.121.2004 9-Jul-98 ST_CLERK 2500   124 50
145 John Russell JRUSSEL 011.44.1344.429268 1-Oct-96 SA_MAN 14000 0.4 100 80
146 Karen Partners KPARTNER 011.44.1344.467268 5-Jan-97 SA_MAN 13500 0.3 100 80
147 Alberto Errazuriz AERRAZUR 011.44.1344.429278 10-Mar-97 SA_MAN 12000 0.3 100 80
148 Gerald Cambrault GCAMBRAU 011.44.1344.619268 15-Oct-99 SA_MAN 11000 0.3 100 80
149 Eleni Zlotkey EZLOTKEY 011.44.1344.429018 29-Jan-00 SA_MAN 10500 0.2 100 80
150 Peter Tucker PTUCKER 011.44.1344.129268 30-Jan-97 SA_REP 10000 0.3 145 80
151 David Bernstein DBERNSTE 011.44.1344.345268 24-Mar-97 SA_REP 9500 0.25 145 80
152 Peter Hall PHALL 011.44.1344.478968 20-Aug-97 SA_REP 9000 0.25 145 80
153 Christopher Olsen COLSEN 011.44.1344.498718 30-Mar-98 SA_REP 8000 0.2 145 80
154 Nanette Cambrault NCAMBRAU 011.44.1344.987668 9-Dec-98 SA_REP 7500 0.2 145 80
155 Oliver Tuvault OTUVAULT 011.44.1344.486508 23-Nov-99 SA_REP 7000 0.15 145 80
156 Janette King JKING 011.44.1345.429268 30-Jan-96 SA_REP 10000 0.35 146 80
157 Patrick Sully PSULLY 011.44.1345.929268 4-Mar-96 SA_REP 9500 0.35 146 80
158 Allan McEwen AMCEWEN 011.44.1345.829268 1-Aug-96 SA_REP 9000 0.35 146 80
159 Lindsey Smith LSMITH 011.44.1345.729268 10-Mar-97 SA_REP 8000 0.3 146 80
160 Louise Doran LDORAN 011.44.1345.629268 15-Dec-97 SA_REP 7500 0.3 146 80
161 Sarath Sewall SSEWALL 011.44.1345.529268 3-Nov-98 SA_REP 7000 0.25 146 80
162 Clara Vishney CVISHNEY 011.44.1346.129268 11-Nov-97 SA_REP 10500 0.25 147 80
163 Danielle Greene DGREENE 011.44.1346.229268 19-Mar-99 SA_REP 9500 0.15 147 80
164 Mattea Marvins MMARVINS 011.44.1346.329268 24-Jan-00 SA_REP 7200 0.1 147 80
165 David Lee DLEE 011.44.1346.529268 23-Feb-00 SA_REP 6800 0.1 147 80
166 Sundar Ande SANDE 011.44.1346.629268 24-Mar-00 SA_REP 6400 0.1 147 80
167 Amit Banda ABANDA 011.44.1346.729268 21-Apr-00 SA_REP 6200 0.1 147 80
168 Lisa Ozer LOZER 011.44.1343.929268 11-Mar-97 SA_REP 11500 0.25 148 80
169 Harrison Bloom HBLOOM 011.44.1343.829268 23-Mar-98 SA_REP 10000 0.2 148 80
170 Tayler Fox TFOX 011.44.1343.729268 24-Jan-98 SA_REP 9600 0.2 148 80
171 William Smith WSMITH 011.44.1343.629268 23-Feb-99 SA_REP 7400 0.15 148 80
172 Elizabeth Bates EBATES 011.44.1343.529268 24-Mar-99 SA_REP 7300 0.15 148 80
173 Sundita Kumar SKUMAR 011.44.1343.329268 21-Apr-00 SA_REP 6100 0.1 148 80
174 Ellen Abel EABEL 011.44.1644.429267 11-May-96 SA_REP 11000 0.3 149 80
175 Alyssa Hutton AHUTTON 011.44.1644.429266 19-Mar-97 SA_REP 8800 0.25 149 80
176 Jonathon Taylor JTAYLOR 011.44.1644.429265 24-Mar-98 SA_REP 8600 0.2 149 80
177 Jack Livingston JLIVINGS 011.44.1644.429264 23-Apr-98 SA_REP 8400 0.2 149 80
178 Kimberely Grant KGRANT 011.44.1644.429263 24-May-99 SA_REP 7000 0.15 149  
179 Charles Johnson CJOHNSON 011.44.1644.429262 4-Jan-00 SA_REP 6200 0.1 149 80
180 Winston Taylor WTAYLOR 650.507.9876 24-Jan-98 SH_CLERK 3200   120 50
181 Jean Fleaur JFLEAUR 650.507.9877 23-Feb-98 SH_CLERK 3100   120 50
182 Martha Sullivan MSULLIVA 650.507.9878 21-Jun-99 SH_CLERK 2500   120 50
183 Girard Geoni GGEONI 650.507.9879 3-Feb-00 SH_CLERK 2800   120 50
184 Nandita Sarchand NSARCHAN 650.509.1876 27-Jan-96 SH_CLERK 4200   121 50
185 Alexis Bull ABULL 650.509.2876 20-Feb-97 SH_CLERK 4100   121 50
186 Julia Dellinger JDELLING 650.509.3876 24-Jun-98 SH_CLERK 3400   121 50
187 Anthony Cabrio ACABRIO 650.509.4876 7-Feb-99 SH_CLERK 3000   121 50
188 Kelly Chung KCHUNG 650.505.1876 14-Jun-97 SH_CLERK 3800   122 50
189 Jennifer Dilly JDILLY 650.505.2876 13-Aug-97 SH_CLERK 3600   122 50
190 Timothy Gates TGATES 650.505.3876 11-Jul-98 SH_CLERK 2900   122 50
191 Randall Perkins RPERKINS 650.505.4876 19-Dec-99 SH_CLERK 2500   122 50
192 Sarah Bell SBELL 650.501.1876 4-Feb-96 SH_CLERK 4000   123 50
193 Britney Everett BEVERETT 650.501.2876 3-Mar-97 SH_CLERK 3900   123 50
194 Samuel McCain SMCCAIN 650.501.3876 1-Jul-98 SH_CLERK 3200   123 50
195 Vance Jones VJONES 650.501.4876 17-Mar-99 SH_CLERK 2800   123 50
196 Alana Walsh AWALSH 650.507.9811 24-Apr-98 SH_CLERK 3100   124 50
197 Kevin Feeney KFEENEY 650.507.9822 23-May-98 SH_CLERK 3000   124 50
198 Donald OConnell DOCONNEL 650.507.9833 21-Jun-99 SH_CLERK 2600   124 50
199 Douglas Grant DGRANT 650.507.9844 13-Jan-00 SH_CLERK 2600   124 50
200 Jennifer Whalen JWHALEN 515.123.4444 17-Sep-87 AD_ASST 4400   101 10
201 Michael Hartstein MHARTSTE 515.123.5555 17-Feb-96 MK_MAN 13000   100 20
202 Pat Fay PFAY 603.123.6666 17-Aug-97 MK_REP 6000   201 20
203 Susan Mavris SMAVRIS 515.123.7777 7-Jun-94 HR_REP 6500   101 40
204 Hermann Baer HBAER 515.123.8888 7-Jun-94 PR_REP 10000   101 70
205 Shelley Higgins SHIGGINS 515.123.8080 7-Jun-94 AC_MGR 12000   101 110
206 William Gietz WGIETZ 515.123.8181 7-Jun-94 AC_ACCOUNT 8300   205 110

mysql subquery example

5 rows in set (0.00 sec)

Subqueries: Guidelines

There are some guidelines to consider when using subqueries :
- A subquery must be enclosed in parentheses. 
- Use single-row operators with single-row subqueries, and use multiple-row operators with multiple-row subqueries.
- If a subquery (inner query) returns a null value to the outer query, the outer query will not return any rows when using certain comparison operators in a WHERE clause.

Types of Subqueries

  • The Subquery as Scalar Operand
  • Comparisons using Subqueries
  • Subqueries with ALL, ANY, IN, or SOME
  • Row Subqueries
  • Subqueries with EXISTS or NOT EXISTS
  • Correlated Subqueries
  • Subqueries in the FROM Clause

MySQL Subquery as Scalar Operand

A scalar subquery is a subquery that returns exactly one column value from one row. A scalar subquery is a simple operand, and you can use it almost anywhere a single column value or literal is legal. If the subquery returns 0 rows then the value of scalar subquery expression in NULL and if the subquery returns more than one row then MySQL returns an error.
There is some situation where a scalar subquery cannot be used. If a statement permits only a literal value, you cannot use a subquery. For example, LIMIT requires literal integer arguments, and LOAD DATA INFILE requires a literal string file name. You cannot use subqueries to supply these values.

Example : MySQL Subquery as Scalar Operand

mysql> SELECT employee_id, last_name,

(CASE WHEN department_id=(

SELECT department_id from departments WHERE location_id=2500)

THEN 'Canada' ELSE 'USA' END)

location FROM employees;

+-------------+-------------+----------+

| employee_id | last_name   | location |

+-------------+-------------+----------+

|         100 | King        | USA      |

|         101 | Kochhar     | USA      |

|         102 | De Haan     | USA      |

|         103 | Hunold      | USA      |

|         104 | Ernst       | USA      |

|         105 | Austin      | USA      |

| - - - - - - - - - - - - - - - - - - -|

| - - - - - - - - - - - - - - - - - - -|

107 rows in set (0.00 sec)

MySQL Subqueries : Using Comparisons

A subquery can be used before or after any of the comparison operators. The subquery can return at most one value. The value can be the result of an arithmetic expression or a column function. SQL then compares the value that results from the subquery with the value on the other side of the comparison operator. You can use the following comparison operators :

Operator

Description

=

Equal to

Greater than

>=

Greater than or equal to

Less than

<=

Less than or equal to

!=

Not equal to

<> 

Not equal to

<=>

NULL-safe equal to operator

For example, suppose you want to find the employee id, first_name, last_name, and salaries for employees whose average salary is higher than the average salary throughout the company.

mysql comparison operator

mysql> SELECT employee_id,first_name,last_name,salary

       FROM employees WHERE salary >

                        (SELECT AVG(SALARY) FROM employees);

+-------------+-------------+------------+----------+

| employee_id | first_name  | last_name  | salary   |

+-------------+-------------+------------+----------+

|         100 | Steven      | King       | 24000.00 |

|         101 | Neena       | Kochhar    | 17000.00 |

|         102 | Lex         | De Haan    | 17000.00 |

|         103 | Alexander   | Hunold     |  9000.00 |

|         108 | Nancy       | Greenberg  | 12000.00 |

|         109 | Daniel      | Faviet     |  9000.00 |

|         120 | Matthew     | Weiss      |  8000.00 |

|         121 | Adam        | Fripp      |  8200.00 |

|         122 | Payam       | Kaufling   |  7900.00 |

|- - - - - - - - - - - - - - - - - - - - - - - - - -|

|- - - - - - - - - - - - - - - - - - - - - - - - - -|

+-------------+-------------+------------+----------+

51 rows in set (0.00 sec)

MySQL Subqueries with ALL, ANY, IN, or SOME

You can use a subquery after a comparison operator, followed by the keyword ALL, ANY, or SOME.

The ALL operator compares value to every value returned by the subquery. Therefore ALL operator (which must follow a comparison operator) returns TRUE if the comparison is TRUE for ALL of the values in the column that the subquery returns.

Syntax :

operand comparison_operator ALL (subquery)

NOT IN is an alias for <> ALL. Thus, these two statements are the same :

SELECT c1 FROM t1 WHERE c1 <> ALL (SELECT c1 FROM t2);

SELECT c1 FROM t1 WHERE c1 NOT IN (SELECT c1 FROM t2);

Example : MySQL Subquery, ALL operator

The following query selects the department with the highest average salary. The subquery finds the average salary for each department, and then the main query selects the department with the highest average salary.

EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
100 Steven King SKING 515.123.4567 17-Jun-87 AD_PRES 24000     90
101 Neena Kochhar NKOCHHAR 515.123.4568 21-Sep-89 AD_VP 17000   100 90
102 Lex De Haan LDEHAAN 515.123.4569 13-Jan-93 AD_VP 17000   100 90
103 Alexander Hunold AHUNOLD 590.423.4567 3-Jan-90 IT_PROG 9000   102 60
104 Bruce Ernst BERNST 590.423.4568 21-May-91 IT_PROG 6000   103 60
105 David Austin DAUSTIN 590.423.4569 25-Jun-97 IT_PROG 4800   103 60
106 Valli Pataballa VPATABAL 590.423.4560 5-Feb-98 IT_PROG 4800   103 60
107 Diana Lorentz DLORENTZ 590.423.5567 7-Feb-99 IT_PROG 4200   103 60
108 Nancy Greenberg NGREENBE 515.124.4569 17-Aug-94 FI_MGR 12000   101 100
109 Daniel Faviet DFAVIET 515.124.4169 16-Aug-94 FI_ACCOUNT 9000   108 100
110 John Chen JCHEN 515.124.4269 28-Sep-97 FI_ACCOUNT 8200   108 100
111 Ismael Sciarra ISCIARRA 515.124.4369 30-Sep-97 FI_ACCOUNT 7700   108 100
112 Jose Manuel Urman JMURMAN 515.124.4469 7-Mar-98 FI_ACCOUNT 7800   108 100
113 Luis Popp LPOPP 515.124.4567 7-Dec-99 FI_ACCOUNT 6900   108 100
114 Den Raphaely DRAPHEAL 515.127.4561 7-Dec-94 PU_MAN 11000   100 30
115 Alexander Khoo AKHOO 515.127.4562 18-May-95 PU_CLERK 3100   114 30
116 Shelli Baida SBAIDA 515.127.4563 24-Dec-97 PU_CLERK 2900   114 30
117 Sigal Tobias STOBIAS 515.127.4564 24-Jul-97 PU_CLERK 2800   114 30
118 Guy Himuro GHIMURO 515.127.4565 15-Nov-98 PU_CLERK 2600   114 30
119 Karen Colmenares KCOLMENA 515.127.4566 10-Aug-99 PU_CLERK 2500   114 30
120 Matthew Weiss MWEISS 650.123.1234 18-Jul-96 ST_MAN 8000   100 50
121 Adam Fripp AFRIPP 650.123.2234 10-Apr-97 ST_MAN 8200   100 50
122 Payam Kaufling PKAUFLIN 650.123.3234 1-May-95 ST_MAN 7900   100 50
123 Shanta Vollman SVOLLMAN 650.123.4234 10-Oct-97 ST_MAN 6500   100 50
124 Kevin Mourgos KMOURGOS 650.123.5234 16-Nov-99 ST_MAN 5800   100 50
125 Julia Nayer JNAYER 650.124.1214 16-Jul-97 ST_CLERK 3200   120 50
126 Irene Mikkilineni IMIKKILI 650.124.1224 28-Sep-98 ST_CLERK 2700   120 50
127 James Landry JLANDRY 650.124.1334 14-Jan-99 ST_CLERK 2400   120 50
128 Steven Markle SMARKLE 650.124.1434 8-Mar-00 ST_CLERK 2200   120 50
129 Laura Bissot LBISSOT 650.124.5234 20-Aug-97 ST_CLERK 3300   121 50
130 Mozhe Atkinson MATKINSO 650.124.6234 30-Oct-97 ST_CLERK 2800   121 50
131 James Marlow JAMRLOW 650.124.7234 16-Feb-97 ST_CLERK 2500   121 50
132 TJ Olson TJOLSON 650.124.8234 10-Apr-99 ST_CLERK 2100   121 50
133 Jason Mallin JMALLIN 650.127.1934 14-Jun-96 ST_CLERK 3300   122 50
134 Michael Rogers MROGERS 650.127.1834 26-Aug-98 ST_CLERK 2900   122 50
135 Ki Gee KGEE 650.127.1734 12-Dec-99 ST_CLERK 2400   122 50
136 Hazel Philtanker HPHILTAN 650.127.1634 6-Feb-00 ST_CLERK 2200   122 50
137 Renske Ladwig RLADWIG 650.121.1234 14-Jul-95 ST_CLERK 3600   123 50
138 Stephen Stiles SSTILES 650.121.2034 26-Oct-97 ST_CLERK 3200   123 50
139 John Seo JSEO 650.121.2019 12-Feb-98 ST_CLERK 2700   123 50
140 Joshua Patel JPATEL 650.121.1834 6-Apr-98 ST_CLERK 2500   123 50
141 Trenna Rajs TRAJS 650.121.8009 17-Oct-95 ST_CLERK 3500   124 50
142 Curtis Davies CDAVIES 650.121.2994 29-Jan-97 ST_CLERK 3100   124 50
143 Randall Matos RMATOS 650.121.2874 15-Mar-98 ST_CLERK 2600   124 50
144 Peter Vargas PVARGAS 650.121.2004 9-Jul-98 ST_CLERK 2500   124 50
145 John Russell JRUSSEL 011.44.1344.429268 1-Oct-96 SA_MAN 14000 0.4 100 80
146 Karen Partners KPARTNER 011.44.1344.467268 5-Jan-97 SA_MAN 13500 0.3 100 80
147 Alberto Errazuriz AERRAZUR 011.44.1344.429278 10-Mar-97 SA_MAN 12000 0.3 100 80
148 Gerald Cambrault GCAMBRAU 011.44.1344.619268 15-Oct-99 SA_MAN 11000 0.3 100 80
149 Eleni Zlotkey EZLOTKEY 011.44.1344.429018 29-Jan-00 SA_MAN 10500 0.2 100 80
150 Peter Tucker PTUCKER 011.44.1344.129268 30-Jan-97 SA_REP 10000 0.3 145 80
151 David Bernstein DBERNSTE 011.44.1344.345268 24-Mar-97 SA_REP 9500 0.25 145 80
152 Peter Hall PHALL 011.44.1344.478968 20-Aug-97 SA_REP 9000 0.25 145 80
153 Christopher Olsen COLSEN 011.44.1344.498718 30-Mar-98 SA_REP 8000 0.2 145 80
154 Nanette Cambrault NCAMBRAU 011.44.1344.987668 9-Dec-98 SA_REP 7500 0.2 145 80
155 Oliver Tuvault OTUVAULT 011.44.1344.486508 23-Nov-99 SA_REP 7000 0.15 145 80
156 Janette King JKING 011.44.1345.429268 30-Jan-96 SA_REP 10000 0.35 146 80
157 Patrick Sully PSULLY 011.44.1345.929268 4-Mar-96 SA_REP 9500 0.35 146 80
158 Allan McEwen AMCEWEN 011.44.1345.829268 1-Aug-96 SA_REP 9000 0.35 146 80
159 Lindsey Smith LSMITH 011.44.1345.729268 10-Mar-97 SA_REP 8000 0.3 146 80
160 Louise Doran LDORAN 011.44.1345.629268 15-Dec-97 SA_REP 7500 0.3 146 80
161 Sarath Sewall SSEWALL 011.44.1345.529268 3-Nov-98 SA_REP 7000 0.25 146 80
162 Clara Vishney CVISHNEY 011.44.1346.129268 11-Nov-97 SA_REP 10500 0.25 147 80
163 Danielle Greene DGREENE 011.44.1346.229268 19-Mar-99 SA_REP 9500 0.15 147 80
164 Mattea Marvins MMARVINS 011.44.1346.329268 24-Jan-00 SA_REP 7200 0.1 147 80
165 David Lee DLEE 011.44.1346.529268 23-Feb-00 SA_REP 6800 0.1 147 80
166 Sundar Ande SANDE 011.44.1346.629268 24-Mar-00 SA_REP 6400 0.1 147 80
167 Amit Banda ABANDA 011.44.1346.729268 21-Apr-00 SA_REP 6200 0.1 147 80
168 Lisa Ozer LOZER 011.44.1343.929268 11-Mar-97 SA_REP 11500 0.25 148 80
169 Harrison Bloom HBLOOM 011.44.1343.829268 23-Mar-98 SA_REP 10000 0.2 148 80
170 Tayler Fox TFOX 011.44.1343.729268 24-Jan-98 SA_REP 9600 0.2 148 80
171 William Smith WSMITH 011.44.1343.629268 23-Feb-99 SA_REP 7400 0.15 148 80
172 Elizabeth Bates EBATES 011.44.1343.529268 24-Mar-99 SA_REP 7300 0.15 148 80
173 Sundita Kumar SKUMAR 011.44.1343.329268 21-Apr-00 SA_REP 6100 0.1 148 80
174 Ellen Abel EABEL 011.44.1644.429267 11-May-96 SA_REP 11000 0.3 149 80
175 Alyssa Hutton AHUTTON 011.44.1644.429266 19-Mar-97 SA_REP 8800 0.25 149 80
176 Jonathon Taylor JTAYLOR 011.44.1644.429265 24-Mar-98 SA_REP 8600 0.2 149 80
177 Jack Livingston JLIVINGS 011.44.1644.429264 23-Apr-98 SA_REP 8400 0.2 149 80
178 Kimberely Grant KGRANT 011.44.1644.429263 24-May-99 SA_REP 7000 0.15 149  
179 Charles Johnson CJOHNSON 011.44.1644.429262 4-Jan-00 SA_REP 6200 0.1 149 80
180 Winston Taylor WTAYLOR 650.507.9876 24-Jan-98 SH_CLERK 3200   120 50
181 Jean Fleaur JFLEAUR 650.507.9877 23-Feb-98 SH_CLERK 3100   120 50
182 Martha Sullivan MSULLIVA 650.507.9878 21-Jun-99 SH_CLERK 2500   120 50
183 Girard Geoni GGEONI 650.507.9879 3-Feb-00 SH_CLERK 2800   120 50
184 Nandita Sarchand NSARCHAN 650.509.1876 27-Jan-96 SH_CLERK 4200   121 50
185 Alexis Bull ABULL 650.509.2876 20-Feb-97 SH_CLERK 4100   121 50
186 Julia Dellinger JDELLING 650.509.3876 24-Jun-98 SH_CLERK 3400   121 50
187 Anthony Cabrio ACABRIO 650.509.4876 7-Feb-99 SH_CLERK 3000   121 50
188 Kelly Chung KCHUNG 650.505.1876 14-Jun-97 SH_CLERK 3800   122 50
189 Jennifer Dilly JDILLY 650.505.2876 13-Aug-97 SH_CLERK 3600   122 50
190 Timothy Gates TGATES 650.505.3876 11-Jul-98 SH_CLERK 2900   122 50
191 Randall Perkins RPERKINS 650.505.4876 19-Dec-99 SH_CLERK 2500   122 50
192 Sarah Bell SBELL 650.501.1876 4-Feb-96 SH_CLERK 4000   123 50
193 Britney Everett BEVERETT 650.501.2876 3-Mar-97 SH_CLERK 3900   123 50
194 Samuel McCain SMCCAIN 650.501.3876 1-Jul-98 SH_CLERK 3200   123 50
195 Vance Jones VJONES 650.501.4876 17-Mar-99 SH_CLERK 2800   123 50
196 Alana Walsh AWALSH 650.507.9811 24-Apr-98 SH_CLERK 3100   124 50
197 Kevin Feeney KFEENEY 650.507.9822 23-May-98 SH_CLERK 3000   124 50
198 Donald OConnell DOCONNEL 650.507.9833 21-Jun-99 SH_CLERK 2600   124 50
199 Douglas Grant DGRANT 650.507.9844 13-Jan-00 SH_CLERK 2600   124 50
200 Jennifer Whalen JWHALEN 515.123.4444 17-Sep-87 AD_ASST 4400   101 10
201 Michael Hartstein MHARTSTE 515.123.5555 17-Feb-96 MK_MAN 13000   100 20
202 Pat Fay PFAY 603.123.6666 17-Aug-97 MK_REP 6000   201 20
203 Susan Mavris SMAVRIS 515.123.7777 7-Jun-94 HR_REP 6500   101 40
204 Hermann Baer HBAER 515.123.8888 7-Jun-94 PR_REP 10000   101 70
205 Shelley Higgins SHIGGINS 515.123.8080 7-Jun-94 AC_MGR 12000   101 110
206 William Gietz WGIETZ 515.123.8181 7-Jun-94 AC_ACCOUNT 8300   205 110

mysql> SELECT department_id, AVG(SALARY)

FROM EMPLOYEES GROUP BY department_id

HAVING AVG(SALARY)>=ALL

(SELECT AVG(SALARY) FROM EMPLOYEES GROUP BY department_id);

+---------------+--------------+

| department_id | AVG(SALARY)  |

+---------------+--------------+

|            90 | 19333.333333 |

+---------------+--------------+

1 row in set (0.00 sec)

Note : Here we have used ALL keyword for this subquery as the department selected by the query must have an average salary greater than or equal to all the average salaries of the other departments.

The ANY operator compares the value to each value returned by the subquery. Therefore ANY keyword (which must follow a comparison operator) returns TRUE if the comparison is TRUE for ANY of the values in the column that the subquery returns.

Syntax :

operand comparison_operator ANY (subquery)

Example : MySQL Subquery, ANY operator

The following query selects any employee who works in the location 1800. The subquery finds the department id in the 1800 location, and then the main query selects the employees who work in any of these departments.

employees table :

EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
100 Steven King SKING 515.123.4567 17-Jun-87 AD_PRES 24000     90
101 Neena Kochhar NKOCHHAR 515.123.4568 21-Sep-89 AD_VP 17000   100 90
102 Lex De Haan LDEHAAN 515.123.4569 13-Jan-93 AD_VP 17000   100 90
103 Alexander Hunold AHUNOLD 590.423.4567 3-Jan-90 IT_PROG 9000   102 60
104 Bruce Ernst BERNST 590.423.4568 21-May-91 IT_PROG 6000   103 60
105 David Austin DAUSTIN 590.423.4569 25-Jun-97 IT_PROG 4800   103 60
106 Valli Pataballa VPATABAL 590.423.4560 5-Feb-98 IT_PROG 4800   103 60
107 Diana Lorentz DLORENTZ 590.423.5567 7-Feb-99 IT_PROG 4200   103 60
108 Nancy Greenberg NGREENBE 515.124.4569 17-Aug-94 FI_MGR 12000   101 100
109 Daniel Faviet DFAVIET 515.124.4169 16-Aug-94 FI_ACCOUNT 9000   108 100
110 John Chen JCHEN 515.124.4269 28-Sep-97 FI_ACCOUNT 8200   108 100
111 Ismael Sciarra ISCIARRA 515.124.4369 30-Sep-97 FI_ACCOUNT 7700   108 100
112 Jose Manuel Urman JMURMAN 515.124.4469 7-Mar-98 FI_ACCOUNT 7800   108 100
113 Luis Popp LPOPP 515.124.4567 7-Dec-99 FI_ACCOUNT 6900   108 100
114 Den Raphaely DRAPHEAL 515.127.4561 7-Dec-94 PU_MAN 11000   100 30
115 Alexander Khoo AKHOO 515.127.4562 18-May-95 PU_CLERK 3100   114 30
116 Shelli Baida SBAIDA 515.127.4563 24-Dec-97 PU_CLERK 2900   114 30
117 Sigal Tobias STOBIAS 515.127.4564 24-Jul-97 PU_CLERK 2800   114 30
118 Guy Himuro GHIMURO 515.127.4565 15-Nov-98 PU_CLERK 2600   114 30
119 Karen Colmenares KCOLMENA 515.127.4566 10-Aug-99 PU_CLERK 2500   114 30
120 Matthew Weiss MWEISS 650.123.1234 18-Jul-96 ST_MAN 8000   100 50
121 Adam Fripp AFRIPP 650.123.2234 10-Apr-97 ST_MAN 8200   100 50
122 Payam Kaufling PKAUFLIN 650.123.3234 1-May-95 ST_MAN 7900   100 50
123 Shanta Vollman SVOLLMAN 650.123.4234 10-Oct-97 ST_MAN 6500   100 50
124 Kevin Mourgos KMOURGOS 650.123.5234 16-Nov-99 ST_MAN 5800   100 50
125 Julia Nayer JNAYER 650.124.1214 16-Jul-97 ST_CLERK 3200   120 50
126 Irene Mikkilineni IMIKKILI 650.124.1224 28-Sep-98 ST_CLERK 2700   120 50
127 James Landry JLANDRY 650.124.1334 14-Jan-99 ST_CLERK 2400   120 50
128 Steven Markle SMARKLE 650.124.1434 8-Mar-00 ST_CLERK 2200   120 50
129 Laura Bissot LBISSOT 650.124.5234 20-Aug-97 ST_CLERK 3300   121 50
130 Mozhe Atkinson MATKINSO 650.124.6234 30-Oct-97 ST_CLERK 2800   121 50
131 James Marlow JAMRLOW 650.124.7234 16-Feb-97 ST_CLERK 2500   121 50
132 TJ Olson TJOLSON 650.124.8234 10-Apr-99 ST_CLERK 2100   121 50
133 Jason Mallin JMALLIN 650.127.1934 14-Jun-96 ST_CLERK 3300   122 50
134 Michael Rogers MROGERS 650.127.1834 26-Aug-98 ST_CLERK 2900   122 50
135 Ki Gee KGEE 650.127.1734 12-Dec-99 ST_CLERK 2400   122 50
136 Hazel Philtanker HPHILTAN 650.127.1634 6-Feb-00 ST_CLERK 2200   122 50
137 Renske Ladwig RLADWIG 650.121.1234 14-Jul-95 ST_CLERK 3600   123 50
138 Stephen Stiles SSTILES 650.121.2034 26-Oct-97 ST_CLERK 3200   123 50
139 John Seo JSEO 650.121.2019 12-Feb-98 ST_CLERK 2700   123 50
140 Joshua Patel JPATEL 650.121.1834 6-Apr-98 ST_CLERK 2500   123 50
141 Trenna Rajs TRAJS 650.121.8009 17-Oct-95 ST_CLERK 3500   124 50
142 Curtis Davies CDAVIES 650.121.2994 29-Jan-97 ST_CLERK 3100   124 50
143 Randall Matos RMATOS 650.121.2874 15-Mar-98 ST_CLERK 2600   124 50
144 Peter Vargas PVARGAS 650.121.2004 9-Jul-98 ST_CLERK 2500   124 50
145 John Russell JRUSSEL 011.44.1344.429268 1-Oct-96 SA_MAN 14000 0.4 100 80
146 Karen Partners KPARTNER 011.44.1344.467268 5-Jan-97 SA_MAN 13500 0.3 100 80
147 Alberto Errazuriz AERRAZUR 011.44.1344.429278 10-Mar-97 SA_MAN 12000 0.3 100 80
148 Gerald Cambrault GCAMBRAU 011.44.1344.619268 15-Oct-99 SA_MAN 11000 0.3 100 80
149 Eleni Zlotkey EZLOTKEY 011.44.1344.429018 29-Jan-00 SA_MAN 10500 0.2 100 80
150 Peter Tucker PTUCKER 011.44.1344.129268 30-Jan-97 SA_REP 10000 0.3 145 80
151 David Bernstein DBERNSTE 011.44.1344.345268 24-Mar-97 SA_REP 9500 0.25 145 80
152 Peter Hall PHALL 011.44.1344.478968 20-Aug-97 SA_REP 9000 0.25 145 80
153 Christopher Olsen COLSEN 011.44.1344.498718 30-Mar-98 SA_REP 8000 0.2 145 80
154 Nanette Cambrault NCAMBRAU 011.44.1344.987668 9-Dec-98 SA_REP 7500 0.2 145 80
155 Oliver Tuvault OTUVAULT 011.44.1344.486508 23-Nov-99 SA_REP 7000 0.15 145 80
156 Janette King JKING 011.44.1345.429268 30-Jan-96 SA_REP 10000 0.35 146 80
157 Patrick Sully PSULLY 011.44.1345.929268 4-Mar-96 SA_REP 9500 0.35 146 80
158 Allan McEwen AMCEWEN 011.44.1345.829268 1-Aug-96 SA_REP 9000 0.35 146 80
159 Lindsey Smith LSMITH 011.44.1345.729268 10-Mar-97 SA_REP 8000 0.3 146 80
160 Louise Doran LDORAN 011.44.1345.629268 15-Dec-97 SA_REP 7500 0.3 146 80
161 Sarath Sewall SSEWALL 011.44.1345.529268 3-Nov-98 SA_REP 7000 0.25 146 80
162 Clara Vishney CVISHNEY 011.44.1346.129268 11-Nov-97 SA_REP 10500 0.25 147 80
163 Danielle Greene DGREENE 011.44.1346.229268 19-Mar-99 SA_REP 9500 0.15 147 80
164 Mattea Marvins MMARVINS 011.44.1346.329268 24-Jan-00 SA_REP 7200 0.1 147 80
165 David Lee DLEE 011.44.1346.529268 23-Feb-00 SA_REP 6800 0.1 147 80
166 Sundar Ande SANDE 011.44.1346.629268 24-Mar-00 SA_REP 6400 0.1 147 80
167 Amit Banda ABANDA 011.44.1346.729268 21-Apr-00 SA_REP 6200 0.1 147 80
168 Lisa Ozer LOZER 011.44.1343.929268 11-Mar-97 SA_REP 11500 0.25 148 80
169 Harrison Bloom HBLOOM 011.44.1343.829268 23-Mar-98 SA_REP 10000 0.2 148 80
170 Tayler Fox TFOX 011.44.1343.729268 24-Jan-98 SA_REP 9600 0.2 148 80
171 William Smith WSMITH 011.44.1343.629268 23-Feb-99 SA_REP 7400 0.15 148 80
172 Elizabeth Bates EBATES 011.44.1343.529268 24-Mar-99 SA_REP 7300 0.15 148 80
173 Sundita Kumar SKUMAR 011.44.1343.329268 21-Apr-00 SA_REP 6100 0.1 148 80
174 Ellen Abel EABEL 011.44.1644.429267 11-May-96 SA_REP 11000 0.3 149 80
175 Alyssa Hutton AHUTTON 011.44.1644.429266 19-Mar-97 SA_REP 8800 0.25 149 80
176 Jonathon Taylor JTAYLOR 011.44.1644.429265 24-Mar-98 SA_REP 8600 0.2 149 80
177 Jack Livingston JLIVINGS 011.44.1644.429264 23-Apr-98 SA_REP 8400 0.2 149 80
178 Kimberely Grant KGRANT 011.44.1644.429263 24-May-99 SA_REP 7000 0.15 149  
179 Charles Johnson CJOHNSON 011.44.1644.429262 4-Jan-00 SA_REP 6200 0.1 149 80
180 Winston Taylor WTAYLOR 650.507.9876 24-Jan-98 SH_CLERK 3200   120 50
181 Jean Fleaur JFLEAUR 650.507.9877 23-Feb-98 SH_CLERK 3100   120 50
182 Martha Sullivan MSULLIVA 650.507.9878 21-Jun-99 SH_CLERK 2500   120 50
183 Girard Geoni GGEONI 650.507.9879 3-Feb-00 SH_CLERK 2800   120 50
184 Nandita Sarchand NSARCHAN 650.509.1876 27-Jan-96 SH_CLERK 4200   121 50
185 Alexis Bull ABULL 650.509.2876 20-Feb-97 SH_CLERK 4100   121 50
186 Julia Dellinger JDELLING 650.509.3876 24-Jun-98 SH_CLERK 3400   121 50
187 Anthony Cabrio ACABRIO 650.509.4876 7-Feb-99 SH_CLERK 3000   121 50
188 Kelly Chung KCHUNG 650.505.1876 14-Jun-97 SH_CLERK 3800   122 50
189 Jennifer Dilly JDILLY 650.505.2876 13-Aug-97 SH_CLERK 3600   122 50
190 Timothy Gates TGATES 650.505.3876 11-Jul-98 SH_CLERK 2900   122 50
191 Randall Perkins RPERKINS 650.505.4876 19-Dec-99 SH_CLERK 2500   122 50
192 Sarah Bell SBELL 650.501.1876 4-Feb-96 SH_CLERK 4000   123 50
193 Britney Everett BEVERETT 650.501.2876 3-Mar-97 SH_CLERK 3900   123 50
194 Samuel McCain SMCCAIN 650.501.3876 1-Jul-98 SH_CLERK 3200   123 50
195 Vance Jones VJONES 650.501.4876 17-Mar-99 SH_CLERK 2800   123 50
196 Alana Walsh AWALSH 650.507.9811 24-Apr-98 SH_CLERK 3100   124 50
197 Kevin Feeney KFEENEY 650.507.9822 23-May-98 SH_CLERK 3000   124 50
198 Donald OConnell DOCONNEL 650.507.9833 21-Jun-99 SH_CLERK 2600   124 50
199 Douglas Grant DGRANT 650.507.9844 13-Jan-00 SH_CLERK 2600   124 50
200 Jennifer Whalen JWHALEN 515.123.4444 17-Sep-87 AD_ASST 4400   101 10
201 Michael Hartstein MHARTSTE 515.123.5555 17-Feb-96 MK_MAN 13000   100 20
202 Pat Fay PFAY 603.123.6666 17-Aug-97 MK_REP 6000   201 20
203 Susan Mavris SMAVRIS 515.123.7777 7-Jun-94 HR_REP 6500   101 40
204 Hermann Baer HBAER 515.123.8888 7-Jun-94 PR_REP 10000   101 70
205 Shelley Higgins SHIGGINS 515.123.8080 7-Jun-94 AC_MGR 12000   101 110
206 William Gietz WGIETZ 515.123.8181 7-Jun-94 AC_ACCOUNT 8300   205 110

departments table :

DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
10 Administration 200 1700
20 Marketing 201 1800
30 Purchasing 114 1700
40 Human Resources 203 2400
50 Shipping 121 1500
60 IT 103 1400
70 Public Relations 204 2700
80 Sales 145 2500
90 Executive 100 1700
100 Finance 108 1700
110 Accounting 205 1700
120 Treasury - 1700
130 Corporate Tax - 1700
140 Control And Credit - 1700
150 Shareholder Services - 1700
160 Benefits - 1700
170 Manufacturing - 1700
180 Construction - 1700
190 Contracting - 1700
200 Operations - 1700
210 IT Support - 1700
220 NOC - 1700
230 IT Helpdesk - 1700
240 Government Sales - 1700
250 Retail Sales - 1700
260 Recruiting - 1700
270 Payroll - 1700

mysql> SELECT first_name, last_name,department_id

FROM employees WHERE department_id= ANY

(SELECT DEPARTMENT_ID FROM departments WHERE location_id=1800);

+------------+-----------+---------------+

| first_name | last_name | department_id |

+------------+-----------+---------------+

| Michael    | Hartstein |            20 |

| Pat        | Fay       |            20 |

+------------+-----------+---------------+

2 rows in set (0.00 sec)

Note : We have used ANY keyword in this query because it is likely that the subquery will find more than one departments in 1800 location. If you use the ALL keyword instead of the ANY keyword, no data is selected because no employee works in all departments of 1800 location

When used with a subquery, the word IN (equal to any member of the list) is an alias for = ANY. Thus, the following two statements are the same:

SELECT c1 FROM t1 WHERE c1 = ANY (SELECT c1 FROM t2);

SELECT c1 FROM t1 WHERE c1 IN (SELECT c1 FROM t2);

The word SOME is an alias for ANY. Thus, these two statements are the same :

SELECT c1 FROM t1 WHERE c1 <> ANY  (SELECT c1 FROM t2);

SELECT c1 FROM t1 WHERE c1 <> SOME (SELECT c1 FROM t2);

MySQL Row Subqueries

A row subquery is a subquery that returns a single row and more than one column value. You can use = , >, <, >=, <=, <>, !=, <=> comparison operators. See the following examples :

SELECT * FROM table1 WHERE (col1,col2) = (SELECT col3, col4 FROM table2 WHERE id = 10);

SELECT * FROM table1 WHERE ROW(col1,col2) = (SELECT col3, col4 FROM table2 WHERE id = 10);

For both queries,

  • if the table table2 contains a single row with id = 10, the subquery returns a single row. If this row has col3 and col4 values equal to the col1 and col2 values of any rows in table1, the WHERE expression is TRUE and each query returns those table1 rows.
  • If the table2 row col3 and col4 values are not equal the col1 and col2 values of any table1 row, the expression is FALSE and the query returns an empty result set. The expression is unknown (that is, NULL) if the subquery produces no rows.
  • An error occurs if the subquery produces multiple rows because a row subquery can return at most one row.

Example : MySQL Row Subqueries

In the following examples, queries shows differentr result according to above conditions :

departments table :

DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
10 Administration 200 1700
20 Marketing 201 1800
30 Purchasing 114 1700
40 Human Resources 203 2400
50 Shipping 121 1500
60 IT 103 1400
70 Public Relations 204 2700
80 Sales 145 2500
90 Executive 100 1700
100 Finance 108 1700
110 Accounting 205 1700
120 Treasury - 1700
130 Corporate Tax - 1700
140 Control And Credit - 1700
150 Shareholder Services - 1700
160 Benefits - 1700
170 Manufacturing - 1700
180 Construction - 1700
190 Contracting - 1700
200 Operations - 1700
210 IT Support - 1700
220 NOC - 1700
230 IT Helpdesk - 1700
240 Government Sales - 1700
250 Retail Sales - 1700
260 Recruiting - 1700
270 Payroll - 1700

employees table :

EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
100 Steven King SKING 515.123.4567 17-Jun-87 AD_PRES 24000     90
101 Neena Kochhar NKOCHHAR 515.123.4568 21-Sep-89 AD_VP 17000   100 90
102 Lex De Haan LDEHAAN 515.123.4569 13-Jan-93 AD_VP 17000   100 90
103 Alexander Hunold AHUNOLD 590.423.4567 3-Jan-90 IT_PROG 9000   102 60
104 Bruce Ernst BERNST 590.423.4568 21-May-91 IT_PROG 6000   103 60
105 David Austin DAUSTIN 590.423.4569 25-Jun-97 IT_PROG 4800   103 60
106 Valli Pataballa VPATABAL 590.423.4560 5-Feb-98 IT_PROG 4800   103 60
107 Diana Lorentz DLORENTZ 590.423.5567 7-Feb-99 IT_PROG 4200   103 60
108 Nancy Greenberg NGREENBE 515.124.4569 17-Aug-94 FI_MGR 12000   101 100
109 Daniel Faviet DFAVIET 515.124.4169 16-Aug-94 FI_ACCOUNT 9000   108 100
110 John Chen JCHEN 515.124.4269 28-Sep-97 FI_ACCOUNT 8200   108 100
111 Ismael Sciarra ISCIARRA 515.124.4369 30-Sep-97 FI_ACCOUNT 7700   108 100
112 Jose Manuel Urman JMURMAN 515.124.4469 7-Mar-98 FI_ACCOUNT 7800   108 100
113 Luis Popp LPOPP 515.124.4567 7-Dec-99 FI_ACCOUNT 6900   108 100
114 Den Raphaely DRAPHEAL 515.127.4561 7-Dec-94 PU_MAN 11000   100 30
115 Alexander Khoo AKHOO 515.127.4562 18-May-95 PU_CLERK 3100   114 30
116 Shelli Baida SBAIDA 515.127.4563 24-Dec-97 PU_CLERK 2900   114 30
117 Sigal Tobias STOBIAS 515.127.4564 24-Jul-97 PU_CLERK 2800   114 30
118 Guy Himuro GHIMURO 515.127.4565 15-Nov-98 PU_CLERK 2600   114 30
119 Karen Colmenares KCOLMENA 515.127.4566 10-Aug-99 PU_CLERK 2500   114 30
120 Matthew Weiss MWEISS 650.123.1234 18-Jul-96 ST_MAN 8000   100 50
121 Adam Fripp AFRIPP 650.123.2234 10-Apr-97 ST_MAN 8200   100 50
122 Payam Kaufling PKAUFLIN 650.123.3234 1-May-95 ST_MAN 7900   100 50
123 Shanta Vollman SVOLLMAN 650.123.4234 10-Oct-97 ST_MAN 6500   100 50
124 Kevin Mourgos KMOURGOS 650.123.5234 16-Nov-99 ST_MAN 5800   100 50
125 Julia Nayer JNAYER 650.124.1214 16-Jul-97 ST_CLERK 3200   120 50
126 Irene Mikkilineni IMIKKILI 650.124.1224 28-Sep-98 ST_CLERK 2700   120 50
127 James Landry JLANDRY 650.124.1334 14-Jan-99 ST_CLERK 2400   120 50
128 Steven Markle SMARKLE 650.124.1434 8-Mar-00 ST_CLERK 2200   120 50
129 Laura Bissot LBISSOT 650.124.5234 20-Aug-97 ST_CLERK 3300   121 50
130 Mozhe Atkinson MATKINSO 650.124.6234 30-Oct-97 ST_CLERK 2800   121 50
131 James Marlow JAMRLOW 650.124.7234 16-Feb-97 ST_CLERK 2500   121 50
132 TJ Olson TJOLSON 650.124.8234 10-Apr-99 ST_CLERK 2100   121 50
133 Jason Mallin JMALLIN 650.127.1934 14-Jun-96 ST_CLERK 3300   122 50
134 Michael Rogers MROGERS 650.127.1834 26-Aug-98 ST_CLERK 2900   122 50
135 Ki Gee KGEE 650.127.1734 12-Dec-99 ST_CLERK 2400   122 50
136 Hazel Philtanker HPHILTAN 650.127.1634 6-Feb-00 ST_CLERK 2200   122 50
137 Renske Ladwig RLADWIG 650.121.1234 14-Jul-95 ST_CLERK 3600   123 50
138 Stephen Stiles SSTILES 650.121.2034 26-Oct-97 ST_CLERK 3200   123 50
139 John Seo JSEO 650.121.2019 12-Feb-98 ST_CLERK 2700   123 50
140 Joshua Patel JPATEL 650.121.1834 6-Apr-98 ST_CLERK 2500   123 50
141 Trenna Rajs TRAJS 650.121.8009 17-Oct-95 ST_CLERK 3500   124 50
142 Curtis Davies CDAVIES 650.121.2994 29-Jan-97 ST_CLERK 3100   124 50
143 Randall Matos RMATOS 650.121.2874 15-Mar-98 ST_CLERK 2600   124 50
144 Peter Vargas PVARGAS 650.121.2004 9-Jul-98 ST_CLERK 2500   124 50
145 John Russell JRUSSEL 011.44.1344.429268 1-Oct-96 SA_MAN 14000 0.4 100 80
146 Karen Partners KPARTNER 011.44.1344.467268 5-Jan-97 SA_MAN 13500 0.3 100 80
147 Alberto Errazuriz AERRAZUR 011.44.1344.429278 10-Mar-97 SA_MAN 12000 0.3 100 80
148 Gerald Cambrault GCAMBRAU 011.44.1344.619268 15-Oct-99 SA_MAN 11000 0.3 100 80
149 Eleni Zlotkey EZLOTKEY 011.44.1344.429018 29-Jan-00 SA_MAN 10500 0.2 100 80
150 Peter Tucker PTUCKER 011.44.1344.129268 30-Jan-97 SA_REP 10000 0.3 145 80
151 David Bernstein DBERNSTE 011.44.1344.345268 24-Mar-97 SA_REP 9500 0.25 145 80
152 Peter Hall PHALL 011.44.1344.478968 20-Aug-97 SA_REP 9000 0.25 145 80
153 Christopher Olsen COLSEN 011.44.1344.498718 30-Mar-98 SA_REP 8000 0.2 145 80
154 Nanette Cambrault NCAMBRAU 011.44.1344.987668 9-Dec-98 SA_REP 7500 0.2 145 80
155 Oliver Tuvault OTUVAULT 011.44.1344.486508 23-Nov-99 SA_REP 7000 0.15 145 80
156 Janette King JKING 011.44.1345.429268 30-Jan-96 SA_REP 10000 0.35 146 80
157 Patrick Sully PSULLY 011.44.1345.929268 4-Mar-96 SA_REP 9500 0.35 146 80
158 Allan McEwen AMCEWEN 011.44.1345.829268 1-Aug-96 SA_REP 9000 0.35 146 80
159 Lindsey Smith LSMITH 011.44.1345.729268 10-Mar-97 SA_REP 8000 0.3 146 80
160 Louise Doran LDORAN 011.44.1345.629268 15-Dec-97 SA_REP 7500 0.3 146 80
161 Sarath Sewall SSEWALL 011.44.1345.529268 3-Nov-98 SA_REP 7000 0.25 146 80
162 Clara Vishney CVISHNEY 011.44.1346.129268 11-Nov-97 SA_REP 10500 0.25 147 80
163 Danielle Greene DGREENE 011.44.1346.229268 19-Mar-99 SA_REP 9500 0.15 147 80
164 Mattea Marvins MMARVINS 011.44.1346.329268 24-Jan-00 SA_REP 7200 0.1 147 80
165 David Lee DLEE 011.44.1346.529268 23-Feb-00 SA_REP 6800 0.1 147 80
166 Sundar Ande SANDE 011.44.1346.629268 24-Mar-00 SA_REP 6400 0.1 147 80
167 Amit Banda ABANDA 011.44.1346.729268 21-Apr-00 SA_REP 6200 0.1 147 80
168 Lisa Ozer LOZER 011.44.1343.929268 11-Mar-97 SA_REP 11500 0.25 148 80
169 Harrison Bloom HBLOOM 011.44.1343.829268 23-Mar-98 SA_REP 10000 0.2 148 80
170 Tayler Fox TFOX 011.44.1343.729268 24-Jan-98 SA_REP 9600 0.2 148 80
171 William Smith WSMITH 011.44.1343.629268 23-Feb-99 SA_REP 7400 0.15 148 80
172 Elizabeth Bates EBATES 011.44.1343.529268 24-Mar-99 SA_REP 7300 0.15 148 80
173 Sundita Kumar SKUMAR 011.44.1343.329268 21-Apr-00 SA_REP 6100 0.1 148 80
174 Ellen Abel EABEL 011.44.1644.429267 11-May-96 SA_REP 11000 0.3 149 80
175 Alyssa Hutton AHUTTON 011.44.1644.429266 19-Mar-97 SA_REP 8800 0.25 149 80
176 Jonathon Taylor JTAYLOR 011.44.1644.429265 24-Mar-98 SA_REP 8600 0.2 149 80
177 Jack Livingston JLIVINGS 011.44.1644.429264 23-Apr-98 SA_REP 8400 0.2 149 80
178 Kimberely Grant KGRANT 011.44.1644.429263 24-May-99 SA_REP 7000 0.15 149  
179 Charles Johnson CJOHNSON 011.44.1644.429262 4-Jan-00 SA_REP 6200 0.1 149 80
180 Winston Taylor WTAYLOR 650.507.9876 24-Jan-98 SH_CLERK 3200   120 50
181 Jean Fleaur JFLEAUR 650.507.9877 23-Feb-98 SH_CLERK 3100   120 50
182 Martha Sullivan MSULLIVA 650.507.9878 21-Jun-99 SH_CLERK 2500   120 50
183 Girard Geoni GGEONI 650.507.9879 3-Feb-00 SH_CLERK 2800   120 50
184 Nandita Sarchand NSARCHAN 650.509.1876 27-Jan-96 SH_CLERK 4200   121 50
185 Alexis Bull ABULL 650.509.2876 20-Feb-97 SH_CLERK 4100   121 50
186 Julia Dellinger JDELLING 650.509.3876 24-Jun-98 SH_CLERK 3400   121 50
187 Anthony Cabrio ACABRIO 650.509.4876 7-Feb-99 SH_CLERK 3000   121 50
188 Kelly Chung KCHUNG 650.505.1876 14-Jun-97 SH_CLERK 3800   122 50
189 Jennifer Dilly JDILLY 650.505.2876 13-Aug-97 SH_CLERK 3600   122 50
190 Timothy Gates TGATES 650.505.3876 11-Jul-98 SH_CLERK 2900   122 50
191 Randall Perkins RPERKINS 650.505.4876 19-Dec-99 SH_CLERK 2500   122 50
192 Sarah Bell SBELL 650.501.1876 4-Feb-96 SH_CLERK 4000   123 50
193 Britney Everett BEVERETT 650.501.2876 3-Mar-97 SH_CLERK 3900   123 50
194 Samuel McCain SMCCAIN 650.501.3876 1-Jul-98 SH_CLERK 3200   123 50
195 Vance Jones VJONES 650.501.4876 17-Mar-99 SH_CLERK 2800   123 50
196 Alana Walsh AWALSH 650.507.9811 24-Apr-98 SH_CLERK 3100   124 50
197 Kevin Feeney KFEENEY 650.507.9822 23-May-98 SH_CLERK 3000   124 50
198 Donald OConnell DOCONNEL 650.507.9833 21-Jun-99 SH_CLERK 2600   124 50
199 Douglas Grant DGRANT 650.507.9844 13-Jan-00 SH_CLERK 2600   124 50
200 Jennifer Whalen JWHALEN 515.123.4444 17-Sep-87 AD_ASST 4400   101 10
201 Michael Hartstein MHARTSTE 515.123.5555 17-Feb-96 MK_MAN 13000   100 20
202 Pat Fay PFAY 603.123.6666 17-Aug-97 MK_REP 6000   201 20
203 Susan Mavris SMAVRIS 515.123.7777 7-Jun-94 HR_REP 6500   101 40
204 Hermann Baer HBAER 515.123.8888 7-Jun-94 PR_REP 10000   101 70
205 Shelley Higgins SHIGGINS 515.123.8080 7-Jun-94 AC_MGR 12000   101 110
206 William Gietz WGIETZ 515.123.8181 7-Jun-94 AC_ACCOUNT 8300   205 110
mysql> SELECT department_id, AVG(SALARY) 
FROM EMPLOYEES GROUP BY department_id 
HAVING AVG(SALARY)>=ALL 
(SELECT AVG(SALARY) FROM EMPLOYEES GROUP BY department_id);
+---------------+--------------+
| department_id | AVG(SALARY)  |
+---------------+--------------+
|            90 | 19333.333333 |
+---------------+--------------+
1 row in set (0.00 sec)

Note: Here we have used ALL keyword for this subquery as the department selected by the query must have an average salary greater than or equal to all the average salaries of the other departments.

The ANY operator compares the value to each value returned by the subquery. Therefore ANY keyword (which must follow a comparison operator) returns TRUE if the comparison is TRUE for ANY of the values in the column that the subquery returns.

Syntax:

operand comparison_operator ANY (subquery)

Example: MySQL Subquery, ANY operator

The following query selects any employee who works in the location 1800. The subquery finds the department id in the 1800 location, and then the main query selects the employees who work in any of these departments.

employees table :

EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
100 Steven King SKING 515.123.4567 17-Jun-87 AD_PRES 24000     90
101 Neena Kochhar NKOCHHAR 515.123.4568 21-Sep-89 AD_VP 17000   100 90
102 Lex De Haan LDEHAAN 515.123.4569 13-Jan-93 AD_VP 17000   100 90
103 Alexander Hunold AHUNOLD 590.423.4567 3-Jan-90 IT_PROG 9000   102 60
104 Bruce Ernst BERNST 590.423.4568 21-May-91 IT_PROG 6000   103 60
105 David Austin DAUSTIN 590.423.4569 25-Jun-97 IT_PROG 4800   103 60
106 Valli Pataballa VPATABAL 590.423.4560 5-Feb-98 IT_PROG 4800   103 60
107 Diana Lorentz DLORENTZ 590.423.5567 7-Feb-99 IT_PROG 4200   103 60
108 Nancy Greenberg NGREENBE 515.124.4569 17-Aug-94 FI_MGR 12000   101 100
109 Daniel Faviet DFAVIET 515.124.4169 16-Aug-94 FI_ACCOUNT 9000   108 100
110 John Chen JCHEN 515.124.4269 28-Sep-97 FI_ACCOUNT 8200   108 100
111 Ismael Sciarra ISCIARRA 515.124.4369 30-Sep-97 FI_ACCOUNT 7700   108 100
112 Jose Manuel Urman JMURMAN 515.124.4469 7-Mar-98 FI_ACCOUNT 7800   108 100
113 Luis Popp LPOPP 515.124.4567 7-Dec-99 FI_ACCOUNT 6900   108 100
114 Den Raphaely DRAPHEAL 515.127.4561 7-Dec-94 PU_MAN 11000   100 30
115 Alexander Khoo AKHOO 515.127.4562 18-May-95 PU_CLERK 3100   114 30
116 Shelli Baida SBAIDA 515.127.4563 24-Dec-97 PU_CLERK 2900   114 30
117 Sigal Tobias STOBIAS 515.127.4564 24-Jul-97 PU_CLERK 2800   114 30
118 Guy Himuro GHIMURO 515.127.4565 15-Nov-98 PU_CLERK 2600   114 30
119 Karen Colmenares KCOLMENA 515.127.4566 10-Aug-99 PU_CLERK 2500   114 30
120 Matthew Weiss MWEISS 650.123.1234 18-Jul-96 ST_MAN 8000   100 50
121 Adam Fripp AFRIPP 650.123.2234 10-Apr-97 ST_MAN 8200   100 50
122 Payam Kaufling PKAUFLIN 650.123.3234 1-May-95 ST_MAN 7900   100 50
123 Shanta Vollman SVOLLMAN 650.123.4234 10-Oct-97 ST_MAN 6500   100 50
124 Kevin Mourgos KMOURGOS 650.123.5234 16-Nov-99 ST_MAN 5800   100 50
125 Julia Nayer JNAYER 650.124.1214 16-Jul-97 ST_CLERK 3200   120 50
126 Irene Mikkilineni IMIKKILI 650.124.1224 28-Sep-98 ST_CLERK 2700   120 50
127 James Landry JLANDRY 650.124.1334 14-Jan-99 ST_CLERK 2400   120 50
128 Steven Markle SMARKLE 650.124.1434 8-Mar-00 ST_CLERK 2200   120 50
129 Laura Bissot LBISSOT 650.124.5234 20-Aug-97 ST_CLERK 3300   121 50
130 Mozhe Atkinson MATKINSO 650.124.6234 30-Oct-97 ST_CLERK 2800   121 50
131 James Marlow JAMRLOW 650.124.7234 16-Feb-97 ST_CLERK 2500   121 50
132 TJ Olson TJOLSON 650.124.8234 10-Apr-99 ST_CLERK 2100   121 50
133 Jason Mallin JMALLIN 650.127.1934 14-Jun-96 ST_CLERK 3300   122 50
134 Michael Rogers MROGERS 650.127.1834 26-Aug-98 ST_CLERK 2900   122 50
135 Ki Gee KGEE 650.127.1734 12-Dec-99 ST_CLERK 2400   122 50
136 Hazel Philtanker HPHILTAN 650.127.1634 6-Feb-00 ST_CLERK 2200   122 50
137 Renske Ladwig RLADWIG 650.121.1234 14-Jul-95 ST_CLERK 3600   123 50
138 Stephen Stiles SSTILES 650.121.2034 26-Oct-97 ST_CLERK 3200   123 50
139 John Seo JSEO 650.121.2019 12-Feb-98 ST_CLERK 2700   123 50
140 Joshua Patel JPATEL 650.121.1834 6-Apr-98 ST_CLERK 2500   123 50
141 Trenna Rajs TRAJS 650.121.8009 17-Oct-95 ST_CLERK 3500   124 50
142 Curtis Davies CDAVIES 650.121.2994 29-Jan-97 ST_CLERK 3100   124 50
143 Randall Matos RMATOS 650.121.2874 15-Mar-98 ST_CLERK 2600   124 50
144 Peter Vargas PVARGAS 650.121.2004 9-Jul-98 ST_CLERK 2500   124 50
145 John Russell JRUSSEL 011.44.1344.429268 1-Oct-96 SA_MAN 14000 0.4 100 80
146 Karen Partners KPARTNER 011.44.1344.467268 5-Jan-97 SA_MAN 13500 0.3 100 80
147 Alberto Errazuriz AERRAZUR 011.44.1344.429278 10-Mar-97 SA_MAN 12000 0.3 100 80
148 Gerald Cambrault GCAMBRAU 011.44.1344.619268 15-Oct-99 SA_MAN 11000 0.3 100 80
149 Eleni Zlotkey EZLOTKEY 011.44.1344.429018 29-Jan-00 SA_MAN 10500 0.2 100 80
150 Peter Tucker PTUCKER 011.44.1344.129268 30-Jan-97 SA_REP 10000 0.3 145 80
151 David Bernstein DBERNSTE 011.44.1344.345268 24-Mar-97 SA_REP 9500 0.25 145 80
152 Peter Hall PHALL 011.44.1344.478968 20-Aug-97 SA_REP 9000 0.25 145 80
153 Christopher Olsen COLSEN 011.44.1344.498718 30-Mar-98 SA_REP 8000 0.2 145 80
154 Nanette Cambrault NCAMBRAU 011.44.1344.987668 9-Dec-98 SA_REP 7500 0.2 145 80
155 Oliver Tuvault OTUVAULT 011.44.1344.486508 23-Nov-99 SA_REP 7000 0.15 145 80
156 Janette King JKING 011.44.1345.429268 30-Jan-96 SA_REP 10000 0.35 146 80
157 Patrick Sully PSULLY 011.44.1345.929268 4-Mar-96 SA_REP 9500 0.35 146 80
158 Allan McEwen AMCEWEN 011.44.1345.829268 1-Aug-96 SA_REP 9000 0.35 146 80
159 Lindsey Smith LSMITH 011.44.1345.729268 10-Mar-97 SA_REP 8000 0.3 146 80
160 Louise Doran LDORAN 011.44.1345.629268 15-Dec-97 SA_REP 7500 0.3 146 80
161 Sarath Sewall SSEWALL 011.44.1345.529268 3-Nov-98 SA_REP 7000 0.25 146 80
162 Clara Vishney CVISHNEY 011.44.1346.129268 11-Nov-97 SA_REP 10500 0.25 147 80
163 Danielle Greene DGREENE 011.44.1346.229268 19-Mar-99 SA_REP 9500 0.15 147 80
164 Mattea Marvins MMARVINS 011.44.1346.329268 24-Jan-00 SA_REP 7200 0.1 147 80
165 David Lee DLEE 011.44.1346.529268 23-Feb-00 SA_REP 6800 0.1 147 80
166 Sundar Ande SANDE 011.44.1346.629268 24-Mar-00 SA_REP 6400 0.1 147 80
167 Amit Banda ABANDA 011.44.1346.729268 21-Apr-00 SA_REP 6200 0.1 147 80
168 Lisa Ozer LOZER 011.44.1343.929268 11-Mar-97 SA_REP 11500 0.25 148 80
169 Harrison Bloom HBLOOM 011.44.1343.829268 23-Mar-98 SA_REP 10000 0.2 148 80
170 Tayler Fox TFOX 011.44.1343.729268 24-Jan-98 SA_REP 9600 0.2 148 80
171 William Smith WSMITH 011.44.1343.629268 23-Feb-99 SA_REP 7400 0.15 148 80
172 Elizabeth Bates EBATES 011.44.1343.529268 24-Mar-99 SA_REP 7300 0.15 148 80
173 Sundita Kumar SKUMAR 011.44.1343.329268 21-Apr-00 SA_REP 6100 0.1 148 80
174 Ellen Abel EABEL 011.44.1644.429267 11-May-96 SA_REP 11000 0.3 149 80
175 Alyssa Hutton AHUTTON 011.44.1644.429266 19-Mar-97 SA_REP 8800 0.25 149 80
176 Jonathon Taylor JTAYLOR 011.44.1644.429265 24-Mar-98 SA_REP 8600 0.2 149 80
177 Jack Livingston JLIVINGS 011.44.1644.429264 23-Apr-98 SA_REP 8400 0.2 149 80
178 Kimberely Grant KGRANT 011.44.1644.429263 24-May-99 SA_REP 7000 0.15 149  
179 Charles Johnson CJOHNSON 011.44.1644.429262 4-Jan-00 SA_REP 6200 0.1 149 80
180 Winston Taylor WTAYLOR 650.507.9876 24-Jan-98 SH_CLERK 3200   120 50
181 Jean Fleaur JFLEAUR 650.507.9877 23-Feb-98 SH_CLERK 3100   120 50
182 Martha Sullivan MSULLIVA 650.507.9878 21-Jun-99 SH_CLERK 2500   120 50
183 Girard Geoni GGEONI 650.507.9879 3-Feb-00 SH_CLERK 2800   120 50
184 Nandita Sarchand NSARCHAN 650.509.1876 27-Jan-96 SH_CLERK 4200   121 50
185 Alexis Bull ABULL 650.509.2876 20-Feb-97 SH_CLERK 4100   121 50
186 Julia Dellinger JDELLING 650.509.3876 24-Jun-98 SH_CLERK 3400   121 50
187 Anthony Cabrio ACABRIO 650.509.4876 7-Feb-99 SH_CLERK 3000   121 50
188 Kelly Chung KCHUNG 650.505.1876 14-Jun-97 SH_CLERK 3800   122 50
189 Jennifer Dilly JDILLY 650.505.2876 13-Aug-97 SH_CLERK 3600   122 50
190 Timothy Gates TGATES 650.505.3876 11-Jul-98 SH_CLERK 2900   122 50
191 Randall Perkins RPERKINS 650.505.4876 19-Dec-99 SH_CLERK 2500   122 50
192 Sarah Bell SBELL 650.501.1876 4-Feb-96 SH_CLERK 4000   123 50
193 Britney Everett BEVERETT 650.501.2876 3-Mar-97 SH_CLERK 3900   123 50
194 Samuel McCain SMCCAIN 650.501.3876 1-Jul-98 SH_CLERK 3200   123 50
195 Vance Jones VJONES 650.501.4876 17-Mar-99 SH_CLERK 2800   123 50
196 Alana Walsh AWALSH 650.507.9811 24-Apr-98 SH_CLERK 3100   124 50
197 Kevin Feeney KFEENEY 650.507.9822 23-May-98 SH_CLERK 3000   124 50
198 Donald OConnell DOCONNEL 650.507.9833 21-Jun-99 SH_CLERK 2600   124 50
199 Douglas Grant DGRANT 650.507.9844 13-Jan-00 SH_CLERK 2600   124 50
200 Jennifer Whalen JWHALEN 515.123.4444 17-Sep-87 AD_ASST 4400   101 10
201 Michael Hartstein MHARTSTE 515.123.5555 17-Feb-96 MK_MAN 13000   100 20
202 Pat Fay PFAY 603.123.6666 17-Aug-97 MK_REP 6000   201 20
203 Susan Mavris SMAVRIS 515.123.7777 7-Jun-94 HR_REP 6500   101 40
204 Hermann Baer HBAER 515.123.8888 7-Jun-94 PR_REP 10000   101 70
205 Shelley Higgins SHIGGINS 515.123.8080 7-Jun-94 AC_MGR 12000   101 110
206 William Gietz WGIETZ 515.123.8181 7-Jun-94 AC_ACCOUNT 8300   205 110

departments table:

DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
10 Administration 200 1700
20 Marketing 201 1800
30 Purchasing 114 1700
40 Human Resources 203 2400
50 Shipping 121 1500
60 IT 103 1400
70 Public Relations 204 2700
80 Sales 145 2500
90 Executive 100 1700
100 Finance 108 1700
110 Accounting 205 1700
120 Treasury - 1700
130 Corporate Tax - 1700
140 Control And Credit - 1700
150 Shareholder Services - 1700
160 Benefits - 1700
170 Manufacturing - 1700
180 Construction - 1700
190 Contracting - 1700
200 Operations - 1700
210 IT Support - 1700
220 NOC - 1700
230 IT Helpdesk - 1700
240 Government Sales - 1700
250 Retail Sales - 1700
260 Recruiting - 1700
270 Payroll - 1700
Copy
mysql> SELECT first_name, last_name,department_id 
FROM employees WHERE department_id= ANY
(SELECT DEPARTMENT_ID FROM departments WHERE location_id=1800);
+------------+-----------+---------------+
| first_name | last_name | department_id |
+------------+-----------+---------------+
| Michael    | Hartstein |            20 |
| Pat        | Fay       |            20 |
+------------+-----------+---------------+
2 rows in set (0.00 sec)

Note: We have used ANY keyword in this query because it is likely that the subquery will find more than one departments in 1800 location. If you use the ALL keyword instead of the ANY keyword, no data is selected because no employee works in all departments of 1800 location

When used with a subquery, the word IN (equal to any member of the list) is an alias for = ANY. Thus, the following two statements are the same:

Code:

SELECT c1 FROM t1 WHERE c1 = ANY (SELECT c1 FROM t2); SELECT c1 FROM t1 WHERE c1 IN (SELECT c1 FROM t2);

The word SOME is an alias for ANY. Thus, these two statements are the same:

Code:

SELECT c1 FROM t1 WHERE c1 <> ANY (SELECT c1 FROM t2); SELECT c1 FROM t1 WHERE c1 <> SOME (SELECT c1 FROM t2);

MySQL Row Subqueries

A row subquery is a subquery that returns a single row and more than one column value. You can use = , >, <, >=, <=, <>, !=, <=> comparison operators. See the following examples:

Code:

SELECT * FROM table1 WHERE (col1,col2) = (SELECT col3, col4 FROM table2 WHERE id = 10); SELECT * FROM table1 WHERE ROW(col1,col2) = (SELECT col3, col4 FROM table2 WHERE id = 10);

For both queries,

  • if the table table2 contains a single row with id = 10, the subquery returns a single row. If this row has col3 and col4 values equal to the col1 and col2 values of any rows in table1, the WHERE expression is TRUE and each query returns those table1 rows.
  • If the table2 row col3 and col4 values are not equal the col1 and col2 values of any table1 row, the expression is FALSE and the query returns an empty result set. The expression is unknown (that is, NULL) if the subquery produces no rows.
  • An error occurs if the subquery produces multiple rows because a row subquery can return at most one row.

Example: MySQL Row Subqueries

In the following examples, queries shows differentr result according to above conditions :

departments table:

DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
10 Administration 200 1700
20 Marketing 201 1800
30 Purchasing 114 1700
40 Human Resources 203 2400
50 Shipping 121 1500
60 IT 103 1400
70 Public Relations 204 2700
80 Sales 145 2500
90 Executive 100 1700
100 Finance 108 1700
110 Accounting 205 1700
120 Treasury - 1700
130 Corporate Tax - 1700
140 Control And Credit - 1700
150 Shareholder Services - 1700
160 Benefits - 1700
170 Manufacturing - 1700
180 Construction - 1700
190 Contracting - 1700
200 Operations - 1700
210 IT Support - 1700
220 NOC - 1700
230 IT Helpdesk - 1700
240 Government Sales - 1700
250 Retail Sales - 1700
260 Recruiting - 1700
270 Payroll - 1700
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
100 Steven King SKING 515.123.4567 17-Jun-87 AD_PRES 24000     90
101 Neena Kochhar NKOCHHAR 515.123.4568 21-Sep-89 AD_VP 17000   100 90
102 Lex De Haan LDEHAAN 515.123.4569 13-Jan-93 AD_VP 17000   100 90
103 Alexander Hunold AHUNOLD 590.423.4567 3-Jan-90 IT_PROG 9000   102 60
104 Bruce Ernst BERNST 590.423.4568 21-May-91 IT_PROG 6000   103 60
105 David Austin DAUSTIN 590.423.4569 25-Jun-97 IT_PROG 4800   103 60
106 Valli Pataballa VPATABAL 590.423.4560 5-Feb-98 IT_PROG 4800   103 60
107 Diana Lorentz DLORENTZ 590.423.5567 7-Feb-99 IT_PROG 4200   103 60
108 Nancy Greenberg NGREENBE 515.124.4569 17-Aug-94 FI_MGR 12000   101 100
109 Daniel Faviet DFAVIET 515.124.4169 16-Aug-94 FI_ACCOUNT 9000   108 100
110 John Chen JCHEN 515.124.4269 28-Sep-97 FI_ACCOUNT 8200   108 100
111 Ismael Sciarra ISCIARRA 515.124.4369 30-Sep-97 FI_ACCOUNT 7700   108 100
112 Jose Manuel Urman JMURMAN 515.124.4469 7-Mar-98 FI_ACCOUNT 7800   108 100
113 Luis Popp LPOPP 515.124.4567 7-Dec-99 FI_ACCOUNT 6900   108 100
114 Den Raphaely DRAPHEAL 515.127.4561 7-Dec-94 PU_MAN 11000   100 30
115 Alexander Khoo AKHOO 515.127.4562 18-May-95 PU_CLERK 3100   114 30
116 Shelli Baida SBAIDA 515.127.4563 24-Dec-97 PU_CLERK 2900   114 30
117 Sigal Tobias STOBIAS 515.127.4564 24-Jul-97 PU_CLERK 2800   114 30
118 Guy Himuro GHIMURO 515.127.4565 15-Nov-98 PU_CLERK 2600   114 30
119 Karen Colmenares KCOLMENA 515.127.4566 10-Aug-99 PU_CLERK 2500   114 30
120 Matthew Weiss MWEISS 650.123.1234 18-Jul-96 ST_MAN 8000   100 50
121 Adam Fripp AFRIPP 650.123.2234 10-Apr-97 ST_MAN 8200   100 50
122 Payam Kaufling PKAUFLIN 650.123.3234 1-May-95 ST_MAN 7900   100 50
123 Shanta Vollman SVOLLMAN 650.123.4234 10-Oct-97 ST_MAN 6500   100 50
124 Kevin Mourgos KMOURGOS 650.123.5234 16-Nov-99 ST_MAN 5800   100 50
125 Julia Nayer JNAYER 650.124.1214 16-Jul-97 ST_CLERK 3200   120 50
126 Irene Mikkilineni IMIKKILI 650.124.1224 28-Sep-98 ST_CLERK 2700   120 50
127 James Landry JLANDRY 650.124.1334 14-Jan-99 ST_CLERK 2400   120 50
128 Steven Markle SMARKLE 650.124.1434 8-Mar-00 ST_CLERK 2200   120 50
129 Laura Bissot LBISSOT 650.124.5234 20-Aug-97 ST_CLERK 3300   121 50
130 Mozhe Atkinson MATKINSO 650.124.6234 30-Oct-97 ST_CLERK 2800   121 50
131 James Marlow JAMRLOW 650.124.7234 16-Feb-97 ST_CLERK 2500   121 50
132 TJ Olson TJOLSON 650.124.8234 10-Apr-99 ST_CLERK 2100   121 50
133 Jason Mallin JMALLIN 650.127.1934 14-Jun-96 ST_CLERK 3300   122 50
134 Michael Rogers MROGERS 650.127.1834 26-Aug-98 ST_CLERK 2900   122 50
135 Ki Gee KGEE 650.127.1734 12-Dec-99 ST_CLERK 2400   122 50
136 Hazel Philtanker HPHILTAN 650.127.1634 6-Feb-00 ST_CLERK 2200   122 50
137 Renske Ladwig RLADWIG 650.121.1234 14-Jul-95 ST_CLERK 3600   123 50
138 Stephen Stiles SSTILES 650.121.2034 26-Oct-97 ST_CLERK 3200   123 50
139 John Seo JSEO 650.121.2019 12-Feb-98 ST_CLERK 2700   123 50
140 Joshua Patel JPATEL 650.121.1834 6-Apr-98 ST_CLERK 2500   123 50
141 Trenna Rajs TRAJS 650.121.8009 17-Oct-95 ST_CLERK 3500   124 50
142 Curtis Davies CDAVIES 650.121.2994 29-Jan-97 ST_CLERK 3100   124 50
143 Randall Matos RMATOS 650.121.2874 15-Mar-98 ST_CLERK 2600   124 50
144 Peter Vargas PVARGAS 650.121.2004 9-Jul-98 ST_CLERK 2500   124 50
145 John Russell JRUSSEL 011.44.1344.429268 1-Oct-96 SA_MAN 14000 0.4 100 80
146 Karen Partners KPARTNER 011.44.1344.467268 5-Jan-97 SA_MAN 13500 0.3 100 80
147 Alberto Errazuriz AERRAZUR 011.44.1344.429278 10-Mar-97 SA_MAN 12000 0.3 100 80
148 Gerald Cambrault GCAMBRAU 011.44.1344.619268 15-Oct-99 SA_MAN 11000 0.3 100 80
149 Eleni Zlotkey EZLOTKEY 011.44.1344.429018 29-Jan-00 SA_MAN 10500 0.2 100 80
150 Peter Tucker PTUCKER 011.44.1344.129268 30-Jan-97 SA_REP 10000 0.3 145 80
151 David Bernstein DBERNSTE 011.44.1344.345268 24-Mar-97 SA_REP 9500 0.25 145 80
152 Peter Hall PHALL 011.44.1344.478968 20-Aug-97 SA_REP 9000 0.25 145 80
153 Christopher Olsen COLSEN 011.44.1344.498718 30-Mar-98 SA_REP 8000 0.2 145 80
154 Nanette Cambrault NCAMBRAU 011.44.1344.987668 9-Dec-98 SA_REP 7500 0.2 145 80
155 Oliver Tuvault OTUVAULT 011.44.1344.486508 23-Nov-99 SA_REP 7000 0.15 145 80
156 Janette King JKING 011.44.1345.429268 30-Jan-96 SA_REP 10000 0.35 146 80
157 Patrick Sully PSULLY 011.44.1345.929268 4-Mar-96 SA_REP 9500 0.35 146 80
158 Allan McEwen AMCEWEN 011.44.1345.829268 1-Aug-96 SA_REP 9000 0.35 146 80
159 Lindsey Smith LSMITH 011.44.1345.729268 10-Mar-97 SA_REP 8000 0.3 146 80
160 Louise Doran LDORAN 011.44.1345.629268 15-Dec-97 SA_REP 7500 0.3 146 80
161 Sarath Sewall SSEWALL 011.44.1345.529268 3-Nov-98 SA_REP 7000 0.25 146 80
162 Clara Vishney CVISHNEY 011.44.1346.129268 11-Nov-97 SA_REP 10500 0.25 147 80
163 Danielle Greene DGREENE 011.44.1346.229268 19-Mar-99 SA_REP 9500 0.15 147 80
164 Mattea Marvins MMARVINS 011.44.1346.329268 24-Jan-00 SA_REP 7200 0.1 147 80
165 David Lee DLEE 011.44.1346.529268 23-Feb-00 SA_REP 6800 0.1 147 80
166 Sundar Ande SANDE 011.44.1346.629268 24-Mar-00 SA_REP 6400 0.1 147 80
167 Amit Banda ABANDA 011.44.1346.729268 21-Apr-00 SA_REP 6200 0.1 147 80
168 Lisa Ozer LOZER 011.44.1343.929268 11-Mar-97 SA_REP 11500 0.25 148 80
169 Harrison Bloom HBLOOM 011.44.1343.829268 23-Mar-98 SA_REP 10000 0.2 148 80
170 Tayler Fox TFOX 011.44.1343.729268 24-Jan-98 SA_REP 9600 0.2 148 80
171 William Smith WSMITH 011.44.1343.629268 23-Feb-99 SA_REP 7400 0.15 148 80
172 Elizabeth Bates EBATES 011.44.1343.529268 24-Mar-99 SA_REP 7300 0.15 148 80
173 Sundita Kumar SKUMAR 011.44.1343.329268 21-Apr-00 SA_REP 6100 0.1 148 80
174 Ellen Abel EABEL 011.44.1644.429267 11-May-96 SA_REP 11000 0.3 149 80
175 Alyssa Hutton AHUTTON 011.44.1644.429266 19-Mar-97 SA_REP 8800 0.25 149 80
176 Jonathon Taylor JTAYLOR 011.44.1644.429265 24-Mar-98 SA_REP 8600 0.2 149 80
177 Jack Livingston JLIVINGS 011.44.1644.429264 23-Apr-98 SA_REP 8400 0.2 149 80
178 Kimberely Grant KGRANT 011.44.1644.429263 24-May-99 SA_REP 7000 0.15 149  
179 Charles Johnson CJOHNSON 011.44.1644.429262 4-Jan-00 SA_REP 6200 0.1 149 80
180 Winston Taylor WTAYLOR 650.507.9876 24-Jan-98 SH_CLERK 3200   120 50
181 Jean Fleaur JFLEAUR 650.507.9877 23-Feb-98 SH_CLERK 3100   120 50
182 Martha Sullivan MSULLIVA 650.507.9878 21-Jun-99 SH_CLERK 2500   120 50
183 Girard Geoni GGEONI 650.507.9879 3-Feb-00 SH_CLERK 2800   120 50
184 Nandita Sarchand NSARCHAN 650.509.1876 27-Jan-96 SH_CLERK 4200   121 50
185 Alexis Bull ABULL 650.509.2876 20-Feb-97 SH_CLERK 4100   121 50
186 Julia Dellinger JDELLING 650.509.3876 24-Jun-98 SH_CLERK 3400   121 50
187 Anthony Cabrio ACABRIO 650.509.4876 7-Feb-99 SH_CLERK 3000   121 50
188 Kelly Chung KCHUNG 650.505.1876 14-Jun-97 SH_CLERK 3800   122 50
189 Jennifer Dilly JDILLY 650.505.2876 13-Aug-97 SH_CLERK 3600   122 50
190 Timothy Gates TGATES 650.505.3876 11-Jul-98 SH_CLERK 2900   122 50
191 Randall Perkins RPERKINS 650.505.4876 19-Dec-99 SH_CLERK 2500   122 50
192 Sarah Bell SBELL 650.501.1876 4-Feb-96 SH_CLERK 4000   123 50
193 Britney Everett BEVERETT 650.501.2876 3-Mar-97 SH_CLERK 3900   123 50
194 Samuel McCain SMCCAIN 650.501.3876 1-Jul-98 SH_CLERK 3200   123 50
195 Vance Jones VJONES 650.501.4876 17-Mar-99 SH_CLERK 2800   123 50
196 Alana Walsh AWALSH 650.507.9811 24-Apr-98 SH_CLERK 3100   124 50
197 Kevin Feeney KFEENEY 650.507.9822 23-May-98 SH_CLERK 3000   124 50
198 Donald OConnell DOCONNEL 650.507.9833 21-Jun-99 SH_CLERK 2600   124 50
199 Douglas Grant DGRANT 650.507.9844 13-Jan-00 SH_CLERK 2600   124 50
200 Jennifer Whalen JWHALEN 515.123.4444 17-Sep-87 AD_ASST 4400   101 10
201 Michael Hartstein MHARTSTE 515.123.5555 17-Feb-96 MK_MAN 13000   100 20
202 Pat Fay PFAY 603.123.6666 17-Aug-97 MK_REP 6000   201 20
203 Susan Mavris SMAVRIS 515.123.7777 7-Jun-94 HR_REP 6500   101 40
204 Hermann Baer HBAER 515.123.8888 7-Jun-94 PR_REP 10000   101 70
205 Shelley Higgins SHIGGINS 515.123.8080 7-Jun-94 AC_MGR 12000   101 110
206 William Gietz WGIETZ 515.123.8181 7-Jun-94 AC_ACCOUNT 8300   205 110

mysql> SELECT first_name

FROM employees 
WHERE ROW(department_id, manager_id) = (SELECT department_id, manager_id FROM departments WHERE location_id = 1800);
+------------+
| first_name |
+------------+
| Pat        |
+------------+
1 row in set (0.00 sec)

Code:

mysql>SELECT first_name 
FROM employees
WHERE ROW(department_id, manager_id) = (SELECT department_id, manager_id FROM departments WHERE location_id = 2800);
Empty set (0.00 sec)

Code:

mysql>SELECT first_name 
FROM employees 
WHERE ROW(department_id, manager_id) = (SELECT department_id, manager_id FROM departments WHERE location_id = 1700);
ERROR 1242 (21000): Subquery returns more than 1 row

MySQL Subqueries with EXISTS or NOT EXISTS

The EXISTS operator tests for the existence of rows in the results set of the subquery. If a subquery row value is found, EXISTS subquery is TRUE and in this case NOT EXISTS subquery is FALSE.

Syntax:

SELECT column1 FROM table1 WHERE EXISTS (SELECT * FROM table2);

In the above statement, if table2 contains any rows, even rows with NULL values, the EXISTS condition is TRUE. Generally, an EXISTS subquery starts with SELECT *, but it could begin with SELECT 'X', SELECT 5, or SELECT column1 or anything at all. MySQL ignores the SELECT list in such a subquery, so it makes no difference.

Example: MySQL Subqueries with EXISTS

From the following tables (employees) find employees (employee_id, first_name, last_name, job_id, department_id) who have at least one person reporting to them.

employees table:

EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
100 Steven King SKING 515.123.4567 17-Jun-87 AD_PRES 24000     90
101 Neena Kochhar NKOCHHAR 515.123.4568 21-Sep-89 AD_VP 17000   100 90
102 Lex De Haan LDEHAAN 515.123.4569 13-Jan-93 AD_VP 17000   100 90
103 Alexander Hunold AHUNOLD 590.423.4567 3-Jan-90 IT_PROG 9000   102 60
104 Bruce Ernst BERNST 590.423.4568 21-May-91 IT_PROG 6000   103 60
105 David Austin DAUSTIN 590.423.4569 25-Jun-97 IT_PROG 4800   103 60
106 Valli Pataballa VPATABAL 590.423.4560 5-Feb-98 IT_PROG 4800   103 60
107 Diana Lorentz DLORENTZ 590.423.5567 7-Feb-99 IT_PROG 4200   103 60
108 Nancy Greenberg NGREENBE 515.124.4569 17-Aug-94 FI_MGR 12000   101 100
109 Daniel Faviet DFAVIET 515.124.4169 16-Aug-94 FI_ACCOUNT 9000   108 100
110 John Chen JCHEN 515.124.4269 28-Sep-97 FI_ACCOUNT 8200   108 100
111 Ismael Sciarra ISCIARRA 515.124.4369 30-Sep-97 FI_ACCOUNT 7700   108 100
112 Jose Manuel Urman JMURMAN 515.124.4469 7-Mar-98 FI_ACCOUNT 7800   108 100
113 Luis Popp LPOPP 515.124.4567 7-Dec-99 FI_ACCOUNT 6900   108 100
114 Den Raphaely DRAPHEAL 515.127.4561 7-Dec-94 PU_MAN 11000   100 30
115 Alexander Khoo AKHOO 515.127.4562 18-May-95 PU_CLERK 3100   114 30
116 Shelli Baida SBAIDA 515.127.4563 24-Dec-97 PU_CLERK 2900   114 30
117 Sigal Tobias STOBIAS 515.127.4564 24-Jul-97 PU_CLERK 2800   114 30
118 Guy Himuro GHIMURO 515.127.4565 15-Nov-98 PU_CLERK 2600   114 30
119 Karen Colmenares KCOLMENA 515.127.4566 10-Aug-99 PU_CLERK 2500   114 30
120 Matthew Weiss MWEISS 650.123.1234 18-Jul-96 ST_MAN 8000   100 50
121 Adam Fripp AFRIPP 650.123.2234 10-Apr-97 ST_MAN 8200   100 50
122 Payam Kaufling PKAUFLIN 650.123.3234 1-May-95 ST_MAN 7900   100 50
123 Shanta Vollman SVOLLMAN 650.123.4234 10-Oct-97 ST_MAN 6500   100 50
124 Kevin Mourgos KMOURGOS 650.123.5234 16-Nov-99 ST_MAN 5800   100 50
125 Julia Nayer JNAYER 650.124.1214 16-Jul-97 ST_CLERK 3200   120 50
126 Irene Mikkilineni IMIKKILI 650.124.1224 28-Sep-98 ST_CLERK 2700   120 50
127 James Landry JLANDRY 650.124.1334 14-Jan-99 ST_CLERK 2400   120 50
128 Steven Markle SMARKLE 650.124.1434 8-Mar-00 ST_CLERK 2200   120 50
129 Laura Bissot LBISSOT 650.124.5234 20-Aug-97 ST_CLERK 3300   121 50
130 Mozhe Atkinson MATKINSO 650.124.6234 30-Oct-97 ST_CLERK 2800   121 50
131 James Marlow JAMRLOW 650.124.7234 16-Feb-97 ST_CLERK 2500   121 50
132 TJ Olson TJOLSON 650.124.8234 10-Apr-99 ST_CLERK 2100   121 50
133 Jason Mallin JMALLIN 650.127.1934 14-Jun-96 ST_CLERK 3300   122 50
134 Michael Rogers MROGERS 650.127.1834 26-Aug-98 ST_CLERK 2900   122 50
135 Ki Gee KGEE 650.127.1734 12-Dec-99 ST_CLERK 2400   122 50
136 Hazel Philtanker HPHILTAN 650.127.1634 6-Feb-00 ST_CLERK 2200   122 50
137 Renske Ladwig RLADWIG 650.121.1234 14-Jul-95 ST_CLERK 3600   123 50
138 Stephen Stiles SSTILES 650.121.2034 26-Oct-97 ST_CLERK 3200   123 50
139 John Seo JSEO 650.121.2019 12-Feb-98 ST_CLERK 2700   123 50
140 Joshua Patel JPATEL 650.121.1834 6-Apr-98 ST_CLERK 2500   123 50
141 Trenna Rajs TRAJS 650.121.8009 17-Oct-95 ST_CLERK 3500   124 50
142 Curtis Davies CDAVIES 650.121.2994 29-Jan-97 ST_CLERK 3100   124 50
143 Randall Matos RMATOS 650.121.2874 15-Mar-98 ST_CLERK 2600   124 50
144 Peter Vargas PVARGAS 650.121.2004 9-Jul-98 ST_CLERK 2500   124 50
145 John Russell JRUSSEL 011.44.1344.429268 1-Oct-96 SA_MAN 14000 0.4 100 80
146 Karen Partners KPARTNER 011.44.1344.467268 5-Jan-97 SA_MAN 13500 0.3 100 80
147 Alberto Errazuriz AERRAZUR 011.44.1344.429278 10-Mar-97 SA_MAN 12000 0.3 100 80
148 Gerald Cambrault GCAMBRAU 011.44.1344.619268 15-Oct-99 SA_MAN 11000 0.3 100 80
149 Eleni Zlotkey EZLOTKEY 011.44.1344.429018 29-Jan-00 SA_MAN 10500 0.2 100 80
150 Peter Tucker PTUCKER 011.44.1344.129268 30-Jan-97 SA_REP 10000 0.3 145 80
151 David Bernstein DBERNSTE 011.44.1344.345268 24-Mar-97 SA_REP 9500 0.25 145 80
152 Peter Hall PHALL 011.44.1344.478968 20-Aug-97 SA_REP 9000 0.25 145 80
153 Christopher Olsen COLSEN 011.44.1344.498718 30-Mar-98 SA_REP 8000 0.2 145 80
154 Nanette Cambrault NCAMBRAU 011.44.1344.987668 9-Dec-98 SA_REP 7500 0.2 145 80
155 Oliver Tuvault OTUVAULT 011.44.1344.486508 23-Nov-99 SA_REP 7000 0.15 145 80
156 Janette King JKING 011.44.1345.429268 30-Jan-96 SA_REP 10000 0.35 146 80
157 Patrick Sully PSULLY 011.44.1345.929268 4-Mar-96 SA_REP 9500 0.35 146 80
158 Allan McEwen AMCEWEN 011.44.1345.829268 1-Aug-96 SA_REP 9000 0.35 146 80
159 Lindsey Smith LSMITH 011.44.1345.729268 10-Mar-97 SA_REP 8000 0.3 146 80
160 Louise Doran LDORAN 011.44.1345.629268 15-Dec-97 SA_REP 7500 0.3 146 80
161 Sarath Sewall SSEWALL 011.44.1345.529268 3-Nov-98 SA_REP 7000 0.25 146 80
162 Clara Vishney CVISHNEY 011.44.1346.129268 11-Nov-97 SA_REP 10500 0.25 147 80
163 Danielle Greene DGREENE 011.44.1346.229268 19-Mar-99 SA_REP 9500 0.15 147 80
164 Mattea Marvins MMARVINS 011.44.1346.329268 24-Jan-00 SA_REP 7200 0.1 147 80
165 David Lee DLEE 011.44.1346.529268 23-Feb-00 SA_REP 6800 0.1 147 80
166 Sundar Ande SANDE 011.44.1346.629268 24-Mar-00 SA_REP 6400 0.1 147 80
167 Amit Banda ABANDA 011.44.1346.729268 21-Apr-00 SA_REP 6200 0.1 147 80
168 Lisa Ozer LOZER 011.44.1343.929268 11-Mar-97 SA_REP 11500 0.25 148 80
169 Harrison Bloom HBLOOM 011.44.1343.829268 23-Mar-98 SA_REP 10000 0.2 148 80
170 Tayler Fox TFOX 011.44.1343.729268 24-Jan-98 SA_REP 9600 0.2 148 80
171 William Smith WSMITH 011.44.1343.629268 23-Feb-99 SA_REP 7400 0.15 148 80
172 Elizabeth Bates EBATES 011.44.1343.529268 24-Mar-99 SA_REP 7300 0.15 148 80
173 Sundita Kumar SKUMAR 011.44.1343.329268 21-Apr-00 SA_REP 6100 0.1 148 80
174 Ellen Abel EABEL 011.44.1644.429267 11-May-96 SA_REP 11000 0.3 149 80
175 Alyssa Hutton AHUTTON 011.44.1644.429266 19-Mar-97 SA_REP 8800 0.25 149 80
176 Jonathon Taylor JTAYLOR 011.44.1644.429265 24-Mar-98 SA_REP 8600 0.2 149 80
177 Jack Livingston JLIVINGS 011.44.1644.429264 23-Apr-98 SA_REP 8400 0.2 149 80
178 Kimberely Grant KGRANT 011.44.1644.429263 24-May-99 SA_REP 7000 0.15 149  
179 Charles Johnson CJOHNSON 011.44.1644.429262 4-Jan-00 SA_REP 6200 0.1 149 80
180 Winston Taylor WTAYLOR 650.507.9876 24-Jan-98 SH_CLERK 3200   120 50
181 Jean Fleaur JFLEAUR 650.507.9877 23-Feb-98 SH_CLERK 3100   120 50
182 Martha Sullivan MSULLIVA 650.507.9878 21-Jun-99 SH_CLERK 2500   120 50
183 Girard Geoni GGEONI 650.507.9879 3-Feb-00 SH_CLERK 2800   120 50
184 Nandita Sarchand NSARCHAN 650.509.1876 27-Jan-96 SH_CLERK 4200   121 50
185 Alexis Bull ABULL 650.509.2876 20-Feb-97 SH_CLERK 4100   121 50
186 Julia Dellinger JDELLING 650.509.3876 24-Jun-98 SH_CLERK 3400   121 50
187 Anthony Cabrio ACABRIO 650.509.4876 7-Feb-99 SH_CLERK 3000   121 50
188 Kelly Chung KCHUNG 650.505.1876 14-Jun-97 SH_CLERK 3800   122 50
189 Jennifer Dilly JDILLY 650.505.2876 13-Aug-97 SH_CLERK 3600   122 50
190 Timothy Gates TGATES 650.505.3876 11-Jul-98 SH_CLERK 2900   122 50
191 Randall Perkins RPERKINS 650.505.4876 19-Dec-99 SH_CLERK 2500   122 50
192 Sarah Bell SBELL 650.501.1876 4-Feb-96 SH_CLERK 4000   123 50
193 Britney Everett BEVERETT 650.501.2876 3-Mar-97 SH_CLERK 3900   123 50
194 Samuel McCain SMCCAIN 650.501.3876 1-Jul-98 SH_CLERK 3200   123 50
195 Vance Jones VJONES 650.501.4876 17-Mar-99 SH_CLERK 2800   123 50
196 Alana Walsh AWALSH 650.507.9811 24-Apr-98 SH_CLERK 3100   124 50
197 Kevin Feeney KFEENEY 650.507.9822 23-May-98 SH_CLERK 3000   124 50
198 Donald OConnell DOCONNEL 650.507.9833 21-Jun-99 SH_CLERK 2600   124 50
199 Douglas Grant DGRANT 650.507.9844 13-Jan-00 SH_CLERK 2600   124 50
200 Jennifer Whalen JWHALEN 515.123.4444 17-Sep-87 AD_ASST 4400   101 10
201 Michael Hartstein MHARTSTE 515.123.5555 17-Feb-96 MK_MAN 13000   100 20
202 Pat Fay PFAY 603.123.6666 17-Aug-97 MK_REP 6000   201 20
203 Susan Mavris SMAVRIS 515.123.7777 7-Jun-94 HR_REP 6500   101 40
204 Hermann Baer HBAER 515.123.8888 7-Jun-94 PR_REP 10000   101 70
205 Shelley Higgins SHIGGINS 515.123.8080 7-Jun-94 AC_MGR 12000   101 110
206 William Gietz WGIETZ 515.123.8181 7-Jun-94 AC_ACCOUNT 8300   205 110
SELECT employee_id, first_name, last_name, job_id, department_id 
FROM employees E 
WHERE EXISTS (SELECT * FROM employees WHERE manager_id = E.employee_id);
+-------------+------------+-----------+---------+---------------+
| employee_id | first_name | last_name | job_id  | department_id |
+-------------+------------+-----------+---------+---------------+
|         100 | Steven     | King      | AD_PRES |            90 |
|         101 | Neena      | Kochhar   | AD_VP   |            90 |
|         102 | Lex        | De Haan   | AD_VP   |            90 |
|         103 | Alexander  | Hunold    | IT_PROG |            60 |
|         108 | Nancy      | Greenberg | FI_MGR  |           100 |
|         114 | Den        | Raphaely  | PU_MAN  |            30 |
|         120 | Matthew    | Weiss     | ST_MAN  |            50 |
|         121 | Adam       | Fripp     | ST_MAN  |            50 |
| ----------  | ---------- | --------- | ------- | ------------- |
+-------------+------------+-----------+---------+----------
18 rows in set (0.02 sec)

Example: MySQL Subqueries with NOT EXISTS

NOT EXISTS subquery almost always contains correlations. Here is an example : 
From the following table (departments and employees) find all departments (department_id, department_name) that do not have any employees.

departments table:

DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
10 Administration 200 1700
20 Marketing 201 1800
30 Purchasing 114 1700
40 Human Resources 203 2400
50 Shipping 121 1500
60 IT 103 1400
70 Public Relations 204 2700
80 Sales 145 2500
90 Executive 100 1700
100 Finance 108 1700
110 Accounting 205 1700
120 Treasury - 1700
130 Corporate Tax - 1700
140 Control And Credit - 1700
150 Shareholder Services - 1700
160 Benefits - 1700
170 Manufacturing - 1700
180 Construction - 1700
190 Contracting - 1700
200 Operations - 1700
210 IT Support - 1700
220 NOC - 1700
230 IT Helpdesk - 1700
240 Government Sales - 1700
250 Retail Sales - 1700
260 Recruiting - 1700
270 Payroll - 1700

 

employees table:

 

EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
100 Steven King SKING 515.123.4567 17-Jun-87 AD_PRES 24000     90
101 Neena Kochhar NKOCHHAR 515.123.4568 21-Sep-89 AD_VP 17000   100 90
102 Lex De Haan LDEHAAN 515.123.4569 13-Jan-93 AD_VP 17000   100 90
103 Alexander Hunold AHUNOLD 590.423.4567 3-Jan-90 IT_PROG 9000   102 60
104 Bruce Ernst BERNST 590.423.4568 21-May-91 IT_PROG 6000   103 60
105 David Austin DAUSTIN 590.423.4569 25-Jun-97 IT_PROG 4800   103 60
106 Valli Pataballa VPATABAL 590.423.4560 5-Feb-98 IT_PROG 4800   103 60
107 Diana Lorentz DLORENTZ 590.423.5567 7-Feb-99 IT_PROG 4200   103 60
108 Nancy Greenberg NGREENBE 515.124.4569 17-Aug-94 FI_MGR 12000   101 100
109 Daniel Faviet DFAVIET 515.124.4169 16-Aug-94 FI_ACCOUNT 9000   108 100
110 John Chen JCHEN 515.124.4269 28-Sep-97 FI_ACCOUNT 8200   108 100
111 Ismael Sciarra ISCIARRA 515.124.4369 30-Sep-97 FI_ACCOUNT 7700   108 100
112 Jose Manuel Urman JMURMAN 515.124.4469 7-Mar-98 FI_ACCOUNT 7800   108 100
113 Luis Popp LPOPP 515.124.4567 7-Dec-99 FI_ACCOUNT 6900   108 100
114 Den Raphaely DRAPHEAL 515.127.4561 7-Dec-94 PU_MAN 11000   100 30
115 Alexander Khoo AKHOO 515.127.4562 18-May-95 PU_CLERK 3100   114 30
116 Shelli Baida SBAIDA 515.127.4563 24-Dec-97 PU_CLERK 2900   114 30
117 Sigal Tobias STOBIAS 515.127.4564 24-Jul-97 PU_CLERK 2800   114 30
118 Guy Himuro GHIMURO 515.127.4565 15-Nov-98 PU_CLERK 2600   114 30
119 Karen Colmenares KCOLMENA 515.127.4566 10-Aug-99 PU_CLERK 2500   114 30
120 Matthew Weiss MWEISS 650.123.1234 18-Jul-96 ST_MAN 8000   100 50
121 Adam Fripp AFRIPP 650.123.2234 10-Apr-97 ST_MAN 8200   100 50
122 Payam Kaufling PKAUFLIN 650.123.3234 1-May-95 ST_MAN 7900   100 50
123 Shanta Vollman SVOLLMAN 650.123.4234 10-Oct-97 ST_MAN 6500   100 50
124 Kevin Mourgos KMOURGOS 650.123.5234 16-Nov-99 ST_MAN 5800   100 50
125 Julia Nayer JNAYER 650.124.1214 16-Jul-97 ST_CLERK 3200   120 50
126 Irene Mikkilineni IMIKKILI 650.124.1224 28-Sep-98 ST_CLERK 2700   120 50
127 James Landry JLANDRY 650.124.1334 14-Jan-99 ST_CLERK 2400   120 50
128 Steven Markle SMARKLE 650.124.1434 8-Mar-00 ST_CLERK 2200   120 50
129 Laura Bissot LBISSOT 650.124.5234 20-Aug-97 ST_CLERK 3300   121 50
130 Mozhe Atkinson MATKINSO 650.124.6234 30-Oct-97 ST_CLERK 2800   121 50
131 James Marlow JAMRLOW 650.124.7234 16-Feb-97 ST_CLERK 2500   121 50
132 TJ Olson TJOLSON 650.124.8234 10-Apr-99 ST_CLERK 2100   121 50
133 Jason Mallin JMALLIN 650.127.1934 14-Jun-96 ST_CLERK 3300   122 50
134 Michael Rogers MROGERS 650.127.1834 26-Aug-98 ST_CLERK 2900   122 50
135 Ki Gee KGEE 650.127.1734 12-Dec-99 ST_CLERK 2400   122 50
136 Hazel Philtanker HPHILTAN 650.127.1634 6-Feb-00 ST_CLERK 2200   122 50
137 Renske Ladwig RLADWIG 650.121.1234 14-Jul-95 ST_CLERK 3600   123 50
138 Stephen Stiles SSTILES 650.121.2034 26-Oct-97 ST_CLERK 3200   123 50
139 John Seo JSEO 650.121.2019 12-Feb-98 ST_CLERK 2700   123 50
140 Joshua Patel JPATEL 650.121.1834 6-Apr-98 ST_CLERK 2500   123 50
141 Trenna Rajs TRAJS 650.121.8009 17-Oct-95 ST_CLERK 3500   124 50
142 Curtis Davies CDAVIES 650.121.2994 29-Jan-97 ST_CLERK 3100   124 50
143 Randall Matos RMATOS 650.121.2874 15-Mar-98 ST_CLERK 2600   124 50
144 Peter Vargas PVARGAS 650.121.2004 9-Jul-98 ST_CLERK 2500   124 50
145 John Russell JRUSSEL 011.44.1344.429268 1-Oct-96 SA_MAN 14000 0.4 100 80
146 Karen Partners KPARTNER 011.44.1344.467268 5-Jan-97 SA_MAN 13500 0.3 100 80
147 Alberto Errazuriz AERRAZUR 011.44.1344.429278 10-Mar-97 SA_MAN 12000 0.3 100 80
148 Gerald Cambrault GCAMBRAU 011.44.1344.619268 15-Oct-99 SA_MAN 11000 0.3 100 80
149 Eleni Zlotkey EZLOTKEY 011.44.1344.429018 29-Jan-00 SA_MAN 10500 0.2 100 80
150 Peter Tucker PTUCKER 011.44.1344.129268 30-Jan-97 SA_REP 10000 0.3 145 80
151 David Bernstein DBERNSTE 011.44.1344.345268 24-Mar-97 SA_REP 9500 0.25 145 80
152 Peter Hall PHALL 011.44.1344.478968 20-Aug-97 SA_REP 9000 0.25 145 80
153 Christopher Olsen COLSEN 011.44.1344.498718 30-Mar-98 SA_REP 8000 0.2 145 80
154 Nanette Cambrault NCAMBRAU 011.44.1344.987668 9-Dec-98 SA_REP 7500 0.2 145 80
155 Oliver Tuvault OTUVAULT 011.44.1344.486508 23-Nov-99 SA_REP 7000 0.15 145 80
156 Janette King JKING 011.44.1345.429268 30-Jan-96 SA_REP 10000 0.35 146 80
157 Patrick Sully PSULLY 011.44.1345.929268 4-Mar-96 SA_REP 9500 0.35 146 80
158 Allan McEwen AMCEWEN 011.44.1345.829268 1-Aug-96 SA_REP 9000 0.35 146 80
159 Lindsey Smith LSMITH 011.44.1345.729268 10-Mar-97 SA_REP 8000 0.3 146 80
160 Louise Doran LDORAN 011.44.1345.629268 15-Dec-97 SA_REP 7500 0.3 146 80
161 Sarath Sewall SSEWALL 011.44.1345.529268 3-Nov-98 SA_REP 7000 0.25 146 80
162 Clara Vishney CVISHNEY 011.44.1346.129268 11-Nov-97 SA_REP 10500 0.25 147 80
163 Danielle Greene DGREENE 011.44.1346.229268 19-Mar-99 SA_REP 9500 0.15 147 80
164 Mattea Marvins MMARVINS 011.44.1346.329268 24-Jan-00 SA_REP 7200 0.1 147 80
165 David Lee DLEE 011.44.1346.529268 23-Feb-00 SA_REP 6800 0.1 147 80
166 Sundar Ande SANDE 011.44.1346.629268 24-Mar-00 SA_REP 6400 0.1 147 80
167 Amit Banda ABANDA 011.44.1346.729268 21-Apr-00 SA_REP 6200 0.1 147 80
168 Lisa Ozer LOZER 011.44.1343.929268 11-Mar-97 SA_REP 11500 0.25 148 80
169 Harrison Bloom HBLOOM 011.44.1343.829268 23-Mar-98 SA_REP 10000 0.2 148 80
170 Tayler Fox TFOX 011.44.1343.729268 24-Jan-98 SA_REP 9600 0.2 148 80
171 William Smith WSMITH 011.44.1343.629268 23-Feb-99 SA_REP 7400 0.15 148 80
172 Elizabeth Bates EBATES 011.44.1343.529268 24-Mar-99 SA_REP 7300 0.15 148 80
173 Sundita Kumar SKUMAR 011.44.1343.329268 21-Apr-00 SA_REP 6100 0.1 148 80
174 Ellen Abel EABEL 011.44.1644.429267 11-May-96 SA_REP 11000 0.3 149 80
175 Alyssa Hutton AHUTTON 011.44.1644.429266 19-Mar-97 SA_REP 8800 0.25 149 80
176 Jonathon Taylor JTAYLOR 011.44.1644.429265 24-Mar-98 SA_REP 8600 0.2 149 80
177 Jack Livingston JLIVINGS 011.44.1644.429264 23-Apr-98 SA_REP 8400 0.2 149 80
178 Kimberely Grant KGRANT 011.44.1644.429263 24-May-99 SA_REP 7000 0.15 149  
179 Charles Johnson CJOHNSON 011.44.1644.429262 4-Jan-00 SA_REP 6200 0.1 149 80
180 Winston Taylor WTAYLOR 650.507.9876 24-Jan-98 SH_CLERK 3200   120 50
181 Jean Fleaur JFLEAUR 650.507.9877 23-Feb-98 SH_CLERK 3100   120 50
182 Martha Sullivan MSULLIVA 650.507.9878 21-Jun-99 SH_CLERK 2500   120 50
183 Girard Geoni GGEONI 650.507.9879 3-Feb-00 SH_CLERK 2800   120 50
184 Nandita Sarchand NSARCHAN 650.509.1876 27-Jan-96 SH_CLERK 4200   121 50
185 Alexis Bull ABULL 650.509.2876 20-Feb-97 SH_CLERK 4100   121 50
186 Julia Dellinger JDELLING 650.509.3876 24-Jun-98 SH_CLERK 3400   121 50
187 Anthony Cabrio ACABRIO 650.509.4876 7-Feb-99 SH_CLERK 3000   121 50
188 Kelly Chung KCHUNG 650.505.1876 14-Jun-97 SH_CLERK 3800   122 50
189 Jennifer Dilly JDILLY 650.505.2876 13-Aug-97 SH_CLERK 3600   122 50
190 Timothy Gates TGATES 650.505.3876 11-Jul-98 SH_CLERK 2900   122 50
191 Randall Perkins RPERKINS 650.505.4876 19-Dec-99 SH_CLERK 2500   122 50
192 Sarah Bell SBELL 650.501.1876 4-Feb-96 SH_CLERK 4000   123 50
193 Britney Everett BEVERETT 650.501.2876 3-Mar-97 SH_CLERK 3900   123 50
194 Samuel McCain SMCCAIN 650.501.3876 1-Jul-98 SH_CLERK 3200   123 50
195 Vance Jones VJONES 650.501.4876 17-Mar-99 SH_CLERK 2800   123 50
196 Alana Walsh AWALSH 650.507.9811 24-Apr-98 SH_CLERK 3100   124 50
197 Kevin Feeney KFEENEY 650.507.9822 23-May-98 SH_CLERK 3000   124 50
198 Donald OConnell DOCONNEL 650.507.9833 21-Jun-99 SH_CLERK 2600   124 50
199 Douglas Grant DGRANT 650.507.9844 13-Jan-00 SH_CLERK 2600   124 50
200 Jennifer Whalen JWHALEN 515.123.4444 17-Sep-87 AD_ASST 4400   101 10
201 Michael Hartstein MHARTSTE 515.123.5555 17-Feb-96 MK_MAN 13000   100 20
202 Pat Fay PFAY 603.123.6666 17-Aug-97 MK_REP 6000   201 20
203 Susan Mavris SMAVRIS 515.123.7777 7-Jun-94 HR_REP 6500   101 40
204 Hermann Baer HBAER 515.123.8888 7-Jun-94 PR_REP 10000   101 70
205 Shelley Higgins SHIGGINS 515.123.8080 7-Jun-94 AC_MGR 12000   101 110
206 William Gietz WGIETZ 515.123.8181 7-Jun-94 AC_ACCOUNT 8300   205 110

 

mysql> SELECT department_id, department_name 
FROM departments d 
WHERE NOT EXISTS (SELECT * FROM employees WHERE department_id = d.department_id);
+---------------+----------------------+
| department_id | department_name      |
+---------------+----------------------+
|           120 | Treasury             |
|           130 | Corporate Tax        |
|           140 | Control And Credit   |
|           150 | Shareholder Services |
|           160 | Benefits             |
|           170 | Manufacturing        |
|           180 | Construction         |
|           190 | Contracting          |
|           200 | Operations           |
| ------------  | -------------------- | 
+---------------+----------------------+
16 rows in set (0.00 sec)

MySQL Correlated Subqueries

A correlated subquery is a subquery that contains a reference to a table (in the parent query) that also appears in the outer query. MySQL evaluates from inside to outside.

Correlated subquery syntax:

MySQL Correlated Subqueries - w3resource

 

 

 

 

Example - 1: MySQL Correlated Subqueries

Following query find all employees who earn more than the average salary in their department.

employees table:

EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
100 Steven King SKING 515.123.4567 17-Jun-87 AD_PRES 24000     90
101 Neena Kochhar NKOCHHAR 515.123.4568 21-Sep-89 AD_VP 17000   100 90
102 Lex De Haan LDEHAAN 515.123.4569 13-Jan-93 AD_VP 17000   100 90
103 Alexander Hunold AHUNOLD 590.423.4567 3-Jan-90 IT_PROG 9000   102 60
104 Bruce Ernst BERNST 590.423.4568 21-May-91 IT_PROG 6000   103 60
105 David Austin DAUSTIN 590.423.4569 25-Jun-97 IT_PROG 4800   103 60
106 Valli Pataballa VPATABAL 590.423.4560 5-Feb-98 IT_PROG 4800   103 60
107 Diana Lorentz DLORENTZ 590.423.5567 7-Feb-99 IT_PROG 4200   103 60
108 Nancy Greenberg NGREENBE 515.124.4569 17-Aug-94 FI_MGR 12000   101 100
109 Daniel Faviet DFAVIET 515.124.4169 16-Aug-94 FI_ACCOUNT 9000   108 100
110 John Chen JCHEN 515.124.4269 28-Sep-97 FI_ACCOUNT 8200   108 100
111 Ismael Sciarra ISCIARRA 515.124.4369 30-Sep-97 FI_ACCOUNT 7700   108 100
112 Jose Manuel Urman JMURMAN 515.124.4469 7-Mar-98 FI_ACCOUNT 7800   108 100
113 Luis Popp LPOPP 515.124.4567 7-Dec-99 FI_ACCOUNT 6900   108 100
114 Den Raphaely DRAPHEAL 515.127.4561 7-Dec-94 PU_MAN 11000   100 30
115 Alexander Khoo AKHOO 515.127.4562 18-May-95 PU_CLERK 3100   114 30
116 Shelli Baida SBAIDA 515.127.4563 24-Dec-97 PU_CLERK 2900   114 30
117 Sigal Tobias STOBIAS 515.127.4564 24-Jul-97 PU_CLERK 2800   114 30
118 Guy Himuro GHIMURO 515.127.4565 15-Nov-98 PU_CLERK 2600   114 30
119 Karen Colmenares KCOLMENA 515.127.4566 10-Aug-99 PU_CLERK 2500   114 30
120 Matthew Weiss MWEISS 650.123.1234 18-Jul-96 ST_MAN 8000   100 50
121 Adam Fripp AFRIPP 650.123.2234 10-Apr-97 ST_MAN 8200   100 50
122 Payam Kaufling PKAUFLIN 650.123.3234 1-May-95 ST_MAN 7900   100 50
123 Shanta Vollman SVOLLMAN 650.123.4234 10-Oct-97 ST_MAN 6500   100 50
124 Kevin Mourgos KMOURGOS 650.123.5234 16-Nov-99 ST_MAN 5800   100 50
125 Julia Nayer JNAYER 650.124.1214 16-Jul-97 ST_CLERK 3200   120 50
126 Irene Mikkilineni IMIKKILI 650.124.1224 28-Sep-98 ST_CLERK 2700   120 50
127 James Landry JLANDRY 650.124.1334 14-Jan-99 ST_CLERK 2400   120 50
128 Steven Markle SMARKLE 650.124.1434 8-Mar-00 ST_CLERK 2200   120 50
129 Laura Bissot LBISSOT 650.124.5234 20-Aug-97 ST_CLERK 3300   121 50
130 Mozhe Atkinson MATKINSO 650.124.6234 30-Oct-97 ST_CLERK 2800   121 50
131 James Marlow JAMRLOW 650.124.7234 16-Feb-97 ST_CLERK 2500   121 50
132 TJ Olson TJOLSON 650.124.8234 10-Apr-99 ST_CLERK 2100   121 50
133 Jason Mallin JMALLIN 650.127.1934 14-Jun-96 ST_CLERK 3300   122 50
134 Michael Rogers MROGERS 650.127.1834 26-Aug-98 ST_CLERK 2900   122 50
135 Ki Gee KGEE 650.127.1734 12-Dec-99 ST_CLERK 2400   122 50
136 Hazel Philtanker HPHILTAN 650.127.1634 6-Feb-00 ST_CLERK 2200   122 50
137 Renske Ladwig RLADWIG 650.121.1234 14-Jul-95 ST_CLERK 3600   123 50
138 Stephen Stiles SSTILES 650.121.2034 26-Oct-97 ST_CLERK 3200   123 50
139 John Seo JSEO 650.121.2019 12-Feb-98 ST_CLERK 2700   123 50
140 Joshua Patel JPATEL 650.121.1834 6-Apr-98 ST_CLERK 2500   123 50
141 Trenna Rajs TRAJS 650.121.8009 17-Oct-95 ST_CLERK 3500   124 50
142 Curtis Davies CDAVIES 650.121.2994 29-Jan-97 ST_CLERK 3100   124 50
143 Randall Matos RMATOS 650.121.2874 15-Mar-98 ST_CLERK 2600   124 50
144 Peter Vargas PVARGAS 650.121.2004 9-Jul-98 ST_CLERK 2500   124 50
145 John Russell JRUSSEL 011.44.1344.429268 1-Oct-96 SA_MAN 14000 0.4 100 80
146 Karen Partners KPARTNER 011.44.1344.467268 5-Jan-97 SA_MAN 13500 0.3 100 80
147 Alberto Errazuriz AERRAZUR 011.44.1344.429278 10-Mar-97 SA_MAN 12000 0.3 100 80
148 Gerald Cambrault GCAMBRAU 011.44.1344.619268 15-Oct-99 SA_MAN 11000 0.3 100 80
149 Eleni Zlotkey EZLOTKEY 011.44.1344.429018 29-Jan-00 SA_MAN 10500 0.2 100 80
150 Peter Tucker PTUCKER 011.44.1344.129268 30-Jan-97 SA_REP 10000 0.3 145 80
151 David Bernstein DBERNSTE 011.44.1344.345268 24-Mar-97 SA_REP 9500 0.25 145 80
152 Peter Hall PHALL 011.44.1344.478968 20-Aug-97 SA_REP 9000 0.25 145 80
153 Christopher Olsen COLSEN 011.44.1344.498718 30-Mar-98 SA_REP 8000 0.2 145 80
154 Nanette Cambrault NCAMBRAU 011.44.1344.987668 9-Dec-98 SA_REP 7500 0.2 145 80
155 Oliver Tuvault OTUVAULT 011.44.1344.486508 23-Nov-99 SA_REP 7000 0.15 145 80
156 Janette King JKING 011.44.1345.429268 30-Jan-96 SA_REP 10000 0.35 146 80
157 Patrick Sully PSULLY 011.44.1345.929268 4-Mar-96 SA_REP 9500 0.35 146 80
158 Allan McEwen AMCEWEN 011.44.1345.829268 1-Aug-96 SA_REP 9000 0.35 146 80
159 Lindsey Smith LSMITH 011.44.1345.729268 10-Mar-97 SA_REP 8000 0.3 146 80
160 Louise Doran LDORAN 011.44.1345.629268 15-Dec-97 SA_REP 7500 0.3 146 80
161 Sarath Sewall SSEWALL 011.44.1345.529268 3-Nov-98 SA_REP 7000 0.25 146 80
162 Clara Vishney CVISHNEY 011.44.1346.129268 11-Nov-97 SA_REP 10500 0.25 147 80
163 Danielle Greene DGREENE 011.44.1346.229268 19-Mar-99 SA_REP 9500 0.15 147 80
164 Mattea Marvins MMARVINS 011.44.1346.329268 24-Jan-00 SA_REP 7200 0.1 147 80
165 David Lee DLEE 011.44.1346.529268 23-Feb-00 SA_REP 6800 0.1 147 80
166 Sundar Ande SANDE 011.44.1346.629268 24-Mar-00 SA_REP 6400 0.1 147 80
167 Amit Banda ABANDA 011.44.1346.729268 21-Apr-00 SA_REP 6200 0.1 147 80
168 Lisa Ozer LOZER 011.44.1343.929268 11-Mar-97 SA_REP 11500 0.25 148 80
169 Harrison Bloom HBLOOM 011.44.1343.829268 23-Mar-98 SA_REP 10000 0.2 148 80
170 Tayler Fox TFOX 011.44.1343.729268 24-Jan-98 SA_REP 9600 0.2 148 80
171 William Smith WSMITH 011.44.1343.629268 23-Feb-99 SA_REP 7400 0.15 148 80
172 Elizabeth Bates EBATES 011.44.1343.529268 24-Mar-99 SA_REP 7300 0.15 148 80
173 Sundita Kumar SKUMAR 011.44.1343.329268 21-Apr-00 SA_REP 6100 0.1 148 80
174 Ellen Abel EABEL 011.44.1644.429267 11-May-96 SA_REP 11000 0.3 149 80
175 Alyssa Hutton AHUTTON 011.44.1644.429266 19-Mar-97 SA_REP 8800 0.25 149 80
176 Jonathon Taylor JTAYLOR 011.44.1644.429265 24-Mar-98 SA_REP 8600 0.2 149 80
177 Jack Livingston JLIVINGS 011.44.1644.429264 23-Apr-98 SA_REP 8400 0.2 149 80
178 Kimberely Grant KGRANT 011.44.1644.429263 24-May-99 SA_REP 7000 0.15 149  
179 Charles Johnson CJOHNSON 011.44.1644.429262 4-Jan-00 SA_REP 6200 0.1 149 80
180 Winston Taylor WTAYLOR 650.507.9876 24-Jan-98 SH_CLERK 3200   120 50
181 Jean Fleaur JFLEAUR 650.507.9877 23-Feb-98 SH_CLERK 3100   120 50
182 Martha Sullivan MSULLIVA 650.507.9878 21-Jun-99 SH_CLERK 2500   120 50
183 Girard Geoni GGEONI 650.507.9879 3-Feb-00 SH_CLERK 2800   120 50
184 Nandita Sarchand NSARCHAN 650.509.1876 27-Jan-96 SH_CLERK 4200   121 50
185 Alexis Bull ABULL 650.509.2876 20-Feb-97 SH_CLERK 4100   121 50
186 Julia Dellinger JDELLING 650.509.3876 24-Jun-98 SH_CLERK 3400   121 50
187 Anthony Cabrio ACABRIO 650.509.4876 7-Feb-99 SH_CLERK 3000   121 50
188 Kelly Chung KCHUNG 650.505.1876 14-Jun-97 SH_CLERK 3800   122 50
189 Jennifer Dilly JDILLY 650.505.2876 13-Aug-97 SH_CLERK 3600   122 50
190 Timothy Gates TGATES 650.505.3876 11-Jul-98 SH_CLERK 2900   122 50
191 Randall Perkins RPERKINS 650.505.4876 19-Dec-99 SH_CLERK 2500   122 50
192 Sarah Bell SBELL 650.501.1876 4-Feb-96 SH_CLERK 4000   123 50
193 Britney Everett BEVERETT 650.501.2876 3-Mar-97 SH_CLERK 3900   123 50
194 Samuel McCain SMCCAIN 650.501.3876 1-Jul-98 SH_CLERK 3200   123 50
195 Vance Jones VJONES 650.501.4876 17-Mar-99 SH_CLERK 2800   123 50
196 Alana Walsh AWALSH 650.507.9811 24-Apr-98 SH_CLERK 3100   124 50
197 Kevin Feeney KFEENEY 650.507.9822 23-May-98 SH_CLERK 3000   124 50
198 Donald OConnell DOCONNEL 650.507.9833 21-Jun-99 SH_CLERK 2600   124 50
199 Douglas Grant DGRANT 650.507.9844 13-Jan-00 SH_CLERK 2600   124 50
200 Jennifer Whalen JWHALEN 515.123.4444 17-Sep-87 AD_ASST 4400   101 10
201 Michael Hartstein MHARTSTE 515.123.5555 17-Feb-96 MK_MAN 13000   100 20
202 Pat Fay PFAY 603.123.6666 17-Aug-97 MK_REP 6000   201 20
203 Susan Mavris SMAVRIS 515.123.7777 7-Jun-94 HR_REP 6500   101 40
204 Hermann Baer HBAER 515.123.8888 7-Jun-94 PR_REP 10000   101 70
205 Shelley Higgins SHIGGINS 515.123.8080 7-Jun-94 AC_MGR 12000   101 110
206 William Gietz WGIETZ 515.123.8181 7-Jun-94 AC_ACCOUNT 8300   205 110
mysql> SELECT last_name, salary, department_id 
FROM employees outerr
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = outerr.department_id);
+-----------+----------+---------------+
| last_name | salary   | department_id |
+-----------+----------+---------------+
| King      | 24000.00 |            90 |
| Hunold    |  9000.00 |            60 |
| Ernst     |  6000.00 |            60 |
| Greenberg | 12000.00 |           100 |
| Faviet    |  9000.00 |           100 |
| Raphaely  | 11000.00 |            30 |
| Weiss     |  8000.00 |            50 |
| Fripp     |  8200.00 |            50 |
| --------  | -------- |  ------------ |
+-----------+----------+---------------+
38 rows in set (0.02 sec)

Example - 2: MySQL Correlated Subqueries

From the employees and job_history tables display details of those employees who have changed jobs at least once.

employees table:

EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
100 Steven King SKING 515.123.4567 17-Jun-87 AD_PRES 24000     90
101 Neena Kochhar NKOCHHAR 515.123.4568 21-Sep-89 AD_VP 17000   100 90
102 Lex De Haan LDEHAAN 515.123.4569 13-Jan-93 AD_VP 17000   100 90
103 Alexander Hunold AHUNOLD 590.423.4567 3-Jan-90 IT_PROG 9000   102 60
104 Bruce Ernst BERNST 590.423.4568 21-May-91 IT_PROG 6000   103 60
105 David Austin DAUSTIN 590.423.4569 25-Jun-97 IT_PROG 4800   103 60
106 Valli Pataballa VPATABAL 590.423.4560 5-Feb-98 IT_PROG 4800   103 60
107 Diana Lorentz DLORENTZ 590.423.5567 7-Feb-99 IT_PROG 4200   103 60
108 Nancy Greenberg NGREENBE 515.124.4569 17-Aug-94 FI_MGR 12000   101 100
109 Daniel Faviet DFAVIET 515.124.4169 16-Aug-94 FI_ACCOUNT 9000   108 100
110 John Chen JCHEN 515.124.4269 28-Sep-97 FI_ACCOUNT 8200   108 100
111 Ismael Sciarra ISCIARRA 515.124.4369 30-Sep-97 FI_ACCOUNT 7700   108 100
112 Jose Manuel Urman JMURMAN 515.124.4469 7-Mar-98 FI_ACCOUNT 7800   108 100
113 Luis Popp LPOPP 515.124.4567 7-Dec-99 FI_ACCOUNT 6900   108 100
114 Den Raphaely DRAPHEAL 515.127.4561 7-Dec-94 PU_MAN 11000   100 30
115 Alexander Khoo AKHOO 515.127.4562 18-May-95 PU_CLERK 3100   114 30
116 Shelli Baida SBAIDA 515.127.4563 24-Dec-97 PU_CLERK 2900   114 30
117 Sigal Tobias STOBIAS 515.127.4564 24-Jul-97 PU_CLERK 2800   114 30
118 Guy Himuro GHIMURO 515.127.4565 15-Nov-98 PU_CLERK 2600   114 30
119 Karen Colmenares KCOLMENA 515.127.4566 10-Aug-99 PU_CLERK 2500   114 30
120 Matthew Weiss MWEISS 650.123.1234 18-Jul-96 ST_MAN 8000   100 50
121 Adam Fripp AFRIPP 650.123.2234 10-Apr-97 ST_MAN 8200   100 50
122 Payam Kaufling PKAUFLIN 650.123.3234 1-May-95 ST_MAN 7900   100 50
123 Shanta Vollman SVOLLMAN 650.123.4234 10-Oct-97 ST_MAN 6500   100 50
124 Kevin Mourgos KMOURGOS 650.123.5234 16-Nov-99 ST_MAN 5800   100 50
125 Julia Nayer JNAYER 650.124.1214 16-Jul-97 ST_CLERK 3200   120 50
126 Irene Mikkilineni IMIKKILI 650.124.1224 28-Sep-98 ST_CLERK 2700   120 50
127 James Landry JLANDRY 650.124.1334 14-Jan-99 ST_CLERK 2400   120 50
128 Steven Markle SMARKLE 650.124.1434 8-Mar-00 ST_CLERK 2200   120 50
129 Laura Bissot LBISSOT 650.124.5234 20-Aug-97 ST_CLERK 3300   121 50
130 Mozhe Atkinson MATKINSO 650.124.6234 30-Oct-97 ST_CLERK 2800   121 50
131 James Marlow JAMRLOW 650.124.7234 16-Feb-97 ST_CLERK 2500   121 50
132 TJ Olson TJOLSON 650.124.8234 10-Apr-99 ST_CLERK 2100   121 50
133 Jason Mallin JMALLIN 650.127.1934 14-Jun-96 ST_CLERK 3300   122 50
134 Michael Rogers MROGERS 650.127.1834 26-Aug-98 ST_CLERK 2900   122 50
135 Ki Gee KGEE 650.127.1734 12-Dec-99 ST_CLERK 2400   122 50
136 Hazel Philtanker HPHILTAN 650.127.1634 6-Feb-00 ST_CLERK 2200   122 50
137 Renske Ladwig RLADWIG 650.121.1234 14-Jul-95 ST_CLERK 3600   123 50
138 Stephen Stiles SSTILES 650.121.2034 26-Oct-97 ST_CLERK 3200   123 50
139 John Seo JSEO 650.121.2019 12-Feb-98 ST_CLERK 2700   123 50
140 Joshua Patel JPATEL 650.121.1834 6-Apr-98 ST_CLERK 2500   123 50
141 Trenna Rajs TRAJS 650.121.8009 17-Oct-95 ST_CLERK 3500   124 50
142 Curtis Davies CDAVIES 650.121.2994 29-Jan-97 ST_CLERK 3100   124 50
143 Randall Matos RMATOS 650.121.2874 15-Mar-98 ST_CLERK 2600   124 50
144 Peter Vargas PVARGAS 650.121.2004 9-Jul-98 ST_CLERK 2500   124 50
145 John Russell JRUSSEL 011.44.1344.429268 1-Oct-96 SA_MAN 14000 0.4 100 80
146 Karen Partners KPARTNER 011.44.1344.467268 5-Jan-97 SA_MAN 13500 0.3 100 80
147 Alberto Errazuriz AERRAZUR 011.44.1344.429278 10-Mar-97 SA_MAN 12000 0.3 100 80
148 Gerald Cambrault GCAMBRAU 011.44.1344.619268 15-Oct-99 SA_MAN 11000 0.3 100 80
149 Eleni Zlotkey EZLOTKEY 011.44.1344.429018 29-Jan-00 SA_MAN 10500 0.2 100 80
150 Peter Tucker PTUCKER 011.44.1344.129268 30-Jan-97 SA_REP 10000 0.3 145 80
151 David Bernstein DBERNSTE 011.44.1344.345268 24-Mar-97 SA_REP 9500 0.25 145 80
152 Peter Hall PHALL 011.44.1344.478968 20-Aug-97 SA_REP 9000 0.25 145 80
153 Christopher Olsen COLSEN 011.44.1344.498718 30-Mar-98 SA_REP 8000 0.2 145 80
154 Nanette Cambrault NCAMBRAU 011.44.1344.987668 9-Dec-98 SA_REP 7500 0.2 145 80
155 Oliver Tuvault OTUVAULT 011.44.1344.486508 23-Nov-99 SA_REP 7000 0.15 145 80
156 Janette King JKING 011.44.1345.429268 30-Jan-96 SA_REP 10000 0.35 146 80
157 Patrick Sully PSULLY 011.44.1345.929268 4-Mar-96 SA_REP 9500 0.35 146 80
158 Allan McEwen AMCEWEN 011.44.1345.829268 1-Aug-96 SA_REP 9000 0.35 146 80
159 Lindsey Smith LSMITH 011.44.1345.729268 10-Mar-97 SA_REP 8000 0.3 146 80
160 Louise Doran LDORAN 011.44.1345.629268 15-Dec-97 SA_REP 7500 0.3 146 80
161 Sarath Sewall SSEWALL 011.44.1345.529268 3-Nov-98 SA_REP 7000 0.25 146 80
162 Clara Vishney CVISHNEY 011.44.1346.129268 11-Nov-97 SA_REP 10500 0.25 147 80
163 Danielle Greene DGREENE 011.44.1346.229268 19-Mar-99 SA_REP 9500 0.15 147 80
164 Mattea Marvins MMARVINS 011.44.1346.329268 24-Jan-00 SA_REP 7200 0.1 147 80
165 David Lee DLEE 011.44.1346.529268 23-Feb-00 SA_REP 6800 0.1 147 80
166 Sundar Ande SANDE 011.44.1346.629268 24-Mar-00 SA_REP 6400 0.1 147 80
167 Amit Banda ABANDA 011.44.1346.729268 21-Apr-00 SA_REP 6200 0.1 147 80
168 Lisa Ozer LOZER 011.44.1343.929268 11-Mar-97 SA_REP 11500 0.25 148 80
169 Harrison Bloom HBLOOM 011.44.1343.829268 23-Mar-98 SA_REP 10000 0.2 148 80
170 Tayler Fox TFOX 011.44.1343.729268 24-Jan-98 SA_REP 9600 0.2 148 80
171 William Smith WSMITH 011.44.1343.629268 23-Feb-99 SA_REP 7400 0.15 148 80
172 Elizabeth Bates EBATES 011.44.1343.529268 24-Mar-99 SA_REP 7300 0.15 148 80
173 Sundita Kumar SKUMAR 011.44.1343.329268 21-Apr-00 SA_REP 6100 0.1 148 80
174 Ellen Abel EABEL 011.44.1644.429267 11-May-96 SA_REP 11000 0.3 149 80
175 Alyssa Hutton AHUTTON 011.44.1644.429266 19-Mar-97 SA_REP 8800 0.25 149 80
176 Jonathon Taylor JTAYLOR 011.44.1644.429265 24-Mar-98 SA_REP 8600 0.2 149 80
177 Jack Livingston JLIVINGS 011.44.1644.429264 23-Apr-98 SA_REP 8400 0.2 149 80
178 Kimberely Grant KGRANT 011.44.1644.429263 24-May-99 SA_REP 7000 0.15 149  
179 Charles Johnson CJOHNSON 011.44.1644.429262 4-Jan-00 SA_REP 6200 0.1 149 80
180 Winston Taylor WTAYLOR 650.507.9876 24-Jan-98 SH_CLERK 3200   120 50
181 Jean Fleaur JFLEAUR 650.507.9877 23-Feb-98 SH_CLERK 3100   120 50
182 Martha Sullivan MSULLIVA 650.507.9878 21-Jun-99 SH_CLERK 2500   120 50
183 Girard Geoni GGEONI 650.507.9879 3-Feb-00 SH_CLERK 2800   120 50
184 Nandita Sarchand NSARCHAN 650.509.1876 27-Jan-96 SH_CLERK 4200   121 50
185 Alexis Bull ABULL 650.509.2876 20-Feb-97 SH_CLERK 4100   121 50
186 Julia Dellinger JDELLING 650.509.3876 24-Jun-98 SH_CLERK 3400   121 50
187 Anthony Cabrio ACABRIO 650.509.4876 7-Feb-99 SH_CLERK 3000   121 50
188 Kelly Chung KCHUNG 650.505.1876 14-Jun-97 SH_CLERK 3800   122 50
189 Jennifer Dilly JDILLY 650.505.2876 13-Aug-97 SH_CLERK 3600   122 50
190 Timothy Gates TGATES 650.505.3876 11-Jul-98 SH_CLERK 2900   122 50
191 Randall Perkins RPERKINS 650.505.4876 19-Dec-99 SH_CLERK 2500   122 50
192 Sarah Bell SBELL 650.501.1876 4-Feb-96 SH_CLERK 4000   123 50
193 Britney Everett BEVERETT 650.501.2876 3-Mar-97 SH_CLERK 3900   123 50
194 Samuel McCain SMCCAIN 650.501.3876 1-Jul-98 SH_CLERK 3200   123 50
195 Vance Jones VJONES 650.501.4876 17-Mar-99 SH_CLERK 2800   123 50
196 Alana Walsh AWALSH 650.507.9811 24-Apr-98 SH_CLERK 3100   124 50
197 Kevin Feeney KFEENEY 650.507.9822 23-May-98 SH_CLERK 3000   124 50
198 Donald OConnell DOCONNEL 650.507.9833 21-Jun-99 SH_CLERK 2600   124 50
199 Douglas Grant DGRANT 650.507.9844 13-Jan-00 SH_CLERK 2600   124 50
200 Jennifer Whalen JWHALEN 515.123.4444 17-Sep-87 AD_ASST 4400   101 10
201 Michael Hartstein MHARTSTE 515.123.5555 17-Feb-96 MK_MAN 13000   100 20
202 Pat Fay PFAY 603.123.6666 17-Aug-97 MK_REP 6000   201 20
203 Susan Mavris SMAVRIS 515.123.7777 7-Jun-94 HR_REP 6500   101 40
204 Hermann Baer HBAER 515.123.8888 7-Jun-94 PR_REP 10000   101 70
205 Shelley Higgins SHIGGINS 515.123.8080 7-Jun-94 AC_MGR 12000   101 110
206 William Gietz WGIETZ 515.123.8181 7-Jun-94 AC_ACCOUNT 8300   205 110

job_history table:

EMPLOYEE_ID START_DATE END_DATE JOB_ID DEPARTMENT_ID
102 13-Jan-93 24-Jul-98 IT_PROG 60
101 21-Sep-89 27-Oct-93 AC_ACCOUNT 110
101 28-Oct-93 15-Mar-97 AC_MGR 110
201 17-Feb-96 19-Dec-99 MK_REP 20
114 24-Mar-98 31-Dec-99 ST_CLERK 50
122 1-Jan-99 31-Dec-99 ST_CLERK 50
200 17-Sep-87 17-Jun-93 AD_ASST 90
176 24-Mar-98 31-Dec-98 SA_REP 80
176 1-Jan-99 31-Dec-99 SA_MAN 80
200 1-Jul-94 31-Dec-98 AC_ACCOUNT 90
mysql> SELECT first_name, last_name, employee_id, job_id 
FROM employees E 
WHERE 1 <= (SELECT COUNT(*) FROM Job_history WHERE employee_id = E.employee_id);
+------------+-----------+-------------+---------+
| first_name | last_name | employee_id | job_id  |
+------------+-----------+-------------+---------+
| Neena      | Kochhar   |         101 | AD_VP   |
| Lex        | De Haan   |         102 | AD_VP   |
| Den        | Raphaely  |         114 | PU_MAN  |
| Payam      | Kaufling  |         122 | ST_MAN  |
| Jonathon   | Taylor    |         176 | SA_REP  |
| Jennifer   | Whalen    |         200 | AD_ASST |
| Michael    | Hartstein |         201 | MK_MAN  |
+------------+-----------+-------------+---------+
7 rows in set (0.00 sec)

MySQL Subqueries in the FROM Clause

Subqueries work in a SELECT statement's FROM clause. The syntax is :

SELECT ... FROM (subquery) [AS] name ...

Every table in a FROM clause must have a name, therefore the [AS] name clause is mandatory. Any columns in the subquery select list must have unique names.

Example: MySQL Subqueries in the FROM Clause

We have the following table tb1.

mysql> CREATE TABLE tb1 (c1 INT, c2 CHAR(5), c3 FLOAT); Query OK, 0 rows affected (0.73 sec) 

Let insert some values into tb1.

mysql> INSERT INTO tb1 VALUES (1, '1', 1.0);
Query OK, 1 row affected (0.11 sec)

mysql> INSERT INTO tb1 VALUES (2, '2', 2.0);
Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO tb1 VALUES (3, '3', 3.0);
Query OK, 1 row affected (0.03 sec)

mysql> select * from tb1;
+------+------+------+
| c1   | c2   | c3   |
+------+------+------+
|    1 | 1    |    1 |
|    2 | 2    |    2 |
|    3 | 3    |    3 |
+------+------+------+
3 rows in set (0.00 sec)

Here is how to use a subquery in the FROM clause, using the example table (tb1) :

mysql> SELECT sc1, sc2, sc3 
FROM (SELECT c1 AS sc1, c2 AS sc2, c3*3 AS sc3 FROM tb1) AS sb 
WHERE sc1 > 1;
+------+------+------+
| sc1  | sc2  | sc3  |
+------+------+------+
|    2 | 2    |    6 |
|    3 | 3    |    9 |
+------+------+------+
2 rows in set (0.02 sec)