1 00:01.130 --> 00:05.090 Today we are starting one new topic that is a joints. 2 00:08.630 --> 00:11.210 So let's directly start with one. 3 00:11.390 --> 00:15.560 One very good example which is frequently asked in the interview. 4 00:15.740 --> 00:18.470 Then we will see further as well. 5 00:18.800 --> 00:24.050 So mainly what are the different types of joints are available in SQL. 6 00:24.080 --> 00:31.970 We can say inner join then left left join, right join then full outer join. 7 00:32.060 --> 00:36.050 Then we can say cross join and self join as well. 8 00:36.860 --> 00:41.060 So these are the most widely used join types in SQL. 9 00:41.570 --> 00:44.690 So let's start with one example directly. 10 00:44.690 --> 00:46.700 And then we will see further. 11 00:47.330 --> 00:50.120 So I have this two table available with me. 12 00:50.210 --> 00:54.800 This kind of SQL question is asked in many interviews. 13 00:54.800 --> 00:57.530 So let's directly start with that. 14 00:58.610 --> 01:01.950 I have copied the data into this Excel file. 15 01:02.010 --> 01:04.080 So this is my table A. 16 01:07.770 --> 01:11.250 And this is my table B, right. 17 01:11.760 --> 01:20.760 So over here, uh, if you see there are uh, some values are repeating multiple times and few couple 18 01:20.790 --> 01:24.330 of values are null over here in id column. 19 01:24.330 --> 01:26.670 And only one one column is there. 20 01:27.090 --> 01:30.720 And over here also some values are repeating multiple times. 21 01:30.720 --> 01:34.200 And two of null values are there. 22 01:34.200 --> 01:41.280 So we have to identify how many records it will give in inner join how many it will give in right left 23 01:41.280 --> 01:44.670 join, how many it will give in left join. 24 01:44.670 --> 01:45.180 And. 25 01:47.850 --> 01:49.470 Full outer. 26 01:52.320 --> 01:53.280 Join. 27 01:54.150 --> 02:01.220 So they are asking this kind of questions They Table gave you two tables with one column only, and 28 02:01.220 --> 02:04.820 they will ask that how many record it will give in inner join? 29 02:04.850 --> 02:06.410 How many in left join? 30 02:06.410 --> 02:08.210 How many in full outer join. 31 02:09.620 --> 02:20.120 So over here I just written this simple query like inner join, left join, right join and full outer 32 02:20.120 --> 02:20.780 join. 33 02:20.870 --> 02:25.190 So let's directly see over here how many it will give in inner join. 34 02:25.820 --> 02:29.450 So this one will be first. 35 02:30.140 --> 02:33.800 First join with all these 331 right. 36 02:33.800 --> 02:36.800 So it will give me three records over here. 37 02:36.830 --> 02:37.640 Right. 38 02:38.330 --> 02:41.510 Then again this one will be joined with. 39 02:44.720 --> 02:46.700 A join with this three one. 40 02:46.700 --> 02:46.970 Right. 41 02:47.000 --> 02:47.750 Again. 42 02:47.840 --> 02:51.320 So again it will give me three more records. 43 02:52.130 --> 02:55.340 Then these two will be joined with 44 02:58.470 --> 03:02.970 Phase two will be joined with these two and another two. 45 03:03.000 --> 03:05.610 This one will be also joining with these two. 46 03:06.150 --> 03:08.040 So it will give me two more records. 47 03:08.040 --> 03:08.520 Right. 48 03:08.550 --> 03:09.930 Two more records. 49 03:10.530 --> 03:13.410 Then these three will be joining with these three. 50 03:13.410 --> 03:15.180 So it will give me one record. 51 03:16.890 --> 03:23.910 Then four will be joining with this for one time and second time also for with these four right. 52 03:25.320 --> 03:28.440 So it will give me again two records. 53 03:30.030 --> 03:40.170 Then five will be joining with one time with this 510 second time with this five and third time with 54 03:40.170 --> 03:41.070 this five. 55 03:41.130 --> 03:46.770 So it will give me three more records then six and seven. 56 03:46.770 --> 03:48.510 Seven cannot be joined, right? 57 03:48.540 --> 03:55.110 Six and seven cannot be joined with any other record because over here six and seven are not there. 58 03:55.110 --> 03:55.710 Right 59 04:02.860 --> 04:05.860 Because over here, six and seven are not there. 60 04:05.980 --> 04:07.870 So how many? 61 04:07.900 --> 04:10.360 We need to do some of this one, right. 62 04:14.290 --> 04:14.650 Okay. 63 04:14.680 --> 04:17.560 One minute, one minute. 64 04:17.590 --> 04:18.100 This three. 65 04:18.100 --> 04:26.710 So it will be three record then one will this three three plus three six then seven eight. 66 04:28.900 --> 04:29.320 Okay. 67 04:29.350 --> 04:31.240 This three will give me actually two records. 68 04:31.240 --> 04:31.480 Right. 69 04:31.510 --> 04:35.380 This three will give me two records because this is one three. 70 04:37.870 --> 04:45.100 And over here two times three are there right now let then this four again two records this five three 71 04:45.130 --> 04:45.760 record. 72 04:45.760 --> 04:49.060 So now we need to do some of all this. 73 04:49.090 --> 04:51.280 And it will be 15 records. 74 04:53.200 --> 04:54.910 It will be 15 records. 75 04:54.920 --> 05:01.190 So let me run this query and then see how many records are given in the inner join. 76 05:05.780 --> 05:08.450 Say it is giving me 15 records. 77 05:08.480 --> 05:09.650 Exactly right. 78 05:09.950 --> 05:12.350 What I have mentioned over here. 79 05:17.390 --> 05:19.400 It will give me 15 records. 80 05:20.150 --> 05:20.960 Now what? 81 05:20.990 --> 05:22.550 Left join will give me. 82 05:22.640 --> 05:28.760 So left join will give me 15 records plus additional two records. 83 05:28.790 --> 05:30.050 This two records. 84 05:31.520 --> 05:39.260 So in left, join all the matching records from both the tables and non matching records from the left 85 05:39.290 --> 05:39.920 table. 86 05:39.920 --> 05:42.680 So these two records will be additionally added. 87 05:43.610 --> 05:47.510 And again this one null and this one null. 88 05:47.600 --> 05:51.170 So two two null values will also be added. 89 05:52.220 --> 05:54.250 Two null values will also be added. 90 05:54.250 --> 05:54.880 Right? 91 05:55.510 --> 05:58.660 So 15 plus two plus two. 92 05:58.720 --> 06:04.810 It will be 19 records in left joint right. 93 06:07.090 --> 06:10.330 19 records will be in the left joint. 94 06:10.420 --> 06:12.460 So let's see over here. 95 06:18.580 --> 06:20.500 It is giving me 19 records. 96 06:20.500 --> 06:20.800 Right. 97 06:20.830 --> 06:21.790 Exactly. 98 06:21.970 --> 06:27.700 And over here if you drop down then one null record and another null null record. 99 06:27.700 --> 06:35.350 Also it is giving in six and seven because six and seven is not present in the right table in this particular 100 06:35.350 --> 06:36.430 right table. 101 06:36.460 --> 06:39.850 That is why all null is coming over here as well. 102 06:39.880 --> 06:40.510 Right? 103 06:41.080 --> 06:43.420 Null will be coming over here as well. 104 06:44.710 --> 06:47.590 So that is why it is giving me 19 record. 105 06:47.740 --> 06:53.990 Then what will it will give me in the right join what thing it will give me in the right join. 106 06:54.020 --> 06:54.590 Again. 107 06:54.590 --> 06:55.160 Right join. 108 06:55.160 --> 06:57.080 15 record will be common. 109 06:57.650 --> 06:59.600 These 15 record will be there. 110 06:59.930 --> 07:01.970 In these two record will be extra. 111 07:01.970 --> 07:04.280 So again these two record will be extra. 112 07:04.550 --> 07:06.350 And again this one null. 113 07:06.350 --> 07:08.480 And this one null will be extra. 114 07:11.180 --> 07:16.430 So again it will be 15 plus 217 plus 219. 115 07:18.350 --> 07:18.920 Right. 116 07:18.920 --> 07:24.260 It will give me 19 record in case of right join as well. 117 07:25.970 --> 07:28.430 So let me execute the query. 118 07:32.360 --> 07:34.220 It is giving me 19 record. 119 07:34.250 --> 07:34.730 Exactly. 120 07:34.730 --> 07:36.710 And this time what it will happen. 121 07:36.740 --> 07:42.110 It will give me eight and nine and null null values over here in the left table. 122 07:46.490 --> 07:53.010 Right now what will be in the case of full join in the case of full join. 123 07:58.560 --> 08:00.540 It will be 19 plus only. 124 08:00.570 --> 08:00.840 Right? 125 08:00.870 --> 08:05.940 19 plus only because this two record will be additionally added. 126 08:07.560 --> 08:12.420 And this two record will also be additionally added. 127 08:13.140 --> 08:17.790 This two record I am talking about and this two record I am talking about. 128 08:18.900 --> 08:24.210 So 19 plus 21 plus 223 record. 129 08:24.240 --> 08:25.950 It should be ideally given. 130 08:26.670 --> 08:28.980 So let me execute this query. 131 08:33.810 --> 08:34.500 Perfect. 132 08:34.500 --> 08:38.430 It is giving me 23 record over here six three. 133 08:38.460 --> 08:39.900 It will give me null null. 134 08:39.900 --> 08:42.540 And over here eight nine it will give me null null. 135 08:48.240 --> 08:53.540 So now I will unmute you for two minutes if you have any question you can ask. 136 08:55.100 --> 08:56.990 Anyone has any question. 137 09:02.390 --> 09:04.010 This is the scenario. 138 09:07.490 --> 09:11.450 This question is frequently asked in interview in. 139 09:11.450 --> 09:15.080 Recently I have given two interviews in that also. 140 09:15.110 --> 09:16.760 This question is asked. 141 09:19.310 --> 09:20.510 Every you know. 142 09:20.840 --> 09:26.120 In fact, I had also attempted one interview I think a month back and same question was asked. 143 09:26.270 --> 09:28.880 There are yes, yes. 144 09:28.910 --> 09:33.680 And they will, uh, intentionally put null values in the column. 145 09:34.130 --> 09:35.270 Yeah, intentionally. 146 09:35.270 --> 09:38.870 They will put null values in the table and confuse us. 147 09:43.700 --> 09:45.590 Now the solution is clear, right? 148 09:45.830 --> 09:46.700 Everyone 149 09:51.210 --> 09:51.660 All right. 150 09:55.920 --> 10:03.210 Okay, now let's further, uh, in case of null, uh, you know, if you have a two null, it is taking 151 10:03.240 --> 10:04.050 two null. 152 10:04.560 --> 10:12.570 Um, the question is, uh, even if it is going to be multiple null is there it will take will it consider 153 10:12.600 --> 10:13.710 null or at least. 154 10:14.820 --> 10:21.420 Uh, yes, it will consider null as well because uh what it will happen null. 155 10:21.450 --> 10:24.240 Actually it is not joining null with this null. 156 10:24.240 --> 10:29.190 It is not joining like that because of null is additional over here. 157 10:29.220 --> 10:36.870 And in the SQL or any relational database, you can say that null is not equal to null. 158 10:37.500 --> 10:39.030 Two nulls are never same. 159 10:39.030 --> 10:42.420 So in this particular left join what is happening? 160 10:42.480 --> 10:49.120 This null will become because all the matching rows from the left table and this null is not matching 161 10:49.150 --> 10:49.540 rows. 162 10:49.570 --> 10:51.280 This null is not matching rows. 163 10:51.280 --> 10:57.700 So what will be the output it will give me like this. 164 10:59.380 --> 11:00.100 It will give. 165 11:04.960 --> 11:11.770 It will give me like uh when you we have join then this null is not matching with this null. 166 11:11.770 --> 11:13.990 And also it is not matching with this null. 167 11:13.990 --> 11:18.820 So whenever there is no match it will automatically put over here null only. 168 11:19.780 --> 11:23.740 Again this six six is not matching with any of this record right. 169 11:23.740 --> 11:25.840 So again it will put null. 170 11:26.140 --> 11:29.350 Again this seven is null matching matching with any other record. 171 11:29.350 --> 11:31.600 So it will put null over here. 172 11:31.600 --> 11:35.110 And again this null is not matching with any of the record. 173 11:35.890 --> 11:38.320 So again it will put null only. 174 11:39.040 --> 11:43.480 But when we do inner join it will ignore null because. 175 11:43.480 --> 11:43.780 Why. 176 11:43.810 --> 11:44.800 Because of this. 177 11:44.830 --> 11:48.680 This sentence Null is not equal to null. 178 11:48.710 --> 11:50.270 Because of that reason. 179 11:53.600 --> 11:54.080 Got it. 180 11:54.080 --> 11:54.560 Clear? 181 11:55.910 --> 11:56.210 Yeah. 182 11:56.240 --> 11:57.080 Okay. 183 11:58.310 --> 12:02.360 And we have the left outer and right outer joins also there now. 184 12:02.750 --> 12:03.350 Yeah. 185 12:03.380 --> 12:07.430 Actually that are same only left outer join and left join. 186 12:07.430 --> 12:08.030 Both are same. 187 12:08.030 --> 12:11.120 Only if you mention over here outer keyword. 188 12:11.300 --> 12:15.170 Then also it will give me the same same result 19 record. 189 12:16.220 --> 12:24.860 And if you do not mention outer keyword then also it will be give me same same result 19 record also 190 12:24.860 --> 12:25.790 full outer join. 191 12:25.790 --> 12:28.610 If you let me try full join. 192 12:30.680 --> 12:32.540 It will give me same result only. 193 12:34.280 --> 12:37.040 So outer keyword is basically optional. 194 12:37.070 --> 12:38.990 Outer keyword is basically optional. 195 12:38.990 --> 12:41.270 If you mention then also fine. 196 12:41.270 --> 12:43.760 If you do not mention then also fine 197 12:47.740 --> 12:48.160 Yeah. 198 12:49.330 --> 12:49.810 Okay. 199 12:50.860 --> 12:51.190 Thank you. 200 12:51.580 --> 12:52.210 Okay. 201 12:52.240 --> 12:52.480 Yeah. 202 12:52.510 --> 12:54.190 Let's move forward. 203 13:00.040 --> 13:03.850 So now let's see few more examples. 204 13:06.370 --> 13:08.980 So this is basic theory of joints. 205 13:08.980 --> 13:11.650 Uh, it is written over here. 206 13:11.650 --> 13:13.690 What are the different types of joints. 207 13:13.690 --> 13:19.240 So we have seen left joint that is also we can consider sometimes as equi join as well. 208 13:19.720 --> 13:24.640 Then after join left outer join full outer join right outer join. 209 13:24.640 --> 13:26.800 So this outer keyword is optional. 210 13:27.070 --> 13:30.460 Then we can also say that non-equity join is there. 211 13:31.270 --> 13:36.070 non-Equity joins means it is not based on equality operator. 212 13:41.050 --> 13:43.000 Not based on equality operator. 213 13:43.030 --> 13:45.170 Then it is known as non equi join. 214 13:45.200 --> 13:48.470 Then we can say that cross join is also there. 215 13:48.650 --> 13:51.290 It is also known as Cartier. 216 13:54.110 --> 13:56.240 Cartesian product also. 217 13:56.810 --> 13:59.270 Then self join is also there. 218 13:59.540 --> 14:02.570 But in Oracle there is a I think self join is there. 219 14:02.570 --> 14:06.950 But over here we are writing self join with different manner. 220 14:06.950 --> 14:12.680 So we will see that example also in some time. 221 14:12.710 --> 14:16.400 And now join we are write writing in two different ways. 222 14:16.580 --> 14:22.370 One is Ansi SQL 89 and one is Ansi SQL 92. 223 14:22.490 --> 14:28.850 So this is actually very old way old way of writing joins. 224 14:29.030 --> 14:32.510 And it is not recommended. 225 14:34.070 --> 14:35.300 It is not recommended. 226 14:35.300 --> 14:40.700 We are mostly writing this Ansi SQL 92 uh, only 227 14:43.890 --> 14:45.870 Now let's see. 228 14:46.980 --> 14:50.520 So we have these three, uh, few tables are there. 229 14:51.390 --> 14:54.720 Like, let me show you the data. 230 14:56.280 --> 14:58.050 Select star from. 231 15:03.420 --> 15:06.690 And then select star from vendors. 232 15:12.990 --> 15:13.770 Okay. 233 15:13.800 --> 15:16.800 So over here what is our requirement. 234 15:17.820 --> 15:19.170 We have v id column. 235 15:19.170 --> 15:20.340 Column is common right. 236 15:20.340 --> 15:22.800 In both the table v id column is common. 237 15:23.370 --> 15:29.100 And we want some of the some of the columns from this table and some of the columns from this table. 238 15:29.130 --> 15:32.370 And over here one, two and three different v ID are there. 239 15:32.400 --> 15:37.320 And all these three different v id are present in second table vendor table. 240 15:37.410 --> 15:41.040 So when we do inner join it will give me all the three. 241 15:41.280 --> 15:46.760 All the records from the, uh, we can say it will ideally give nine records. 242 15:46.790 --> 15:47.390 Eight records. 243 15:47.390 --> 15:47.960 Right. 244 15:48.410 --> 15:50.330 Let me execute this one. 245 15:50.360 --> 15:50.960 Okay. 246 15:50.960 --> 15:52.550 It will give me all the eight records. 247 15:52.550 --> 15:53.150 Why? 248 15:53.900 --> 15:56.900 Because all the products are available. 249 15:56.930 --> 15:57.410 Products? 250 15:57.410 --> 16:00.590 Vendor ID are available in the vendor vendor table. 251 16:00.620 --> 16:01.220 Right? 252 16:01.790 --> 16:08.720 So that is why it will give me all the records from this left table products table. 253 16:09.320 --> 16:11.420 Now let's move ahead. 254 16:14.300 --> 16:19.880 Now let us see this when we execute this particular query. 255 16:20.120 --> 16:27.590 So if you query very carefully execute see this table v id column is present v id column is present 256 16:27.590 --> 16:29.540 in these two both the table. 257 16:29.630 --> 16:35.120 So when we do not mention any alias then at that time it will give me error like this. 258 16:36.980 --> 16:44.850 It will give me error like this ambiguous column name v id y ambiguous because it is not able to identify 259 16:44.940 --> 16:48.960 from which which table I want this particular column. 260 16:50.310 --> 16:58.890 So to have the solution, we can either mention like this products v dot dot this particular table. 261 16:58.920 --> 17:01.590 So it will give me perfect result right. 262 17:06.930 --> 17:09.180 It will give me perfect result. 263 17:13.320 --> 17:15.240 Now let's move ahead. 264 17:21.120 --> 17:26.220 And ideally ideally we do not mention this entire table name. 265 17:26.760 --> 17:30.780 Or like over here in the alias this is a alias right. 266 17:30.810 --> 17:31.500 Ideally. 267 17:31.860 --> 17:38.220 But for that purpose what we are doing, we are giving some alias to this particular table and giving 268 17:38.220 --> 17:40.390 some alias to this particular table. 269 17:40.540 --> 17:48.700 And we can call the table and columns from this particular table with alias name like p dot v id and 270 17:48.700 --> 17:50.500 v dot v id. 271 17:50.800 --> 17:54.310 So basically improve it will improve the performance. 272 17:55.180 --> 17:58.030 It will basically improve the performance. 273 18:01.360 --> 18:12.040 Now let us see how we can write Ansi SQL 89 which is actually not recommended right. 274 18:12.070 --> 18:18.100 So in the Ansi SQL 89 we are writing the joins like this. 275 18:18.460 --> 18:22.720 We are not mentioning the Or condition we are directly created. 276 18:22.720 --> 18:27.010 Comma directly join two table with comma only. 277 18:27.040 --> 18:32.620 See over here directly we have join two tables with comma only and. 278 18:36.760 --> 18:42.980 And in our Uh and uh, Ansi SQL 92. 279 18:43.010 --> 18:45.980 What we are doing, we are joining with some keyword. 280 18:45.980 --> 18:46.280 Right. 281 18:46.310 --> 18:47.240 Inner join. 282 18:50.270 --> 18:50.960 On. 283 18:51.380 --> 18:52.250 Right. 284 18:52.250 --> 18:55.730 So both of these query will be ideally giving me the same result. 285 18:55.760 --> 18:56.360 Right. 286 18:56.540 --> 18:58.820 Both of these query will give me same result. 287 18:58.850 --> 19:02.390 See eight row over here in eight row over here as well. 288 19:02.420 --> 19:03.080 Right. 289 19:04.100 --> 19:09.680 So this is Ansi SQL 89 and below one is Ansi SQL 92. 290 19:13.820 --> 19:19.070 And this is nowadays our most mostly preferred. 291 19:23.870 --> 19:30.560 This is nowadays are mostly preferred right now join more than two tables. 292 19:32.210 --> 19:39.730 So we can also joining more than two tables like the this this way as well from this particular table. 293 19:39.760 --> 19:40.630 Then. 294 19:44.260 --> 19:46.870 This is my, uh, second joint. 295 19:46.870 --> 19:47.710 And this is. 296 19:49.330 --> 19:51.370 This is my third joint. 297 19:51.400 --> 19:54.850 So we can join multiple tables as well. 298 19:56.410 --> 19:58.960 And this is again Ansi SQL 92. 299 19:58.960 --> 20:02.590 And this is Ansi SQL 89. 300 20:02.620 --> 20:02.830 Why? 301 20:02.860 --> 20:06.610 Because all the joints are joining with comma only right. 302 20:06.640 --> 20:08.950 All the joints are joining with comma only. 303 20:08.980 --> 20:11.380 So let me execute this query. 304 20:12.790 --> 20:15.520 And currently we are doing the inner joins only right. 305 20:16.000 --> 20:20.650 So it will give me data from the orders table products table and vendors table. 306 20:21.760 --> 20:22.780 All the three tables. 307 20:22.780 --> 20:24.130 It will give me data. 308 20:27.580 --> 20:28.840 Now 309 20:36.260 --> 20:37.520 So now let's see. 310 20:37.550 --> 20:41.510 Show me the list of products sold and also their quantity sold. 311 20:41.690 --> 20:45.470 Show me the list of products sold and also their quantity sold. 312 20:45.500 --> 20:48.260 So if you see over here both the tables. 313 21:01.160 --> 21:09.920 If you see over here, both the tables, then we have to do the join of this multiple multiple this 314 21:09.950 --> 21:11.120 vid column. 315 21:12.440 --> 21:15.590 Sorry this PID column with this PID column. 316 21:15.590 --> 21:16.160 Right. 317 21:16.310 --> 21:19.280 This PID column with this PID column. 318 21:19.550 --> 21:23.270 So if you do the join between these two. 319 21:29.630 --> 21:30.080 Legs 320 21:37.530 --> 21:41.550 Then if you see this PID is coming two times over here, right? 321 21:41.610 --> 21:43.680 So how it will perform. 322 21:45.210 --> 21:48.600 We want PID PID p name and quantity. 323 21:48.630 --> 21:48.960 Right. 324 21:48.990 --> 21:50.640 PID p name and quantity. 325 21:50.670 --> 21:52.740 So let me copy this three column. 326 21:58.830 --> 22:02.640 PID p name and quantity. 327 22:05.580 --> 22:11.190 So PID one will be joining with this PID one and also two times right. 328 22:11.190 --> 22:14.130 So p name we can say Lux. 329 22:16.080 --> 22:17.640 Lux will repeat over here. 330 22:17.640 --> 22:19.650 And it will be ten and five. 331 22:21.630 --> 22:24.060 Then two two and Rexona. 332 22:24.060 --> 22:28.260 So I need to copy this two and Rexona and quantity will be. 333 22:30.840 --> 22:36.200 This ten will be come over here Then three, four and five. 334 22:36.200 --> 22:42.680 So let me copy all this together three, four and five and let me copy price of three, four and five 335 22:42.680 --> 22:43.520 over here. 336 22:44.330 --> 22:46.460 So this is how joining will be work right. 337 22:46.460 --> 22:48.230 This is how joining will be work. 338 22:48.230 --> 22:50.870 So it will give me ideally six rows. 339 22:51.440 --> 22:53.900 Let me execute this query. 340 22:56.870 --> 23:00.170 Perfect one minute one to. 341 23:02.240 --> 23:03.740 It is giving me five rows. 342 23:03.770 --> 23:06.200 Why 112. 343 23:08.960 --> 23:09.620 Two. 344 23:09.650 --> 23:10.580 Two two. 345 23:13.490 --> 23:13.880 Okay. 346 23:13.880 --> 23:14.600 Product okay. 347 23:14.600 --> 23:16.940 Product I okay I have done wrong. 348 23:18.410 --> 23:19.340 One minute. 349 23:22.070 --> 23:22.970 One one. 350 23:23.000 --> 23:25.010 Then we need to see this PID. 351 23:25.250 --> 23:25.880 Okay. 352 23:25.910 --> 23:27.590 This one then five and six. 353 23:27.590 --> 23:27.800 Right. 354 23:27.800 --> 23:31.400 We need to copy five and six over here. 355 23:31.400 --> 23:36.030 And we need to copy this one. 356 23:36.990 --> 23:37.380 Okay. 357 23:37.380 --> 23:41.640 Now this result and our table results both will be same. 358 23:41.670 --> 23:42.210 Ten. 359 23:42.240 --> 23:42.480 Five. 360 23:42.510 --> 23:42.750 Ten. 361 23:42.780 --> 23:42.990 One. 362 23:43.020 --> 23:43.530 Two. 363 23:43.560 --> 23:44.640 So see over here. 364 23:44.640 --> 23:44.880 Ten. 365 23:44.910 --> 23:45.120 Five. 366 23:45.150 --> 23:45.390 Ten. 367 23:45.420 --> 23:45.600 One. 368 23:45.630 --> 23:45.840 Two. 369 23:45.870 --> 23:46.110 And. 370 23:46.140 --> 23:46.650 Lux. 371 23:46.650 --> 23:47.250 Lux. 372 23:47.310 --> 23:48.090 Rexona. 373 23:48.120 --> 23:48.870 Horlicks. 374 23:48.900 --> 23:50.190 -- Complan. 375 23:50.820 --> 23:51.300 Right. 376 23:51.330 --> 23:53.010 It is giving perfect result now. 377 23:53.040 --> 23:55.680 But now over here there will be one problem. 378 23:55.680 --> 23:56.460 Quantity. 379 23:56.490 --> 23:57.690 This one. 380 23:57.690 --> 24:00.120 And Lux is coming two times this one. 381 24:00.120 --> 24:01.950 And Lux is coming two times. 382 24:01.980 --> 24:02.970 So now what? 383 24:02.970 --> 24:04.590 We have to do this ten and five. 384 24:04.620 --> 24:06.180 We need to do addition. 385 24:06.480 --> 24:09.000 So how we can do the addition over here. 386 24:09.000 --> 24:13.500 We have to ideally simply we need to do group by p id and p name. 387 24:13.500 --> 24:14.040 Right. 388 24:14.070 --> 24:15.930 Group by p id and pre name. 389 24:15.930 --> 24:19.080 And then we need to do some of quantity. 390 24:19.110 --> 24:21.300 We need to do some of quantity. 391 24:21.480 --> 24:22.980 So see over here. 392 24:25.320 --> 24:27.270 Ten 510 one two. 393 24:27.990 --> 24:32.290 Now if I do the sum of quantity let me execute this query. 394 24:32.740 --> 24:34.810 It will become four rows now, right? 395 24:34.810 --> 24:36.010 It will become four rows. 396 24:36.040 --> 24:36.640 Why? 397 24:36.760 --> 24:40.240 Because this two row will be converted to single row only. 398 24:40.240 --> 24:40.840 Right? 399 24:41.470 --> 24:41.950 Why? 400 24:41.980 --> 24:46.150 Because we have done the group by and some of this ten and five. 401 24:46.180 --> 24:47.680 So it will be 15. 402 24:47.680 --> 24:51.010 And all these four row will be same as it is. 403 24:51.040 --> 24:51.610 Right. 404 24:51.610 --> 24:54.040 All these four row will be same as it is. 405 24:56.860 --> 24:57.130 Okay. 406 24:57.130 --> 24:58.810 Now let's move ahead. 407 24:59.830 --> 25:04.750 Now what is our requirement from this particular result set. 408 25:06.850 --> 25:12.460 We want only only uh, where quantity should be greater than. 409 25:15.760 --> 25:19.930 Or some of quantity should be greater than 15 and ten or greater than. 410 25:19.930 --> 25:20.560 Ten. 411 25:20.980 --> 25:22.840 Greater than equal to ten. 412 25:22.870 --> 25:28.270 So in this particular case it should be this two record will be retrieved. 413 25:28.270 --> 25:28.690 Right. 414 25:28.690 --> 25:30.860 This two record will be retrieved. 415 25:34.940 --> 25:37.280 So over here, what will happen? 416 25:37.280 --> 25:40.160 We are filtering based upon the group, right. 417 25:40.190 --> 25:45.920 We are filtering based upon the sum of quantity. 418 25:45.950 --> 25:46.250 Right. 419 25:46.280 --> 25:50.210 In this particular query we have mentioned sum of quantity. 420 25:50.240 --> 25:53.750 And now we are filtering on this particular rows. 421 25:53.750 --> 25:56.930 So that is why we need to write having clause right. 422 25:57.680 --> 25:59.990 That is why we need to write having clause. 423 25:59.990 --> 26:04.010 So how we can write having we can ideally write it like this. 424 26:04.220 --> 26:13.190 Having o dot quantity or a sum of O dot quantity greater than or equal to ten. 425 26:13.220 --> 26:16.970 So if I write like this then it will give me only two records. 426 26:17.900 --> 26:21.260 Then it will give me only two records which I have mentioned over here. 427 26:21.290 --> 26:22.400 Lux and Rexona. 428 26:22.400 --> 26:23.000 Right. 429 26:24.380 --> 26:26.060 Lux and Rexona 430 26:32.770 --> 26:34.900 Now what is a cross join? 431 26:34.930 --> 26:39.100 So cross join or Cartesian join returns the cross product of two tables. 432 26:39.130 --> 26:44.770 And if the cross join is performed between two tables, then each record of the first table is joined 433 26:44.770 --> 26:47.560 with each and every record of the second table. 434 27:03.580 --> 27:04.270 Meals. 435 27:09.190 --> 27:14.350 Now, basically in this kind of scenario, cross join is mainly used. 436 27:14.770 --> 27:17.350 Suppose we are going in the restaurant, right? 437 27:17.380 --> 27:21.160 Suppose we are going in the restaurant and they are selling. 438 27:21.370 --> 27:24.910 They are selling drinks as well as as meals as well. 439 27:24.940 --> 27:25.510 Right. 440 27:25.600 --> 27:27.580 Drinks and meals as well. 441 27:27.620 --> 27:30.950 But now suppose there are few items. 442 27:30.980 --> 27:32.690 Few items are based upon the. 443 27:32.960 --> 27:34.070 Combinations. 444 27:34.100 --> 27:37.370 Suppose someone wants to eat the combo meals. 445 27:37.400 --> 27:38.510 Someone wants to. 446 27:38.540 --> 27:40.520 Want to eat the combo meals? 447 27:40.790 --> 27:42.140 Then there will be some. 448 27:42.350 --> 27:43.670 Some one table is for. 449 27:43.700 --> 27:44.810 Drinks table. 450 27:44.840 --> 27:47.270 One table will be for milk table. 451 27:47.300 --> 27:47.660 Sorry. 452 27:47.660 --> 27:50.960 Milk table and one table will be for combo meals. 453 27:50.990 --> 27:53.270 So what will happen in the combo meals? 454 27:53.360 --> 27:55.310 Someone has a taste. 455 27:55.340 --> 28:03.020 They want to eat muffins with the tea or someone will like your brother, want to eat muffins with the 456 28:03.020 --> 28:03.800 coffee? 457 28:03.920 --> 28:10.580 So over here, this cold drink will be joining with, uh, combined with all the meals option. 458 28:11.660 --> 28:15.920 So this is a perfect example of, we can say of cross join. 459 28:17.390 --> 28:19.790 This is a perfect example of cross join. 460 28:19.820 --> 28:20.390 Right. 461 28:20.930 --> 28:26.180 So in that particular scenario they don't want to drink any particular thing. 462 28:26.580 --> 28:27.570 they want to. 463 28:27.600 --> 28:30.120 Don't want to order any particular meals. 464 28:30.120 --> 28:39.570 Also, they want a combo item and they can take combo item from this particular detail or Excel file 465 28:39.570 --> 28:42.120 or any table, whatever it is there. 466 28:42.540 --> 28:45.780 So this one we can say one example of cross join. 467 28:51.870 --> 28:52.230 Okay. 468 28:52.230 --> 28:55.020 Now let's see the example of outer joins. 469 29:02.130 --> 29:07.320 So inner join will give only matching records but but cannot retain the unmatched record. 470 29:07.500 --> 29:11.160 To get unmatched records as well we need outer joins as well. 471 29:11.190 --> 29:12.450 Right left join. 472 29:13.020 --> 29:15.870 So what left join will do basically. 473 29:18.810 --> 29:27.170 Left join will return all the data from the left table as well as Non-matching data from the right table 474 29:27.170 --> 29:27.830 as well. 475 29:27.860 --> 29:31.190 So if you see over here this particular two tables. 476 29:34.070 --> 29:36.320 Let me show you again. 477 29:55.580 --> 29:56.360 Okay. 478 29:56.780 --> 30:00.350 So what will be the result of left join this time. 479 30:07.490 --> 30:10.310 This is my one table and this is my. 480 30:15.140 --> 30:15.980 Second table. 481 30:16.010 --> 30:16.400 Right. 482 30:16.400 --> 30:18.020 This is my second table. 483 30:18.050 --> 30:24.240 So in particular case of left, join all the records from the matching all the records from the left 484 30:24.240 --> 30:31.710 table will come as well as this, three and four will also come and. 485 30:34.530 --> 30:37.470 This seven, eight, eight will also come right. 486 30:37.470 --> 30:41.400 In this particular case, this seven and eight, three and four. 487 30:41.400 --> 30:43.740 And this seven, eight, eight will also come. 488 30:43.740 --> 30:46.050 So let me execute this query. 489 30:55.200 --> 30:58.170 See over here three and four is coming also. 490 30:58.410 --> 30:59.190 And seven. 491 31:01.530 --> 31:04.860 Seven eight, seven and eight is also coming right. 492 31:08.370 --> 31:10.530 So both the records are coming. 493 31:10.950 --> 31:15.660 And wherever it is not matched from the right table, it will become null over here automatically. 494 31:15.690 --> 31:16.260 Right. 495 31:16.290 --> 31:25.330 Because these two columns are we are retrieving from the right table quantity in order id quantity and 496 31:25.330 --> 31:27.520 order id we are retrieving from the right table. 497 31:27.550 --> 31:31.450 Right now, what is a left anti-join? 498 31:31.450 --> 31:34.330 I am not sure whether you have heard about it. 499 31:34.900 --> 31:36.610 Left anti-join or not. 500 31:36.610 --> 31:42.190 So in that particular case, ideally it should give me these two record. 501 31:45.880 --> 31:48.370 And these two record only. 502 31:51.910 --> 31:53.710 These two record and these two record only. 503 31:53.710 --> 31:54.940 These four records only. 504 31:54.940 --> 31:55.960 It will give. 505 31:59.110 --> 32:04.060 So how we can write it. 506 32:04.060 --> 32:13.960 We have to just mention this additional condition over here in terms of left anti-join where o dot o 507 32:13.990 --> 32:17.380 id is null where o dot o id is null. 508 32:17.380 --> 32:24.230 So if you see over here when ordered order id is null over here in this three and four. 509 32:24.260 --> 32:27.560 It is null and seven and eight. 510 32:27.590 --> 32:28.790 It is null, right? 511 32:29.330 --> 32:30.080 Three and four. 512 32:30.110 --> 32:30.560 It is null. 513 32:30.560 --> 32:31.430 And seven and eight. 514 32:31.460 --> 32:32.480 It is null. 515 32:32.510 --> 32:36.560 So this left join is coming in power BI as well. 516 32:38.480 --> 32:45.920 So this is the result of left anti join which I have shown you shown you over here right. 517 32:46.040 --> 32:50.060 And all record over here it will be null. 518 32:53.510 --> 32:58.130 And this column is we are retrieving from the 0.0 ID right. 519 33:02.090 --> 33:04.160 Now right outer join. 520 33:04.280 --> 33:05.210 It is. 521 33:12.230 --> 33:19.220 It is also like from wherever we are using the columns from the right table. 522 33:19.220 --> 33:22.090 It will be null only So all the columns from write. 523 33:22.090 --> 33:25.630 I will write it first, so you will identify. 524 33:30.070 --> 33:30.490 Okay. 525 33:30.490 --> 33:31.360 One minute. 526 33:39.760 --> 33:40.840 Okay I think. 527 33:42.370 --> 33:42.700 Sorry. 528 33:42.700 --> 33:44.440 That one is perfect only. 529 33:54.010 --> 33:57.310 Now why this? 530 33:57.340 --> 33:58.300 Why this three. 531 33:58.300 --> 34:02.860 And for some, some is not available. 532 34:02.860 --> 34:04.210 Why this? 533 34:04.210 --> 34:06.400 Three and four and this seven and eight. 534 34:06.430 --> 34:07.300 Why this summation? 535 34:07.300 --> 34:09.010 Summation is not available. 536 34:11.560 --> 34:14.080 Because this three. 537 34:19.820 --> 34:26.330 This three, three, and four are available in the left table only, but it is not available in the 538 34:26.330 --> 34:27.350 right table. 539 34:27.710 --> 34:30.290 Three and four is not available over here, right? 540 34:30.710 --> 34:33.170 That is why it is quantity. 541 34:33.200 --> 34:35.720 Value will be automatically nulled, right? 542 34:35.840 --> 34:37.670 Automatically null only. 543 34:37.700 --> 34:43.280 So that is why when we are doing the sum, it will become null over here only. 544 34:43.280 --> 34:45.710 And which is showing over here right. 545 34:45.920 --> 34:47.720 Which is showing over here. 546 34:48.710 --> 34:57.530 And what is right Anti-join where when we are retrieving this, this three four and seven eight at that 547 34:57.530 --> 34:57.980 time. 548 34:57.980 --> 34:58.370 Right. 549 34:58.370 --> 35:01.580 Anti-join will give us the null result, right? 550 35:03.710 --> 35:04.730 At that time, right. 551 35:04.760 --> 35:07.280 Anti-join will give the null result. 552 35:13.910 --> 35:16.220 Then this is the full outer join 553 35:24.630 --> 35:28.410 Actually this is the full outer anti join. 554 35:28.440 --> 35:30.360 Full outer anti join. 555 35:30.360 --> 35:35.820 But uh let me show you the full outer join. 556 35:51.600 --> 35:54.720 So this is the result of full outer join. 557 35:56.850 --> 35:59.850 Order id p id and quantity. 558 35:59.880 --> 36:01.620 Everything is null over here. 559 36:08.310 --> 36:10.470 Now let me know if you have any question. 560 36:10.500 --> 36:11.340 Till now 561 36:24.200 --> 36:25.310 Self-join is. 562 36:27.950 --> 36:30.020 Is this a double? 563 36:30.440 --> 36:31.190 Yes, yes. 564 36:32.210 --> 36:34.160 And 89 are SQL. 565 36:40.490 --> 36:40.790 Yeah. 566 36:40.790 --> 36:41.180 Okay. 567 36:44.960 --> 36:47.480 Now anyone has any question. 568 36:50.120 --> 36:52.280 We have to cover two more joins. 569 36:52.280 --> 36:59.900 Mainly left or a Self-join and non-Equity join. 570 37:01.940 --> 37:08.000 But both of them we will cover tomorrow because it may take some time to explain. 571 37:12.710 --> 37:18.780 Whenever we want to find a hierarchy, like if we want, if we have some table like this one. 572 37:19.650 --> 37:20.970 Suppose. 573 37:24.120 --> 37:26.250 Let me show you this table. 574 37:31.290 --> 37:33.480 So this is our table, right. 575 37:33.510 --> 37:38.610 Now over here I have employee number is their manager number is their. 576 37:38.790 --> 37:41.820 And this all columns are available with me right. 577 37:42.060 --> 37:44.430 But now suppose I want manager name. 578 37:44.430 --> 37:47.820 Also I want manager name also. 579 37:47.940 --> 37:49.800 Then how can I get it. 580 37:51.360 --> 37:55.920 So for that purpose we can have the concept of self join. 581 37:55.980 --> 38:03.360 We can join this particular column with this particular column because manager will be at the end. 582 38:03.360 --> 38:05.430 It will be a employee only right. 583 38:05.910 --> 38:07.680 Manager will be at the end. 584 38:07.680 --> 38:09.210 It is a employee only. 585 38:12.840 --> 38:15.880 So that is why Self-join will be in picture. 586 38:15.880 --> 38:22.240 I need to explain it in more detail so it will maybe take some 1015 more minutes. 587 38:22.870 --> 38:24.730 And again, no equijoin. 588 38:24.760 --> 38:25.210 Yeah. 589 38:26.050 --> 38:26.530 I'm sorry. 590 38:26.830 --> 38:28.150 Sorry to break you in the middle of it. 591 38:28.660 --> 38:29.530 Probably. 592 38:30.400 --> 38:34.240 So one minute this image is opening. 593 38:37.030 --> 38:37.660 See? 594 38:37.660 --> 38:38.230 Over here. 595 38:38.260 --> 38:40.150 Are you able to see this image? 596 38:40.600 --> 38:41.500 Yes. 597 38:43.360 --> 38:48.460 So basically, this image is giving me the, uh, giving us the perfect example. 598 38:48.460 --> 38:53.590 So if you see over here first Venn diagram A and B, so what will be the left. 599 38:53.590 --> 38:56.230 Join all the over here. 600 38:56.230 --> 39:02.320 This middle middle thing will be the inner join only if you see this middle figure center figure. 601 39:02.950 --> 39:08.800 All all the things from A will be covered and all the things from B will be covered that is highlighted 602 39:08.800 --> 39:11.230 in yellow in this center figure. 603 39:11.230 --> 39:13.760 So that is why it is written inner join. 604 39:16.310 --> 39:18.740 This will be my center figure. 605 39:18.740 --> 39:20.090 Will be my inner join. 606 39:20.120 --> 39:21.980 Then top left figure. 607 39:22.100 --> 39:23.810 So top left, left figure. 608 39:23.840 --> 39:31.130 All the matching matching one will be highlighted as well as enter A is also highlighted in top left 609 39:31.130 --> 39:31.760 side. 610 39:31.970 --> 39:33.920 So that is why it is written over here. 611 39:33.920 --> 39:38.750 Also left join now right join will be the reverse case. 612 39:39.230 --> 39:48.590 Again, this middle middle thing will be also highlighted and everything from the B is also highlighted. 613 39:48.590 --> 39:51.650 So that is why right join is written over here. 614 39:52.220 --> 39:53.480 So this will be my right. 615 39:53.480 --> 39:57.110 Join this a a thing will be not highlighted because why. 616 39:57.140 --> 39:59.510 Because it is not matching right. 617 39:59.510 --> 40:01.580 It will be not matching with B. 618 40:02.870 --> 40:12.940 So this is my right join then obviously this bottom left bottom left will be entire thing. 619 40:12.970 --> 40:14.620 Everything will be highlighted. 620 40:14.620 --> 40:18.220 So that is why it will be my full outer join. 621 40:19.930 --> 40:21.970 It will be my full outer join. 622 40:24.220 --> 40:27.400 And this one left anti left anti join. 623 40:27.400 --> 40:27.880 Why? 624 40:28.000 --> 40:35.950 Because from the left table whatever whatever is matching we have again this highlighted in this second 625 40:36.130 --> 40:38.410 uh left figure. 626 40:38.440 --> 40:39.520 Second left figure. 627 40:39.520 --> 40:40.960 So that is why left. 628 40:40.960 --> 40:43.240 And also one condition is added. 629 40:43.270 --> 40:44.230 Key is null. 630 40:44.230 --> 40:48.580 So I have also write this condition right in the query. 631 40:48.580 --> 40:49.660 So this is left empty. 632 40:49.690 --> 40:50.650 This is right empty. 633 40:50.650 --> 40:53.620 And this is full outer anti join. 634 40:56.230 --> 41:01.660 So I think if it is fine uh this is fine. 635 41:01.660 --> 41:06.190 But uh you know if you have uh maybe uh maybe I'm not. 636 41:06.220 --> 41:11.860 Maybe some what I feel is if you have the, uh data.