When performing SQL queries, it can sometimes be helpful to further restrict the results of a query using information that is already in the database. This can be useful for general queries in the WHERE clause, but can also be helpful in INSERT or DELETE queries to be more specific about what is inserted into or deleted from the database.
What is an SQL subquery?
An SQL subquery, also called an inner query or nested query, is basically a query that is run within another (main) query. It is typically used in a FROM or WHERE clause of a SELECT statement to further refine the results. Subqueries have a few important rules to remember:
When you use a subquery, that query is run first, and the result is used as part of the main query.
How to use an SQL subquery
To use an SQL subquery, you include it within parentheses within your main query. For example, suppose you have data in two different tables for employees, with a common ID for each employee.
TABLE: employees
+—-+———-+
| ID | NAME |
+—-+———-+
| 1 | Kaitlyn |
| 2 | Daisy |
| 3 | James |
| 4 | Joe |
+—-+———-+
TABLE: employee_hours
+—-+——-+
| ID | HOURS |
+—-+——-+
| 1 | 45 |
| 2 | 25 |
| 3 | 37 |
| 4 | 41 |
+—-+——-+
Now suppose you want to know which employees worked more than 40 hours. To get the employee IDs for those employees from the employee_hours table, you would use the following query:
SELECT id FROM employee_hours WHERE hours>40;
This gives you the following result:
+—-+
| ID |
+—-+
| 1 |
| 4 |
+—-+
Since we want their names, we can use this as a subquery inside another query to make use of its results. So, we will run a query to get the names of all the employees from the employees table where their id matches one of the results of the subquery:
SELECT name FROM employees WHERE id IN (subquery returning list of ids);
When both queries are put together, we get the following final query:
SELECT name FROM employees WHERE id IN (SELECT id FROM employee_hours WHERE hours>40);
The final result is the list of employee names that have worked more than 40 hours:
+———-+
| NAME |
+———-+
| Kaitlyn |
| Joe |
+———-+
With the result you wanted, you now have successfully made use of an SQL subquery!
Databases and monitoring with Morpheus
Do you need to run an SQL database? If so, you should give Morpheus a try. Morpheus is a SaaS solution which allows you to provision databases, including SQL and NoSQL databases, as well as apps and servers with a user-friendly interface. In addition to this, Morpheus can monitor your apps, servers, and databases. With Morpheus, data logging is automatic as you provision servers and apps. Using the available tools, you can monitor the various parts of your system to keep track of uptime, response time, and to be alerted if an issue does arise.
The Morpheus interface is clean and easy to use. Source: Morpheus.
Morpheus allows you to provision apps in a single click, and provides ease of use for developers with APIs and a CLI. In addition, backups are also automatic, and you can have redundancy as needed to avoid potentially long waits for disaster recovery to take place. So, why not register an account or try out Morpheus for free today?
