1 00:00.720 --> 00:01.720 Hello everyone. 2 00:02.200 --> 00:05.320 So let's solve another SQL interview question. 3 00:05.880 --> 00:08.240 What are correlated subqueries? 4 00:08.760 --> 00:11.760 So I will explain you first theoretically. 5 00:12.360 --> 00:17.120 And then after that I will give you one perfect example of correlated subqueries. 6 00:17.760 --> 00:24.600 A correlated subquery is a sub query in SQL that refers to values from outer query. 7 00:25.600 --> 00:32.200 The key difference between correlated subquery and a regular subquery is that correlated subquery is 8 00:32.200 --> 00:36.440 evaluated for each row processed by the outer query. 9 00:36.840 --> 00:37.840 What is that? 10 00:37.880 --> 00:39.760 I will explain you in some time. 11 00:41.120 --> 00:48.000 This makes it dynamic as it can return different results for each row depending on the values of the 12 00:48.040 --> 00:49.160 outer queries. 13 00:50.520 --> 00:54.880 Now what are the key characteristics of the correlated sub queries? 14 00:55.360 --> 00:59.880 So correlated subquery will do the evaluation row by row. 15 01:00.680 --> 01:04.750 The subquery is executed once for each row in the outer query. 16 01:05.670 --> 01:07.390 It is dynamic and dependent. 17 01:07.430 --> 01:13.350 The inner query uses values from the outer query makes it dependent on the outer query. 18 01:13.790 --> 01:18.710 So here inner query and outer query are dependent on each other. 19 01:18.750 --> 01:24.750 They are not independent like normal subqueries, right and used for complex filtering. 20 01:24.870 --> 01:29.430 Correlated subqueries are commonly used for row specific filtering. 21 01:29.430 --> 01:34.630 Ranking calculation based on other related data. 22 01:36.470 --> 01:39.190 Now see I will give you one perfect example. 23 01:39.910 --> 01:43.270 So let me show you the data in the Excel file. 24 01:46.470 --> 01:51.230 So this is my EMP department table ID name department and salary is there. 25 01:51.910 --> 01:54.910 And ID name department and salary. 26 01:54.950 --> 01:55.830 These are my data. 27 01:55.870 --> 01:58.350 I have exported the data into the Excel file. 28 01:59.910 --> 02:01.750 Now what is my requirement? 29 02:01.790 --> 02:02.150 See? 30 02:03.830 --> 02:05.750 Now what is my requirement? 31 02:06.990 --> 02:08.230 Let me write it. 32 02:08.270 --> 02:24.270 Find out the employees whose salary is more than the average salary of the department. 33 02:24.990 --> 02:31.030 Find out the employees whose salary is more than the average salaries of the department. 34 02:31.550 --> 02:39.590 So over here, this statement, if you see this is not directly a sub query, we can write for this 35 02:39.590 --> 02:39.990 one. 36 02:40.910 --> 02:41.350 Right? 37 02:41.630 --> 02:45.950 First of all, we need to find the average salary of the department. 38 02:45.950 --> 02:46.350 Right. 39 02:46.870 --> 02:50.430 First of all let me see you. 40 02:51.870 --> 02:54.950 So if you see this is my data right. 41 02:55.750 --> 02:58.350 Now how many departments are there over here. 42 02:58.910 --> 03:00.390 Different departments are there. 43 03:00.390 --> 03:03.830 So HR department, is there an IT department is there. 44 03:04.350 --> 03:09.780 So now we need to calculate the average salary of the HR department. 45 03:10.300 --> 03:15.380 So what I have done calculate the average salary of the HR department and calculate the average salary 46 03:15.380 --> 03:17.340 of the IT department. 47 03:17.340 --> 03:23.860 This can be done by easily group by statement for just for the sake of resultset. 48 03:23.860 --> 03:29.380 I have done it separately, but now group by is actually not needed over here. 49 03:30.180 --> 03:36.140 See now this is my average salary of particular department. 50 03:36.860 --> 03:45.860 Now I want to find out those employee whose salary is more than the average salary in that particular 51 03:45.860 --> 03:46.740 department. 52 03:47.620 --> 03:50.020 So for that purpose see what I have written. 53 03:50.420 --> 03:52.380 Select star from EMP dept. 54 03:52.460 --> 03:59.620 I have given one alias over here where salary greater than now salary should be greater than the average 55 03:59.620 --> 04:01.540 salary of the particular department. 56 04:01.900 --> 04:08.740 So see over here I have mentioned in the inner part this is known as. 57 04:08.740 --> 04:08.860 Is. 58 04:15.620 --> 04:17.420 Known as correlated subquery. 59 04:17.980 --> 04:21.460 I am finding out the average salary of the EMP department. 60 04:21.500 --> 04:30.780 Table where m p department department is equal to e dot department. 61 04:30.820 --> 04:35.580 Now e dot department is coming from where it is coming from this. 62 04:39.620 --> 04:40.460 Outer query. 63 04:40.460 --> 04:46.420 And this is correlated subquery as well as inner query. 64 04:46.460 --> 04:46.900 Right. 65 04:47.500 --> 04:52.540 So here inner query will execute for each row written by the outer query. 66 04:52.660 --> 04:55.340 So first of all what it will give you. 67 04:55.780 --> 04:58.420 First of all it will give over here HR. 68 05:00.700 --> 05:05.260 You have to just visualize it like that right. 69 05:08.660 --> 05:15.290 So only 40,000 only 40,000 salary in the air is more than the average salary. 70 05:15.330 --> 05:17.250 See what is the average salary? 71 05:17.250 --> 05:18.370 31,000. 72 05:18.770 --> 05:21.650 So now it will pick up first this row. 73 05:21.970 --> 05:22.290 Right. 74 05:22.330 --> 05:24.010 It will pick up first this row. 75 05:24.770 --> 05:27.930 So is 40,000 is greater than this 31,000. 76 05:27.970 --> 05:28.330 Yes. 77 05:28.330 --> 05:28.770 True. 78 05:29.450 --> 05:30.890 So it will return me. 79 05:31.650 --> 05:33.610 Let me copy the output. 80 05:33.610 --> 05:34.850 This will be our output. 81 05:34.850 --> 05:35.290 Right. 82 05:39.490 --> 05:42.610 So 40,000 is greater than the 31,000. 83 05:43.170 --> 05:44.450 So that is correct. 84 05:44.650 --> 05:49.090 Now it will go to the second row in the HR department. 85 05:50.090 --> 05:53.130 And that particular person salary is 25,000. 86 05:53.410 --> 06:01.650 Thus 25,000 is greater than 35,000 is greater than the average salary of that particular HR department. 87 06:01.690 --> 06:02.290 No. 88 06:03.330 --> 06:03.890 Right. 89 06:04.490 --> 06:05.930 Same for this one. 90 06:05.930 --> 06:07.490 So it will give give me for HR. 91 06:07.530 --> 06:09.010 It will give me only one row. 92 06:09.370 --> 06:10.610 And which is this one. 93 06:10.610 --> 06:11.090 Right. 94 06:15.010 --> 06:15.930 Which is this round? 95 06:15.970 --> 06:17.650 Now it will execute for the. 96 06:21.690 --> 06:23.370 IT department here. 97 06:36.010 --> 06:38.570 Here we need to specify the IT over here. 98 06:38.570 --> 06:41.810 So C101 and 107. 99 06:42.250 --> 06:46.170 So now 107 and 101. 100 06:46.170 --> 06:51.530 So both of these person salary is more than the average salary of the IT department. 101 06:52.170 --> 06:54.290 Now see this one zero for it. 102 06:54.330 --> 07:01.410 When it will evaluate one zero for so does the one zero for employee salary is greater than the average 103 07:01.410 --> 07:05.730 salary of the IT department because this person is a IT department, right? 104 07:06.090 --> 07:08.170 That is why we are returning over here. 105 07:08.370 --> 07:14.210 Department comparing department of inner query with the department of outer query. 106 07:16.120 --> 07:18.360 And now this one is not required. 107 07:18.360 --> 07:18.760 Right. 108 07:19.320 --> 07:22.640 So now see it will give me three result. 109 07:22.640 --> 07:24.600 So it will give me three rows. 110 07:24.720 --> 07:31.280 So see for HR it will give me 40,000 and for rest 55 and 50,000. 111 07:31.920 --> 07:34.800 So this is how correlated subquery will work. 112 07:35.440 --> 07:39.600 We cannot independently execute this particular query. 113 07:39.640 --> 07:41.880 See it will give me some error right. 114 07:44.640 --> 07:45.160 So. 115 07:56.600 --> 08:00.880 Correlated subquery cannot be executed independently right. 116 08:03.480 --> 08:05.200 Also it is dependent. 117 08:08.600 --> 08:12.680 It is executed for each row. 118 08:19.160 --> 08:25.240 Each row result given by the outer query. 119 08:25.640 --> 08:27.400 And because of it is, uh. 120 08:29.680 --> 08:36.600 Because of it is, uh, actually quoted for each row. 121 08:40.480 --> 08:45.520 Performance wise, it is slow and. 122 08:48.200 --> 08:58.640 People are rarely use it until we do not have any other. 123 09:00.840 --> 09:01.520 Option. 124 09:01.520 --> 09:02.040 Right. 125 09:02.880 --> 09:12.520 So this is what you have to explain when someone asks regarding to correlated subquery. 126 09:13.360 --> 09:13.640 Right. 127 09:13.640 --> 09:17.480 We will see few more questions in the coming video. 128 09:17.480 --> 09:18.200 Thank you.