SQL In Nested Query
A nested query in SQL, often referred to as a subquery, is a query inside another query. It allows you to retrieve data from one table or use the result of one query as part of another query.
Example:
SELECT Column_Name1, Column_Name2, …., Column_NameN FROM Table_Name WHERE Column_Name Comparison_Operator ( SELECT Column_Name1, Column_Name2, …., Column_NameN FROM Table_Name WHERE condition;
Let’s take the following table named Student_Details:
The following SQL query returns the record of those students whose marks are greater than the average total marks:
SELECT * FROM Student_Details WHERE Stu_Marks> ( SELECT AVG(Stu_Marks ) FROM Student_Details);
Output:
This example uses the Greater than comparison operator with the Subquery.
Let’s take the following two tables named Faculty_Details and Department tables.
Faculty_Details table:
Department table:
SELECT TOP 4 * FROM Employee WHERE Emp_City = Goa ;
Output:
Course Video
1. Write SQL Query to get all details from
Purchasing.PurchaseOrderDetail table where
Product Name = ‘Adjustable Race’.
2. Write SQL Query to get All Details from
Product where RedorderPoint are greater than avg (RedorderPoint).
3. Write SQL Query to get ProductCategory, Name from Production.ProductCategory table where ProductCategoryID from 1 to 3.
4. Write SQL Query to get PurchaseOrdeID, OrderQTY, UnitPrice from Purchasing.PurchaseOrderDetail table where ModifiedDate Greater than ‘2015-01-01’
5. Write SQL Query to get ProductID, Name, ProductNumber, Color, SafetyStockLevel, SellStartDate from Product table where ReorderPoint are less than avg (ReorderPoint).
6. Compose a SQL query to retrieve all production products from the database, focusing specifically on those associated with the product model named ‘LL Road Frame’. [12 ROWS]
7. Craft a SQL query to fetch currency rates from the database, selecting those entries where the average rate exceeds the overall average of average rates. [OUTPUT: 18ROWS]
8. Identify the email address associated with the most recently modification date recorded in the database. [OUTPUT: 2 ROWS]
9. Write a query to get all product records whose category is ‘Dairy products’, or ‘seafood’. [output: 22 rows]
10. Compose a SQL query to extract all product information where suppliers with company names matching with ‘Plutzer Lebensmittelgroßmärkte AG’ and ‘Specialty Biscuits, Ltd.’ [output: 9rows]
YouTube Reference:
1) SQL In Nested Query in Hindi/Urdu
2) SQL In Nested Query in English
SQL IN with nested queries allows you to use the results of a subquery as the list of values for an IN clause. It’s commonly used for dynamic filtering.
Nested queries, also known as subqueries, are queries embedded within another query. They return values used by the parent query. Example:
sqlCopy codeSELECT name FROM Employees WHERE department_id IN (SELECT id FROM Departments WHERE location = ‘New York’);
SQL IN can be combined with multiple conditions to create more complex filters. Example:
sqlCopy codeSELECT * FROM Orders WHERE product_id IN (SELECT id FROM Products WHERE category = ‘Electronics’ AND price > 1000);
- Avoid deeply nested subqueries for better performance.
- Use indexes on columns referenced in the subquery.
- Consider JOINs as an alternative to subqueries for complex conditions.
- Data type mismatch: Ensure the subquery returns values compatible with the column in the IN clause.
- NULL values in subqueries: Handle potential NULL results using IS NOT NULL.
While SQL IN Nested Queries can replicate JOIN functionality, JOINs are generally faster and more efficient for retrieving related data.
Case sensitivity depends on the database configuration. In MySQL, queries are case-insensitive by default, while PostgreSQL is case-sensitive unless explicitly configured.
- Filter by complex conditions:
sqlCopy codeSELECT * FROM Employees WHERE id IN (SELECT employee_id FROM Salaries WHERE amount > 50000);
- Use correlated subqueries:
sqlCopy codeSELECT name FROM Employees e WHERE salary > (SELECT AVG(salary) FROM Employees WHERE department = e.department);
Check out our comprehensive SQL IN Nested Queries Tutorial for detailed explanations and examples.
Yes, aggregate functions like SUM(), AVG(), or COUNT() can be used in subqueries:
sqlCopy codeSELECT name FROM Employees WHERE id IN (SELECT employee_id FROM Salaries WHERE amount > (SELECT AVG(amount) FROM Salaries));
