1 00:00.320 --> 00:02.360 Let's see another SQL problem. 2 00:02.800 --> 00:05.120 Finding matched and mismatched records. 3 00:05.600 --> 00:07.600 So below is the problem statement. 4 00:08.320 --> 00:12.240 Given two tables a source and a target with the same structure. 5 00:12.640 --> 00:16.240 Write a query to identify and classify all records. 6 00:17.400 --> 00:22.520 The output should clearly state whether a record is matched when it is matched. 7 00:23.120 --> 00:25.800 Exists in both tables with the same data. 8 00:26.360 --> 00:27.440 New in source. 9 00:27.480 --> 00:30.840 Exists in the source table, but not in the target table. 10 00:31.240 --> 00:36.720 Mismatch ID is available in both the table, but name is different in. 11 00:36.760 --> 00:41.880 Missing in source it means exist in the target table, but not in the source table. 12 00:42.360 --> 00:44.240 So we are having the two different tables. 13 00:44.240 --> 00:46.280 Source and target tables are there. 14 00:46.680 --> 00:46.920 In. 15 00:46.920 --> 00:48.040 Below is the data. 16 00:50.600 --> 00:54.240 Now let me ask the ChatGPT about the same. 17 00:54.720 --> 00:57.520 So let's see. 18 01:02.200 --> 01:09.050 I, I am having two tables and data as per. 19 01:11.970 --> 01:12.650 Below. 20 01:15.370 --> 01:17.170 Okay, now it will ask. 21 01:17.170 --> 01:19.930 Please go ahead and share your two tables. 22 01:19.930 --> 01:20.450 Right. 23 01:20.730 --> 01:24.330 So now I am sharing my tables and data with ChatGPT. 24 01:25.530 --> 01:28.250 And now let's see. 25 01:31.890 --> 01:36.050 Now let me share my problem statement with ChatGPT as well. 26 01:37.730 --> 01:40.210 So my problem statement is look like this. 27 01:40.210 --> 01:42.530 Let me share with ChatGPT. 28 01:45.890 --> 01:54.530 Now let's see whether it is providing us the solution as per our requirement or not. 29 01:57.890 --> 02:02.010 So see uh, this is the goal has been defined, right? 30 02:04.890 --> 02:08.590 Match exists in both with same name. 31 02:09.430 --> 02:11.750 New in source exists in source only. 32 02:12.270 --> 02:17.030 Mismatch same ID but name differs in missing. 33 02:17.030 --> 02:20.270 Source exists in target only right now. 34 02:20.310 --> 02:23.590 SQL server solution it has provided, right. 35 02:23.630 --> 02:28.030 Now let me copy the same thing and let's see. 36 02:31.710 --> 02:31.990 Okay. 37 02:32.030 --> 02:34.990 It has provided the solution like this. 38 02:36.750 --> 02:37.430 Okay. 39 02:38.310 --> 02:45.550 Now let me write solution provided by ChatGPT. 40 02:46.470 --> 02:47.950 Now see what it is doing. 41 02:48.230 --> 02:50.790 It is comparing the ID right. 42 02:51.150 --> 02:52.710 Comparing the id. 43 02:52.750 --> 02:58.110 If it is null then it is provide the target table ID right? 44 02:58.630 --> 02:59.550 That is fine. 45 03:01.670 --> 03:03.630 Same thing for the name as well. 46 03:03.870 --> 03:08.910 If the source name is null then it will provide the target table name right? 47 03:09.830 --> 03:11.030 That is also fine. 48 03:11.400 --> 03:13.080 Now what it is doing over here? 49 03:13.400 --> 03:20.120 When s.id is not null, it means n t.id is not null, right? 50 03:20.160 --> 03:25.400 It means the data is available in the source table as well as in the target table. 51 03:25.880 --> 03:28.120 And the name is also the same. 52 03:29.040 --> 03:31.320 And the name is also the same, right? 53 03:31.920 --> 03:33.960 Then it is a match record, right? 54 03:34.680 --> 03:38.680 Now see, it is doing a full outer join over here. 55 03:38.680 --> 03:39.680 That is perfect. 56 03:40.160 --> 03:42.400 And that is also with the id id column. 57 03:42.400 --> 03:42.840 Right. 58 03:43.360 --> 03:44.720 That we want only. 59 03:45.280 --> 03:45.720 Right. 60 03:46.280 --> 03:55.920 Now for the second thing, what it is doing when acid is not null and t.id is null, right? 61 03:56.920 --> 03:58.320 Then what? 62 03:58.360 --> 03:59.400 What will happen? 63 03:59.600 --> 04:01.920 Acid is not null, it means. 64 04:05.040 --> 04:08.360 The data is available in the source and t.id is null. 65 04:08.360 --> 04:11.240 It means data is not available in the target. 66 04:11.240 --> 04:12.640 It means new in source. 67 04:12.640 --> 04:13.080 Right. 68 04:13.440 --> 04:18.410 And what we have done over here for new installs when t.id is null. 69 04:18.450 --> 04:18.850 Right. 70 04:18.890 --> 04:22.330 The same thing we are doing over here then new installs. 71 04:22.890 --> 04:26.170 So our this is our solution. 72 04:26.170 --> 04:29.770 And below is the solution provided by ChatGPT. 73 04:32.050 --> 04:39.890 Now when acid is not null and t.id is not null, it means data is present in both the source table as 74 04:39.890 --> 04:41.410 well as in the target table. 75 04:41.810 --> 04:44.170 But name is different at that time. 76 04:44.170 --> 04:45.530 What will happen at that time? 77 04:45.530 --> 04:47.690 It will be mismatched, right? 78 04:47.730 --> 04:49.450 At that time it will be mismatched. 79 04:50.130 --> 04:59.130 And when the acid is null and t.id is not null, it means in the source data is not available, right? 80 04:59.770 --> 05:01.890 But in the target data is available. 81 05:01.890 --> 05:04.610 So we can say missing in source. 82 05:04.650 --> 05:06.330 As simple as that right. 83 05:07.290 --> 05:12.050 You can compare our solution and ChatGPT solution. 84 05:12.290 --> 05:12.770 Right. 85 05:13.290 --> 05:19.180 Both the solution you can compare and and what is the different Is there in both. 86 05:19.180 --> 05:19.860 The solution? 87 05:19.860 --> 05:21.300 You can check that one. 88 05:21.300 --> 05:24.460 We have not used this Coalesce function right. 89 05:24.460 --> 05:27.300 So let me execute both of this query and let's see. 90 05:31.060 --> 05:31.420 Okay. 91 05:31.460 --> 05:35.260 In our query we are having id name id name. 92 05:35.580 --> 05:43.740 We are having two different columns for for two different ID column and two different name column both 93 05:43.740 --> 05:46.220 from Source and Target respectively. 94 05:46.860 --> 05:49.540 But they have merged merge the column right. 95 05:49.700 --> 05:50.980 They have merged column. 96 05:51.620 --> 05:54.380 So they have given us more optimum solution. 97 05:54.380 --> 05:56.460 We can say right. 98 06:00.420 --> 06:03.260 And our final column will be the same in both. 99 06:03.260 --> 06:03.780 Right. 100 06:06.740 --> 06:07.740 So perfect. 101 06:08.060 --> 06:11.460 Now let's see what else ChatGPT has done. 102 06:11.820 --> 06:14.500 It will give you the proper explanation as well. 103 06:14.540 --> 06:15.020 Right? 104 06:16.300 --> 06:18.860 So why they have used the full outer join? 105 06:18.900 --> 06:24.680 Ensure we see old records from both the tables right now coalesce function. 106 06:24.680 --> 06:31.880 Why we have they have used the coalesce function picks whichever value is not null source or target. 107 06:33.920 --> 06:34.480 Right. 108 06:34.480 --> 06:38.800 So suppose source value is not null then source value it will provide. 109 06:39.160 --> 06:43.200 But if the source value is null then it will provide the target value right. 110 06:44.120 --> 06:47.120 In of course case statement we have to use right. 111 06:47.560 --> 06:50.320 So match both sides have same ID and name. 112 06:50.800 --> 06:54.080 New in source means id exists only in source. 113 06:54.720 --> 06:57.120 Mismatch means same ID but different name. 114 06:57.120 --> 07:01.760 Right and missing source means ID exists only in target table. 115 07:02.360 --> 07:10.440 And they have also told that this query works perfectly in SQL server, PostgreSQL, SQL, and BigQuery 116 07:11.000 --> 07:12.680 with little or no change. 117 07:13.760 --> 07:14.280 Right? 118 07:14.960 --> 07:18.280 So how good the ChatGPT is, we can say, right? 119 07:19.920 --> 07:24.120 So let's see the next question into the next lecture. 120 07:24.120 --> 07:24.760 Thank you.