SQL Intermediate: Subqueries

Name: SQL Intermediate: Subqueries
Author: Eric Grose

Master subqueries for dynamic data retrieval

Preview the first lesson free — get full access to all 5 lessons.

Course: On-Demand

In this lesson, we'll focus specifically on correlated subqueries. We'll explore what it is, how it differs from simple queries, and when to use it. Let's begin with a quick review of a correlated subquery. A correlated subquery is a type of subquery that uses values from the outer query, or the main query. In other words, it is executed once each row is processed by the outer query. So for our basic example here, we'll select the player name and the position from the players table. Once our outer query has found those results, it will go ahead and process our subquery. Here, we'll use the WHERE clause with the EXIST keyword to say, once these values exist, go ahead and process our subquery. So for here, we'll say SELECT 1 or FIND ME A ROW if this matches. We'll be using this from the games table, which we've aliased as G. And we'll use the alias from the games table and the P alias from the players table to say that these tables are linked based on the player ID column. Then finally, we'll say where the player has greater than 7 assists. So let's go ahead and run this by pressing CTRL ENTER. And we can see here, it only returns a few players who actually meet this criteria. Based on their structure, correlated subqueries differ from simple or non -correlated subqueries. Simple subquery is independent from the outer query, and it runs only once for the entire operation or the query itself. On the other hand, a correlated subquery is tied to the outer query or the main query, and it runs once for each row processed by the outer query. As you can imagine, this can lead to different results and also mean that the subquery can be more resource intensive. If we try to run this example as a simple subquery, this can lead to different results. And also, as you can imagine, correlated subqueries can be more resource intensive or take more processing power to find all these values. Please take caution when writing these queries as they may execute slower than simple subqueries, particularly when you're on a real -world database with thousands or even millions of rows. So now let's look at another use case. Let's say we want to find all the players who have scored the most points in a game. To accomplish this, we'll use the player's name. We'll use the max function on the points column. We'll go ahead and give this an alias of highest score. Then we'll be using, at first, the players table, which we'll alias as P. Then we'll be using an inner join on the games table, which we've aliased as G. Finally here, we're using the on keyword to allow us to create the link between these tables, which is based on the player ID column, which is present in both of these tables. All of that now just to define our criteria or the conditions that must be met. So here we'll use the where clause with the points. And now we want to do a subquery on our where clause. So here we'll say select max points from the games table, where the player ID equals a player ID from the players table. Then, since we're using an aggregate function or the max function in our select clause, that means we'll have to use a group by clause with our player's name. And lastly, I decided to order by our max points or our highest score alias column in descending order, so we see the highest point score. If we created this query correctly, this should return the players and their highest score in a game. So let's go ahead and run this. And if we look here, we only see one row for each player because this is the highest score they've accumulated within a single game. Although correlated subqueries can be a bit challenging and hard to understand, they're extremely useful for complex data retrieval, particularly for the scenarios that we just laid out. Stay tuned for the next lesson, where we discuss the difference between subqueries and joins. Thanks for watching.

Intermediate Provider : Eric Grose 5 Lessons · 26m in Arabic, English

Course Description

Starting with the basic understanding of what subqueries are and why they're essential, we'll broaden your knowledge by studying various types of subqueries, including single-row, multiple-row, and correlated subqueries. These lessons also provide a detailed comparison between subqueries and joins, aiding you in understanding their pros and cons and making informed decisions about their usage. As we progress, we'll delve deeper into the concept of correlated subqueries, exploring their uniqueness and real-world use cases. By the end of these lessons, you'll be armed with the knowledge and hands-on experience to craft and apply various types of subqueries, enhancing your problem-solving skills in SQL data retrieval.

In these lessons on subqueries, you will learn about the mechanics of SQL subqueries and how they revolutionize data retrieval. We will explore the concept of subqueries, delve into their various types including single-row, multiple-row, and correlated subqueries, and discuss the differences between subqueries and joins. By the end of these lessons, you will be able to craft various types of subqueries and use them effectively to solve complex data retrieval problems.

What You'll Learn

Understand what SQL subqueries are and why they are essential for complex data retrieval
Identify the various types of subqueries, including single-row, multiple-row, and correlated subqueries
Compare subqueries and joins to decide when to use each
Explore correlated subqueries and their real-world use cases
Write and apply subqueries in SQL to solve complex data retrieval problems

Key Takeaways

Subqueries enable complex data retrieval and can revolutionize how data is queried in SQL.
Subqueries come in several types, including single-row, multiple-row, and correlated subqueries.
Comparing subqueries and joins helps you understand their pros and cons and make informed decisions about their usage.
Correlated subqueries have a unique behavior and apply to specific real-world use cases.
The lessons combine conceptual knowledge with hands-on experience for crafting and applying subqueries.

Frequently Asked Questions

What will I learn in this course?

You will learn the mechanics of SQL subqueries, their various types including single-row, multiple-row, and correlated subqueries, the differences between subqueries and joins, and how to craft and apply subqueries to solve complex data retrieval problems.

What types of subqueries does this course cover?

The course covers single-row, multiple-row, and correlated subqueries, and also compares subqueries with joins.

Does this course include hands-on practice?

Yes. The lessons provide hands-on experience so you can craft and apply various types of subqueries, including a lesson on writing subqueries in SQL.

What skills will I gain from this course?

You will gain skills in database programming, extended SQL, hierarchical and recursive queries in SQL, nested queries, query understanding, and SQL as a programming language.

What topics are taught in the lessons?

The lessons include Introduction to Subqueries, Types of Subqueries, Correlated Subqueries, Subqueries vs. Joins, and Writing Subqueries in SQL.