- SQL Basics
-
Overview
- Introduction to Databases and SQL
- Creating and Managing Tables in SQL
- Data Types and Constraints
- SELECT Queries and Filtering Data
- GROUP BY, HAVING, and ORDER BY
- SQL Joins (INNER, LEFT, RIGHT, FULL)
- Subqueries and Nested Queries
- UNION, INTERSECT, and EXCEPT
- Common Table Expressions (CTE)
- SQL Views and Stored Procedures
Subqueries and Nested Queries
Add to BookmarkIntroduction
Subqueries and nested queries allow SQL queries to be more powerful by enabling one query to be embedded within another. This is useful for retrieving data that depends on the results of another query. In this tutorial, we will explore the concept, syntax, and various use cases of subqueries and nested queries.
What is a Subquery?
A subquery, also known as an inner query, is a query nested inside another SQL query. The outer query, or main query, depends on the results of the subquery to execute.
Syntax:
SELECT column_name(s)
FROM table_name
WHERE column_name OPERATOR (
SELECT column_name
FROM another_table
WHERE condition
);
Types of Subqueries
1. Single-Row Subqueries
A subquery that returns only one row of data.
Example:
SELECT name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary) FROM employees
);
This query retrieves employees whose salary is higher than the average salary.
2. Multi-Row Subqueries
A subquery that returns multiple rows and is used with IN
, ANY
, or ALL
.
Example:
SELECT name, department
FROM employees
WHERE department IN (
SELECT department FROM employees WHERE salary > 50000
);
This query finds all employees in departments where at least one employee earns more than 50,000.
3. Correlated Subqueries
A correlated subquery is executed once for each row processed by the outer query.
Example:
SELECT e1.name, e1.salary
FROM employees e1
WHERE e1.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e1.department = e2.department
);
This query retrieves employees earning more than the average salary in their respective departments.
4. Nested Queries in SELECT Clause
Subqueries can also be used in the SELECT
clause to return computed values.
Example:
SELECT name, (SELECT MAX(salary) FROM employees) AS max_salary
FROM employees;
This query displays each employee’s name along with the highest salary in the company.
5. Nested Queries in FROM Clause
Subqueries can be used as derived tables in the FROM
clause.
Example:
SELECT department, avg_salary
FROM (
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
) AS dept_avg
WHERE avg_salary > 60000;
This query finds departments where the average salary exceeds 60,000.
Performance Considerations
- Optimize subqueries: Use indexes and ensure efficient execution plans.
- Use JOINs when possible: Sometimes, JOINs perform better than subqueries.
- Avoid unnecessary subqueries: Reduce complexity by using alternative query structures.
Conclusion
Subqueries and nested queries add flexibility to SQL queries, allowing complex data retrieval scenarios. Understanding different types of subqueries and their use cases helps in writing efficient and effective SQL queries.
Prepare for Interview
- Design Patterns in Python
- Dynamic Programming and Recursion in Python
- Trees and Graphs in Python
- Linked Lists, Stacks, and Queues in Python
- Sorting and Searching in Python
- Debugging in Python
- Unit Testing in Python
- Asynchronous Programming in PYthon
- Multithreading and Multiprocessing in Python
- Context Managers in Python
- Decorators in Python
- Generators in Python
- Requests in Python
- Django
- Flask
Random Blogs
- Understanding HTAP Databases: Bridging Transactions and Analytics
- Understanding OLTP vs OLAP Databases: How SQL Handles Query Optimization
- Google’s Core Update in May 2020: What You Need to Know
- AI in Cybersecurity: The Future of Digital Protection
- How to Become a Good Data Scientist ?
- Ideas for Content of Every niche on Reader’s Demand during COVID-19
- Python Challenging Programming Exercises Part 2
- What is YII? and How to Install it?
- The Ultimate Guide to Starting a Career in Computer Vision
- Deep Learning (DL): The Core of Modern AI
- Python Challenging Programming Exercises Part 3
- Store Data Into CSV File Using Python Tkinter GUI Library
- Convert RBG Image to Gray Scale Image Using CV2
- AI in Marketing & Advertising: The Future of AI-Driven Strategies
- How AI is Making Humans Weaker – The Hidden Impact of Artificial Intelligence
Datasets for Machine Learning
- Amazon Product Reviews Dataset
- Ozone Level Detection Dataset
- Bank Transaction Fraud Detection
- YouTube Trending Video Dataset (updated daily)
- Covid-19 Case Surveillance Public Use Dataset
- US Election 2020
- Forest Fires Dataset
- Mobile Robots Dataset
- Safety Helmet Detection
- All Space Missions from 1957
- OSIC Pulmonary Fibrosis Progression Dataset
- Wine Quality Dataset
- Google Audio Dataset
- Iris flower dataset
- Artificial Characters Dataset