How to Use Subqueries Without JOIN in SQL

Discover effective techniques for writing SQL subqueries without using JOINs. Learn to show section IDs, enrollments, and list unique students in sections with under five enrollments.
---
This video is based on the question https://stackoverflow.com/q/75932942/ asked by the user 'ProgrammingStudent' ( https://stackoverflow.com/u/21442437/ ) and on the answer https://stackoverflow.com/a/75933087/ provided by the user 'Olivier Jacot-Descombes' ( https://stackoverflow.com/u/880990/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to use subqueries without using JOIN?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/licensing
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Use Subqueries Without JOIN in SQL: A Step-by-Step Guide

In the world of SQL, JOINs are commonly used to combine data from multiple tables. However, there are situations where you may want to utilize subqueries instead. In this post, we'll address a specific problem related to the student schema database provided by Oracle. Our goal is to answer two questions using subqueries without the need for JOINs.

The Problem: Understanding the Requirements

We are presented with two sub-questions involving a student database that contains the following tables:

STUDENT: with columns first_name, last_name, and student_id.

ENROLLMENT: with columns student_id and section_id.

Sub-Questions

Question (a): Write a SQL statement to display section IDs and section enrollment counts.

Question (b): Write a SQL statement to list all unique students (formatted as first_name, last_name) in sections with fewer than five students. The output should not contain any duplicate names and needs to be sorted by last name.

The Solution: Writing SQL Statements with Subqueries

Let's explore how we can answer these questions using subqueries instead of JOINs.

Step 1: Displaying Section IDs and Section Enrollment Counts

For the first question, you can achieve the desired result with a simple query that counts the number of students enrolled in each section. The code looks like this:

[[See Video to Reveal this Text or Code Snippet]]

This statement groups all entries in the enrollment table by section_id, allowing us to count how many students are enrolled in each section.

Step 2: Listing Unique Students in Sections with Fewer Than Five Enrollments

The second question requires more complexity, but with subqueries, it remains manageable. We need to write a query that finds students who are enrolled in sections with fewer than five students. Here’s how we can structure this in SQL:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Query

Inner Query:

The innermost subquery retrieves section IDs where the count of students is less than five. This is done using the HAVING clause, which allows the use of aggregate functions after a GROUP BY.

Middle Query:

This subquery takes the results from the inner query and retrieves the corresponding student_id values from the enrollment table.

Outer Query:

Finally, the outer query selects distinct student names from the student table whose student_id is in the list returned by the middle query. The SUBSTR function is used to format the first name to include only the initial.

Important Considerations

Using DISTINCT: This keyword ensures that each student's name appears only once in the result, useful especially if we anticipate duplicate names from the enrollment table.

Sorting: The ORDER BY last_name clause organizes the result for better readability.

Conclusion

By utilizing subqueries effectively, you can retrieve information from multiple tables without needing to perform JOIN operations. This method not only simplifies certain queries but also gives you a robust toolset for managing complex SQL requirements. With this knowledge, you're now equipped to handle SQL queries involving subqueries confidently!

Feel free to experiment with the queries in your SQL environment and adjust them based on your specific database requirements.

Видео How to Use Subqueries Without JOIN in SQL канала vlogize