Original subtitles

0 Hi everyone 1

In this lecture we will be discussing about subqueries 2

Subqueries are a very powerful tool and you must know this 3

What is a subquery? It is a 4

Query within a query 5

Till now we've been retrieving raw data or very simple processed data 6

If you want to retrieve Complex data we might have to use Complex structured query with a lot of 7

Joins and unions 8

This is where subquery comes in 9

You can use a sub query to reduce your efforts 10

Make the query more readable 11

Reduce the time taken and even save some resources on your database 12

Subqueries can reside within the where clause 13

The from clause or the select clause 14

Syntax I have provided here 15

Is how to use the subquery in the where clause 16

It is like a normal query it is starting with select column names from table name 17

Where you specify the column name 18

Give a comparison operator 19

And after this comes the query 20

This query is part of the main query the outer query 21

And this part is called the sub query 22

Where you write select the column names from table name and give the where condition 23

the output of this subquery will be used 24

as values in the comparison operator 25

Against which the column name 2 the column name 2 written here 26

The values of this column will be compared 27

It will become more clear when we look at the examples 28

Let us say you want to find out 29

the order lines or the sales items 30

where the customer who bought those orders is aged more than 60 31

If we do not know about subqueries how would we do this. We will first find out all the customers 32

Who are aged more than 60 33

Probably store it in a new table 34

and then we'll take an inner join of this sales table and the new table that we have created 35

to find out customers whose age is 60 and who have given orders 36

instead of doing this we'll just use the subquery. first we will find out all the sales 37

From the sales table 38

In the condition we will say 39

Only those customer IDs which belong to this sub query 40

I need this subquery will find out those customer IDs 41

Where customer's age is more than 60 42

so we will get 43

Customer IDs 44

here from this subquery 45

We'll check the customer ID belonging to this set 46

and for only those customer IDs we will get the select result 47

Let's go and write this in the interface 48

So we will select 49

Star from sales 50

Where 51

Customer ID 52

Belongs to 53

where customer ID belongs to a set and we'll define this set by 54

Select 55

customer ID 56

From 57

The customer table 58

Where age is 59

More than 60 60

So if you remember while discussing the IN keyword 61

I told you that I'll be using this IN 62

in the sub queries and there is a lot of 63

Importance of this IN keyword in subqueries. so this is how we use IN 64

during some queries 65

and let us run this query 66

Here you can see 67

I have all the sales 68

Where the customer who has bought is aged more than 60 69

Do note that while selecting in the subquery 70

We had only one column which is customer ID 71

We cannot have the second column because when we are having this IN keyword 72

This IN keyboard wants to compare 73

Customer ID 74

In this 75

range. These are the values that we are giving 76

So it cannot have two columns. It should have only one column 77

Which has the all the values listed in it 78

So this one column 79

Which contains the customer IDs of customers with more than age 60 will be used to compare the customer IDs 80

And give the output from sales table 81

so we just checked how to use the subquery in where 82

Let us now go and learn how to use the subquery within from 83

Now we have a table, product table which list all the products that we have in our inventory 84

Suppose we want to find out how much of each product has been sold 85

I want to find out the quantity of each product sold 86

In the product table I have the product name and categories also 87

So, In future if I have this data I'll be able to find out which category is selling how much 88

So this data may be of use to me 89

If I want to find out this, suppose we are not using the sub queries. I'll first need to find out the sales 90

quantity from the sales table 91

I'll group by 92

the product ID and find out the sum of quantity and save this data in a new table 93

And then I'll do a join of this table with the product table 94

This is how I will get this data 95

What this will lead to is I'll have one additional table which will be taking up resources in my database. Instead of doing this 96

I can do the two things in a single query without saving the data into a table 97

Let us see how subquery helps us in this 98

First the table that we would be saving think of it as a table that you have actually saved 99

That table will be created in the sub query and we'll save that table with some name. you can take out 100

The quantity from the table 101

When we are writing the query we'll select all the columns from the product table 102

And 103

this quantity 104

From the 105

Table that we'll be creating in the subquery 106

After this we'll write the product table and name it as a 107

