Definition : A subquery, also known as an inner query or nested query, is a query within another query. It is a technique in which a query is written inside another query’s parentheses, allowing the outer query to use the results of the inner query as its own data source.
Types of subquery :
- Scalar subqueries: Scalar subquery returns a single value and can be used in places where a single value is expected, such as in a WHERE clause or a SELECT statement. For example, you could use a scalar subquery to retrieve the maximum value of a particular column or to check if a value exists in another table. Scalar subqueries can also be used in arithmetic expressions, such as adding or subtracting the result of a subquery from another value.
- Table subqueries: Table subquery returns a result set that can be used as a temporary table in the outer query. This can be useful for performing more complex calculations or filtering on subsets of data. Table subqueries can be used in the FROM clause of a SELECT statement, and the resulting temporary table can be joined with other tables in the outer query. You can also use table subqueries to create more advanced filtering criteria, such as finding all orders that match a certain set of conditions.
Break down of syntax:
- SELECT statement: A subquery always starts with a SELECT statement enclosed in parentheses. The SELECT statement can select one or more columns from one or more tables.
Example:
(SELECT Column1, Column2 FROM Table_name)
- FROM clause: The FROM clause specifies the table or tables that the subquery is selecting from. This can be the same table as the outer query or a different table.
- WHERE clause: The WHERE clause specifies the conditions that the subquery uses to filter the data. The results of the subquery are then used in the outer query’s WHERE clause to further filter the data.
Example:
(SELECT Column1 FROM Table_name WHERE column_name > ‘Date’)
Example:
SELECT
Column1,
Column2
FROM
Table_name
WHERE
Column_Name IN
(SELECT Column_Name FROM Table_Name WHERE Column_Name > ‘Date’)
(To use a subquery in a SQL statement, you typically use a comparison operator, such as IN, EXISTS, or NOT EXISTS, to compare the results of the subquery with other values in the outer query. For example, you might use a subquery in the WHERE clause to filter results based on the results of the subquery)
Best Practice to keep in mind :
- Limit the size of subqueries : Subqueries can be slow and resource-intensive if they return a large number of rows. To avoid this, limit the size of your subqueries as much as possible. Use WHERE clauses and other filters to restrict the number of rows returned by the subquery.
- Use efficient subqueries : Not all subqueries are created equal in terms of efficiency. Some subqueries can be rewritten as joins, which can be more efficient in certain cases. If you’re experiencing performance issues with a subquery, consider rewriting it as a join to see if it improves performance.
- Rewrite subqueries as joins : In some cases, subqueries can be rewritten as joins, which can be more efficient. This is especially true for correlated subqueries, which can be slow and resource-intensive. By rewriting a subquery as a join, you can often achieve the same result with better performance.
Examples:
Level1: Subquery to filter data
SELECT *
FROM
Sales.Orders
WHERE orderid IN
(SELECT Orderid FROM Sales.OrderDetails WHERE Productid =77);
(In this example, the subquery is used to retrieve all order IDs that include a particular product (product ID 77), and these values are used in the outer query to retrieve all information about those orders.)
Level2: Subquery with group by
SELECT
Custid,
COUNT(*) as Num_Orders,
Country
FROM
Sales.Customers
WHERE Custid IN
(SELECT Custid FROM Sales.Customers WHERE Country = ‘Spain’)
GROUP BY Custid, Country ;
Launch your Graphy