Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/sql/nested-queries-in-sql/ below:

SQL Nested Queries - GeeksforGeeks

A nested query (also called a subquery) is a query embedded within another SQL query. The result of the inner query is used by the outer query to perform additional operations. Subqueries can be used in various parts of an SQL query such as SELECT, FROM or WHERE Clauses.

• The inner query runs first, providing data for the outer query.
• The output query uses the results of the inner query for comparison or as input.
• Nested queries are particularly useful for breaking down complex problems into smaller, manageable parts, making it easier to retrieve specific results from large datasets.

Nested queries are highly useful when:

• We need to retrieve data based on the results of another query.
• We want to perform filtering or aggregation without using joins.
• We need to break down complex operations into smaller steps.

To better understand nested queries, we will use the following sample tables: STUDENT, COURSE, and STUDENT_COURSE. These tables demonstrate a real-world scenario of students, courses and their enrollment details, which will be used in the examples below.

The STUDENT table stores information about students, including their unique ID, name, address, phone number, and age.

The STUDENT_COURSE table maps students to the courses they have enrolled in. It uses the student and course IDs as foreign keys.

3. STUDENT_COURSE Table

This table maps students to the courses they have enrolled in, with columns for student ID (S_ID) and course ID (C_ID):

There are two primary types of nested queries in SQL, Independent Nested Queries and Correlated Nested Queries. Each type has its own use case and benefits depending on the complexity of the task at hand.

In an independent nested query, the execution of the inner query is independent of the outer query. The inner query runs first and its result is used directly by the outer query. Operators like IN, NOT IN, ANY and ALL are commonly used with independent nested query.

In this Example we will find the S_IDs of students who are enrolled in the courses ‘DSA’ or ‘DBMS’. We can break the query into two parts:

This query retrieves the IDs of the courses named 'DSA' or 'DBMS' from the COURSE table.

SELECT C_ID FROM COURSE WHERE C_NAME IN ('DSA', 'DBMS');

Output

The inner query finds the course IDs, and the outer query retrieves the student IDs associated with those courses from the STUDENT_COURSE table

SELECT S_ID FROM STUDENT_COURSE WHERE C_ID IN (
SELECT C_ID FROM COURSE WHERE C_NAME IN ('DSA', 'DBMS')
);

Output

Explanation: In this example, the inner query retrieves the C_IDs of the courses 'DSA' and 'DBMS', and the outer query retrieves the student IDs (S_IDs) enrolled in those courses.

In correlated nested queries, the inner query depends on the outer query for its execution. For each row processed by the outer query, the inner query is executed. This means the inner query references columns from the outer query. The EXISTS keyword is often used with correlated queries.

In this Example, we will find the names of students who are enrolled in the course with C_ID = 'C1':

SELECT S_NAME FROM STUDENT S
WHERE EXISTS (
SELECT 1 FROM STUDENT_COURSE SC
WHERE S.S_ID = SC.S_ID AND SC.C_ID = 'C1'
);

Output

Explanation:

For each student in the STUDENT table, the inner query checks if an entry exists in the STUDENT_COURSE table with the same S_ID and the specified C_ID. If such a record exists, the student’s name is included in the output.

SQL provides several operators that can be used with nested queries to filter, compare, and perform conditional checks.

The IN operator is used to check whether a column value matches any value in a list of values returned by a subquery. This operator simplifies queries by avoiding the need for multiple OR conditions.

This query filters the students enrolled in the specified courses by chaining multiple nested queries.

SELECT S_NAME FROM STUDENT
WHERE S_ID IN (
SELECT S_ID FROM STUDENT_COURSE
WHERE C_ID IN (
SELECT C_ID FROM COURSE WHERE C_NAME IN ('DSA', 'DBMS')
)
);

The NOT IN operator excludes rows based on a set of values from a subquery. It is particularly useful for filtering out unwanted results. This operator helps identify records that do not match the conditions defined in the subquery.

This query excludes students who are enrolled in the courses 'DSA' or 'DBMS'.

SELECT S_ID FROM STUDENT
WHERE S_ID NOT IN (
SELECT S_ID FROM STUDENT_COURSE
WHERE C_ID IN (
SELECT C_ID FROM COURSE WHERE C_NAME IN ('DSA', 'DBMS')
)
);

Output

The EXISTS operator checks for the existence of rows in a subquery. It returns true if the subquery produces any rows, making it efficient for conditional checks. This operator is often used to test for relationships between tables.

The inner query checks for matching records in the STUDENT_COURSE table, and the outer query returns the corresponding student names.

SELECT S_NAME FROM STUDENT S
WHERE EXISTS (
SELECT 1 FROM STUDENT_COURSE SC
WHERE S.S_ID = SC.S_ID AND SC.C_ID = 'C1'
);

• ANY: Compares a value with any value returned by the subquery.
• ALL: Compares a value with all values returned by the subquery.

SELECT S_NAME FROM STUDENT
WHERE S_AGE > ANY (
SELECT S_AGE FROM STUDENT WHERE S_ADDRESS = 'DELHI'
);

SELECT S_NAME FROM STUDENT
WHERE S_AGE > ALL (
SELECT S_AGE FROM STUDENT WHERE S_ADDRESS = 'DELHI'
);

• Simplifies complex queries: Nested queries allow us to divide complicated SQL tasks into smaller, more manageable parts. This modular approach makes queries easier to write, debug, and maintain.
• Enhances flexibility: By enabling the use of results from one query within another, nested queries allow dynamic filtering and indirect joins, which can simplify query logic.
• Supports advanced analysis: Nested queries empower developers to perform operations like conditional aggregation, subsetting, and customized calculations, making them ideal for sophisticated data analysis tasks.
• Improves readability: When properly written, nested queries can make complex operations more intuitive by encapsulating logic within inner queries.

1. Optimize independent queries: Use independent nested queries whenever possible, as they are easier to execute and debug. Ensure that inner queries are optimized for performance by adding appropriate indexes.
2. Prefer joins for simple queries: For cases where nested queries add unnecessary complexity, consider using joins instead. Joins are typically faster and more readable for straightforward relationships.
3. Avoid deep nesting: Limit the levels of nesting in queries to improve performance and maintain readability. Deeply nested queries can be computationally expensive and difficult to debug.
4. Use EXISTS and IN wisely: Choose the appropriate operator based on the scenario. Use EXISTS for checking existence and IN for comparing with a list of values.
5. Test query performance: Analyze the execution plan of our queries to identify bottlenecks and improve efficiency. Rewrite queries where necessary to enhance performance.
6. Maintain clarity: Use meaningful table aliases and comments to ensure that nested queries remain understandable to other developers or your future self.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4