Tags:concept SQL subquery database Status:🟩

SQL Subqueries

Summary

A subquery is a query embedded within another query, typically used to simplify complex operations or filter data. Subqueries can be placed in various parts of the outer query, such as WHERE, FROM, SELECT, or HAVING clauses.

Details

Subqueries

A subquery is a query embedded inside another query, often used to retrieve data that will be passed to the outer query for further processing. Subqueries allow for more complex and dynamic data retrieval by breaking down a problem into smaller, manageable steps. They can be used in various parts of the outer query, such as the WHERE, FROM, SELECT, or HAVING clauses.

Subqueries come in two main types:

Non-correlated subqueries (or nested queries) are independent of the outer query. They are executed once, and their result is used by the outer query.
Correlated subqueries depend on values from the outer query and are evaluated repeatedly for each row processed by the outer query.

Subqueries are a powerful tool in SQL, enabling complex filtering, comparison, and data manipulation by allowing the output of one query to influence another.

Nested Queries (non-correlated queries)

A nested query is a query inside another query, which allows you to use the results of the inner query to filter or manipulate data in the outer query. The inner query is executed first and then used by the outer query for further operations. The inner queries does not rely on the outer query, but the outer query rely on the inner query.

-- Find the names of students who are in the same city as student number 64
SELECT name FROM students WHERE city = (
SELECT city FROM student WHERE id = 64 );

Correlated Queries

A correlated query is a type of subquery where the inner query uses values from the outer query and is evaluated repeatedly for each row processed by the outer query. This makes it dynamic but potentially slower. The inner query depends on values from the outer query. Subqueries can appear in the SELECT, WHERE, or FROM clauses.

-- Find students who have a higher age than the average age in their own city:
SELECT name FROM student s1 WHERE age > (
SELECT AVG(s2.age) FROM student s2 WHERE s1.city = s2.city );

Queries with ALL/ANY

ALL and ANY allows comparing a value to multiple values from a subquery (multiset).

-- Find names of students older than all students in city 'Copenhagen':
SELECT name FROM student WHERE age > ALL (
SELECT age FROM student WHERE city = 'Copenhagen');
 
-- Find students older than at least one student in city 'Copenhagen':
SELECT name FROM student WHERE age > ANY (
SELECT age FROM student WHERE city = 'Copenhagen');

Queries with IN

IN allows checking if a value is in the set of values

-- Find the names of courses that have at least one enrolled student:
SELECT Name FROM Courses WHERE CourseID IN ( 
SELECT DISTINCT CourseID FROM Enrollments );

Queries with EXISTS

EXISTS checks if a subquery returns any rows. It returns TRUE if there is at least one row, otherwise FALSE.

-- Find all students who are enrolled in at least one course
SELECT Name FROM Students s WHERE EXISTS ( 
SELECT 1 FROM Enrollments e WHERE e.StudentID = s.StudentID );
-- (The subquery doesn't need to return specific columns (hence `1` is sufficient); its purpose is just to verify existence.)

Queries with Set Operations

SQL supports set operations to combine results from multiple SELECT statements:

UNION: Combines all rows from both SELECT statements, removing duplicates.
INTERSECT: Includes rows that are present in both SELECT statements.
EXCEPT: Shows rows in the first SELECT that are not in the second.
(ALL): This can be used after each operation to include duplicates.

-- Find student names who are either in city 'Copenhagen' or have completed at least one course
SELECT name FROM student WHERE city = 'Copenhagen'
UNION 
SELECT name FROM student WHERE completed_courses > 0

INDBS Noter

Explorer