O'Reilly logo

MySQL 8 Cookbook by Karthik Appigatla

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Using SUB queries

A subquery is a SELECT statement within another statement. Suppose you want to find the name of the employees who started as a Senior Engineer on 1986-06-26. You can get the emp_no from the titles table, and name from the employees table. You can also use JOIN to find out the results.

To get the emp_no from titles:

mysql> SELECT emp_no FROM titles WHERE title="Senior Engineer" AND from_date="1986-06-26";+--------+| emp_no |+--------+|  10001 ||  84305 || 228917 || 426700 || 458304 |+--------+5 rows in set (0.14 sec)

To find the name:

mysql> SELECT first_name, last_name FROM employees WHERE emp_no IN (< output from preceding query>)mysql> SELECT first_name, last_name FROM employees WHERE emp_no IN (10001,84305,228917,426700,458304); ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required