Subquery in SQL with WHERE, SELECT, and FROM

In the final chapter of DataCamp’s “Joining Data in SQL” course, I delved into the concept of Subquery in SQL. Here’s a detailed exploration of the key concepts, including examples and explanations.

Introduction to Subqueries

A subquery, also known as an inner query or nested query, is a query within another SQL query. It can be used to perform operations that require multiple steps. Here are the fundamental concepts, explained with examples:

Table 1: students

student_id	name	age
1	Alice	20
2	Bob	22
3	Carol	23
4	Dave	21

Table 2: grades

grade_id	student_id	course	grade
1	1	Math	A
2	2	Science	B
3	3	History	A
4	4	Math	C
5	1	Science	B
6	2	Math	B

1. Subquery with WHERE

A subquery in a WHERE clause allows you to filter records based on the results of another query.

Example:

SELECT name
FROM students
WHERE student_id IN (SELECT student_id FROM grades WHERE grade = 'A');

This query retrieves the names of students who have received an ‘A’ in any course.

2. Subquery with SELECT

A subquery in a SELECT clause allows you to include the results of another query as a column in your result set.

Example:

SELECT name, age, (SELECT grade FROM grades WHERE students.student_id = grades.student_id AND course = 'Math') AS math_grade
FROM students;

This query retrieves the names, ages, and Math grades of students.

3. Subquery with FROM

A subquery in a FROM clause allows you to use the result set of another query as a temporary table.

Example:

SELECT name, avg_grade
FROM students, (SELECT student_id, AVG(CASE 
                                            WHEN grade = 'A' THEN 4
                                            WHEN grade = 'B' THEN 3
                                            WHEN grade = 'C' THEN 2
                                            ELSE 0 END) AS avg_grade
                FROM grades
                GROUP BY student_id) AS grade_avg
WHERE students.student_id = grade_avg.student_id;

This query retrieves the names of students along with their average grade, where grades are converted to a numeric scale (A=4, B=3, C=2).

Summary Table

Concept	Description	Example
Subquery with WHERE	Filters records based on a subquery result	`SELECT ... WHERE ... (SELECT ...)`
Subquery with SELECT	Includes subquery results as a column in the result set	`SELECT ..., (SELECT ...) AS ...`
Subquery with FROM	It uses subquery results as a temporary table	`SELECT ... FROM ..., (SELECT ...) AS ...`

At this point, I have decided to pause my journey with DataCamp’s SQL courses. Although I don’t plan to specialize in Data Science or Data Analysis, the knowledge I’ve gained in SQL forms a solid foundation for my backend development aspirations. Previously, I tried studying full-stack development through FreeCodeCamp and The Odin Project, but I found HTML and CSS rather tedious. Now that I know PostgreSQL and relational databases, I’m excited to expand my backend knowledge. I plan in the future to enroll in Codecademy’s Backend Developer Career Path. But for now, I’m leaving the web development path aside and focusing on pure programming. Therefore, I’ve decided to start studying the Ios Developer Career Path on Codecademy for a free 7-day trial. Let’s see how it goes!

Best regards,
The Principal

Write a comment