1 00:00.480 --> 00:01.560 Hello everyone. 2 00:02.120 --> 00:03.120 Good morning all. 3 00:04.160 --> 00:07.520 So let's have another SQL interview question. 4 00:08.320 --> 00:11.440 What is the difference between union and union all. 5 00:13.080 --> 00:20.680 So union combines the results from multiple tables and return the distinct records into a single result 6 00:20.680 --> 00:21.160 set. 7 00:22.680 --> 00:24.560 So this is the definition of union. 8 00:24.560 --> 00:27.400 We will see the example very soon. 9 00:28.160 --> 00:33.760 Now union regarding union all combines the results from the multiple table. 10 00:33.760 --> 00:37.600 In return all the records into a single result set. 11 00:40.360 --> 00:46.920 So union will return the distinct record into a single result set and union all will return the all 12 00:46.920 --> 00:49.280 the records into a single result set. 13 00:51.640 --> 00:57.480 Now regarding Union, it has a default feature to eliminate duplicate rows from the table. 14 00:58.240 --> 00:58.800 So if. 15 00:59.000 --> 01:06.650 If the two result set having a duplicate records into both, then Union will return the only the distinct 16 01:07.130 --> 01:10.450 records into the result set, whereas union all. 17 01:10.450 --> 01:13.370 It can't eliminate the duplicate rows from the table. 18 01:14.370 --> 01:18.450 Regarding performance wise, union is bit slow. 19 01:18.490 --> 01:25.970 Slow performance at it removes the duplicate rows from the output, whereas union all is fast. 20 01:25.970 --> 01:33.370 Performance is compared to union and below is the syntax for the union and union all. 21 01:34.210 --> 01:38.130 Now we will see the example of union and union all. 22 01:39.250 --> 01:43.010 So here I have two different tables. 23 01:45.130 --> 01:47.130 One table is sales dot. 24 01:47.690 --> 01:51.050 So let me select the data. 25 01:51.090 --> 01:52.170 Select star from. 26 01:55.090 --> 01:57.290 Sales dot sales order details. 27 01:57.930 --> 02:06.620 So if you see over here in the sales order detail, we have sales order ID column is there and sales 28 02:06.620 --> 02:09.980 order ID we have three distinct values 29 02:11.260 --> 02:18.340 (436) 594-3660 and 43661. 30 02:18.700 --> 02:19.220 Right. 31 02:19.660 --> 02:21.540 And in the second table. 32 02:24.260 --> 02:29.180 If you see over here let me select data select star from. 33 02:33.460 --> 02:39.500 In the second table we have result of (436) 594-3660. 34 02:40.060 --> 02:45.900 So here two distinct sales order ID are there in above one three distinct sales order ID are there. 35 02:46.220 --> 02:50.140 Now if we do the union between these two table. 36 02:50.140 --> 02:53.980 So I have taken the column from both the table over here. 37 02:53.980 --> 02:59.830 So if you select the data from the first table it will give 29 rows. 38 02:59.870 --> 03:07.430 And if if you select the data from the below table, then it will give 14 rows. 39 03:07.710 --> 03:13.630 Now if you do the union between both the tables then it will give 29 rows. 40 03:13.670 --> 03:21.110 So this below 14 rows will be duplicated in above table and below table as well. 41 03:21.670 --> 03:27.150 So it will uniquely retrieve data from both the queries. 42 03:27.670 --> 03:33.270 Now if you select the both the queries through Union all, then. 43 03:36.030 --> 03:40.870 It will give 43 rows because both 29 plus 14. 44 03:40.910 --> 03:44.630 We need to see so 29 plus 14. 45 03:44.950 --> 03:47.030 So it will be 43 right. 46 03:47.510 --> 03:50.710 So this 14 row will not be eliminated. 47 03:50.710 --> 03:59.890 And it is giving again in the result set because it is a union All keyword we have used, right? 48 03:59.930 --> 04:00.610 Union all. 49 04:00.650 --> 04:01.130 Operator. 50 04:01.130 --> 04:02.010 We can say that. 51 04:03.570 --> 04:05.170 So these are union. 52 04:05.170 --> 04:08.570 And union all comes under the category of set operators. 53 04:09.010 --> 04:14.170 And now there are some basic rules when we are performing the union and union all. 54 04:15.090 --> 04:22.890 So these are the rules you can say as a answer into the interview. 55 04:22.890 --> 04:28.370 So both the select statement must have the same number of columns in same data type. 56 04:28.770 --> 04:35.490 So if you see over here in the example both the Select statement has same number of columns are there 57 04:36.170 --> 04:39.450 in because of same number of columns. 58 04:39.890 --> 04:42.130 Definitely data type will be the same right? 59 04:44.890 --> 04:49.330 But if the data type is different, suppose sales order id is integer data type. 60 04:49.770 --> 04:54.090 In this first table and sales order id as varchar data type in the second table. 61 04:54.530 --> 04:58.180 Then it may throw the error because data type also should be the same. 62 04:58.220 --> 05:03.580 Right now, columns in both the select statement must be in the same order. 63 05:04.180 --> 05:10.260 So this if you see over here sales order ID sales order detail ID career track tracking number. 64 05:10.300 --> 05:13.780 These columns are in the same order in both the select statements. 65 05:14.620 --> 05:21.300 If these columns will come over here in the first one, then this column will come in the second one. 66 05:21.300 --> 05:23.540 Then it will cause an error. 67 05:23.540 --> 05:25.940 Right in. 68 05:25.940 --> 05:32.180 The names of the columns can be different, but the final result set will show the column name of the 69 05:32.180 --> 05:34.660 first select query. 70 05:34.700 --> 05:40.460 So if you see over here if I select sales over here as. 71 05:42.500 --> 05:46.580 Sales order just like sales order. 72 05:46.580 --> 05:51.340 But still it will give sales order ID only because. 73 05:52.980 --> 06:00.230 But now if I Why select as sales, then it will give sales. 74 06:00.670 --> 06:01.270 Because. 75 06:05.070 --> 06:06.790 So if you can see over here right. 76 06:06.830 --> 06:10.910 It is given sales because the names of the columns can be different. 77 06:11.230 --> 06:14.590 So names are different in both the both the select statement. 78 06:15.950 --> 06:21.470 But the final result set will show the column name of the first select query. 79 06:21.470 --> 06:24.030 So first select query column name. 80 06:24.030 --> 06:25.190 It is showing right. 81 06:27.190 --> 06:30.110 So this is the difference between union and union. 82 06:30.110 --> 06:33.350 All this is very common interview questions for. 83 06:35.910 --> 06:41.310 Interview in SQL or data analyst or data engineer related interviews. 84 06:41.950 --> 06:43.590 So thank you so much everyone. 85 06:44.270 --> 06:51.230 If you like this video then please do press the like button and then comment and subscribe and share 86 06:51.230 --> 06:52.350 as much as possible. 87 06:52.350 --> 06:53.110 Thank you.