We will left join this 108

Why we are left joining, you should be knowing 109

All the values of product table will be listed even if it is not part of this second table which will be the subquery 110

So we are using left join 111

This is the 2nd table 112

We'll select the product ID 113

sum the quantity and we group it by the 114

Product ID 115

and we save it as b 116

we are joining these two 117

With the common predicate product ID 118

and we order this with the quantity 119

Let's go and write this and see the result 120

we will select 121

Product ID 122

Product name 123

Add category 124

from the second table 125

That we'll be creating, we need 126

the quantity ok 127

This quantity is actually 128

This quantity 129

Is also an alias so from 130

The table is product 131

which is saved as a 132

After this we will left join 133

this is the subquery part now. select product ID 134

Sum of 135

The quantity 136

As quantity 137

Now we have 138

the Alias 139

From sales 140

Table 141

Grouped by the product ID 142

this whole subquery part 143

Is giving us a table and that table is named as b 144

and we will use the 145

Condition of 146

Product ID of A is same as product ID of B 147

This is the condition that we need to give for the left join 148

Ordered by 149

Quantity in the descending order 150

You can see 151

for 152

We now have all the products 153

from the product table 154

And the quantities of that product sold 155

Mapped against each product 156

you can see that First row has actually null quantity 157

You might be wondering why it is at the top 158

Because null is always considered as the largest number 159

Whenever you order it in descending order Null will always be at top if you order it in ascending order 160

Null will always be at the bottom 161

The same thing you could have done 162

by 163

Creating two different queries 164

Saving the table in between 165

Instead of doing that we just clubbed the two queries 166

One query become a sub query for the other outer query 167

and this saved us some space on the database 168

Now let us look at the third use case of 169

Subquery 170

Here the subquery will be written within the 171

Select part 172

First we wrote it in the where part next we wrote it in the from part now we are writing it in the select 173

part 174

Writing a subquery in the select part is just 175

The same as the left join 176

For example in left join we found out for each order line what is the customer name and age 177

Same thing you can do by using the sub queries also 178

How 179

We'll select customer ID and order line 180

these are directly coming from the sales table 181

but the 3rd column which is the customer name 182

Will come from the customer table and it'll be matched 183

Using the customer ID 184

So customer ID order line 185

And there is a subquery 186

Giving us the customer name 187

from the customer table 188

Let us go and write this in the 189

Interface 190

So we select 191

Customer ID 192

orderline 193

and then we'll write 194

the subquery where we'll find out the customer name 195

Which is matching the customer id from the sales table 196

Customer ID 197

Customer dot customer ID is equal to sales dot customer ID 198

These customer IDs are matched and we get the 199

Customer name 200

from the sales table 201

by customer 202

Select this 203

run it 204

so we get the data 205

These are all the customer IDs in the sales table 206

And wherever customer name is available from the customer table it is mapped here 207

The same thing we did using the 208

Left join 209

However, whenever you have the option of using left join 210

vs subquery 211

I will suggest that we go with the 212

Joins, since they perform better in the database 213

subqueries may take up more resources of the database while performing and it may give result after taking a long time 214

There are few rules 215

I'll mention two or three important ones 216

First is subqueries must be enclosed within parentheses 217

We have been putting brackets around the subqueries 218

So always remember to put brackets 219

and the second point 220

A subquery can have only one column in the select clause 221

Where multiple columns are in the main query for the query subquery to compare date selected column so what this point means is 222

Like we discussed here 223

In this example 224

When we were discussing this IN keyword 225

Here only one column should be present 226

It cannot be be more than one columns 227

That is what this point is signifying 228

So whenever you are comparing values using the subquery 229

It should have only one column 230

Another important point is this last point where between operator 231

It can be used within a subquery 232

It cannot be used with a subquery, so if within a query you are having a subquery you cannot use 233

Between operator like we used IN operator 234

Between cannot be used there 235

But within a subquery you can use between operator 236

These are some important points 237

We will not be discussing other points 238

Just read them. this presentation will be part of the resources of this lecture 239

And that is all for subqueries 240

In the next video we will learn about views and how they are useful