Intersect operator in sql server — Transcript

Full transcript

1. 0:00this is part 89 of SQL Server tutorial
2. 0:03in this video we'll discuss the
3. 0:05intersect operator in SQL server and the
4. 0:07difference between intersect and in a
5. 0:10join so what does this intersect
6. 0:12operator do this operator retrieves the
7. 0:15common records from both the left and
8. 0:17the right query of the intersect
9. 0:19operator let's understand this with an
10. 0:21example here we've got two tables table
11. 0:24a and table B notice both the tables
12. 0:26have got the same number and type of
13. 0:28columns if you look at the data employee
14. 0:31records 2 and three are present in both
15. 0:34the tables now what we want to do is
16. 0:36write a query that's going to retrieve
17. 0:38the common rules that are present in
18. 0:40both the tables we can achieve this two
19. 0:42ways we can achieve this either by using
20. 0:45the intersect operator or by using inner
21. 0:48join let's look at both the ways let's
22. 0:50flip to SQL of management studio so this
23. 0:53query is going to retrieve all the rows
24. 0:55from table a now I'm going to use
25. 0:58intersect operator and retrieve ID name
26. 1:01gender from table B so if we execute
27. 1:04this intersect query we should get all
28. 1:07the rules that are common between both
29. 1:08tables now if you look at these two
30. 1:10tables the common rows are with IDs 2
31. 1:13and three so we get those in our result
32. 1:15set right here now let's see how to
33. 1:18achieve exactly the same thing using in
34. 1:20a join so what we want to do now is
35. 1:23Select ID name gender from table a in a
36. 1:26join with table B and we have to to
37. 1:30specify the join condition and the
38. 1:31common column between both the tables is
39. 1:33ID so on table a. ID equals table b. ID
40. 1:40now if we execute this in a join query
41. 1:42we are going to get an error that's
42. 1:44because in both the tables we've got the
43. 1:46same column names ID name gender so
44. 1:49that's why we get this ambiguous column
45. 1:51name error three times so we will have
46. 1:53to specify the name of the table from
47. 1:55which we want these columns we can
48. 1:57either use table a or table B
49. 2:00but I'm going to use table a for this
50. 2:02example so table a. ID table
51. 2:06a.name and table a. agenda now let's
52. 2:10execute these two queries together
53. 2:11notice we get exactly the same result
54. 2:15now the obvious question that comes to
55. 2:17our mind at this point is then what's
56. 2:18the difference between in a join and
57. 2:21intersect operators we'll discuss those
58. 2:24differences in just a bit but before
59. 2:26that let's discuss a few more points
60. 2:28about this intersect operator this
61. 2:30operator is introduced in SQL Server
62. 2:322005 just like the except operator for
63. 2:36intersect operator to work the number
64. 2:38and the Order of the columns must be the
65. 2:41same in both the queries the data types
66. 2:43must be same or at least
67. 2:45compatible all right now let's look at
68. 2:47the differences between intersect and in
69. 2:50a join intersect filters duplicates and
70. 2:53returns only distinct rows that are
71. 2:55common between the left and right query
72. 2:57whereas in a join does not filter the
73. 2:59duplicates let's understand this
74. 3:01difference with an example now I'm going
75. 3:04to insert This Record into table a so
76. 3:08the record is going to have an ID value
77. 3:09of two name Mary gender female now if we
78. 3:13retrieve data from table a we should
79. 3:18have Mary's record repeated twice notice
80. 3:21ID name and gender are the same for
81. 3:24record 2 and record 4 okay so now let's
82. 3:28execute our intersect query notice that
83. 3:32we only get the unique rows the
84. 3:35duplicates are filtered out we only get
85. 3:37once this Mery record but if you look at
86. 3:40inner join we are going to get Mery
87. 3:43record twice so that's one of the
88. 3:45primary differences between intersect
89. 3:47and inner join to make this inner join
90. 3:50behave like the intersect operator use
91. 3:52the distinct operator so now notice if
92. 3:55we use distinct operator this is only
93. 3:58going to retrieve the distinct row so in
94. 4:00this case the inner join is going to
95. 4:03behave like intersect operator notice we
96. 4:06get exactly the same result now the
97. 4:10other difference is inner joint treats
98. 4:12two nulls as two different values so if
99. 4:14you're joining two tables based on a
100. 4:16nullable column and if both tables have
101. 4:19nulls in that joining column then inner
102. 4:21join will not include those rows in the
103. 4:23result set whereas intersect treats two
104. 4:26nulls as a same value and it returns all
105. 4:29matching rows let's look at this in
106. 4:31action so to understand this difference
107. 4:34I'm going to insert a row with null
108. 4:37value for ID column into table a and
109. 4:39similarly into table b as well so let's
110. 4:42execute these
111. 4:43two insert queries and let's select the
112. 4:47data that we have at the moment in table
113. 4:49a and table B so there is one null
114. 4:54column you know for one of the rows and
115. 4:57if you look at name and gender columns
116. 4:58the data is same okay now let's see
117. 5:01what's going to happen when we execute
118. 5:03the intersect query so when we execute
119. 5:06the intersect query notice that we get
120. 5:09that null row okay whereas when we
121. 5:12execute the inner join we will not get
122. 5:16that null row okay so that's another
123. 5:19difference between inner join and
124. 5:21intersect
125. 5:24queries thank you for listening and have
126. 5:26a great day