Difference between except and not in sql server — Transcript

Full transcript

1. 0:00this is part 88 of SQL Server tutorial
2. 0:03in this video we'll discuss the
3. 0:04difference between accept and not in
4. 0:07operators in SQL Server this is
5. 0:09continuation to part 87 so please watch
6. 0:12part 87 before proceeding in the
7. 0:15previous video session we discussed that
8. 0:17the accept operator returns all the rows
9. 0:20from the left query that are not there
10. 0:22in the right query's results n an
11. 0:24operator also does exactly the same
12. 0:27thing let's understand this with an
13. 0:29example here we've got two tables table
14. 0:31a and table B notice both the tables
15. 0:34have got the same number and type of
16. 0:37columns if you look at the data the
17. 0:39employee Rows 2 and three are present in
18. 0:42both the tables now what we want to do
19. 0:44is write a query that's going to return
20. 0:46us only the rows that are present in
21. 0:48table a but not in table B to achieve
22. 0:51this we can either use the accept
23. 0:53operator or not in operator let's look
24. 0:56at that in action so when we execute
25. 0:58this select query we're going to to get
26. 1:00all the rows and columns from table a
27. 1:02now let's use the accept operator and
28. 1:06then select ID name gender from table B
29. 1:10so if we execute this query we should
30. 1:12get only the rows that are present in
31. 1:14table a but not in table B so notice we
32. 1:17just get that one row which is present
33. 1:19only in table a but not in table B now
34. 1:23we can achieve this exact same thing
35. 1:25using not in operator as well let's see
36. 1:28how to do it using the not an operator
37. 1:31so we want to select ID name gender from
38. 1:34table a where
39. 1:38ID not
40. 1:41in
41. 1:44select ID
42. 1:47from table B so let's execute these two
43. 1:51queries together notice we get exactly
44. 1:54the same result for both the queries so
45. 1:57the obvious next question that comes to
46. 1:58our mind is well then what's the
47. 2:00difference between accept and not an
48. 2:03operators there are two key
49. 2:06differences except operator filters
50. 2:09duplicates and returns only distinct
51. 2:11rows from the left query that are not
52. 2:13there in the right queries results
53. 2:15whereas noton does not filter the
54. 2:18duplicates let's understand this
55. 2:19difference with an example at the moment
56. 2:22if you look at the data that we have got
57. 2:23in table a we don't have any duplicate
58. 2:26rows now what I'm going to do is insert
59. 2:28another record for Mark so we will have
60. 2:31the same ID name and gender so let's
61. 2:33execute this insert query right here now
62. 2:37if we select what we have in table a
63. 2:39notice that Mark record is repeated
64. 2:42twice now when we execute this accept
65. 2:46query notice we get only one row for
66. 2:49Mark that means the duplicates are
67. 2:52filtered whereas when we execute this
68. 2:54not in
69. 2:55query notice that we get the duplicates
70. 2:59so the primary difference is that accept
71. 3:02filters the duplicates whereas not will
72. 3:05not filter the duplicates another
73. 3:07difference is that accept operator
74. 3:10expects the same number of columns in
75. 3:12both the queries whereas not in Compares
76. 3:15a single column from the outer query
77. 3:17with a single column from the subquery
78. 3:19so if you look at the accept operator
79. 3:21here notice we have got two select
80. 3:23queries you know on the left side of the
81. 3:26accept operator and on the right side of
82. 3:28the accept operator and if you look at
83. 3:30the number of columns they are exactly
84. 3:32the same now what's going to happen if
85. 3:34the number changes so now one query has
86. 3:37got two columns and the other query has
87. 3:39got three columns when we execute that
88. 3:41we get an error saying all queries
89. 3:43combined using a union intersector
90. 3:45except operator must have an equal
91. 3:48number of expressions in the Target list
92. 3:51whereas if you look at the not in
93. 3:53operator it Compares a single column
94. 3:56from the outer query with a single
95. 3:58column from the subquery so if you look
96. 4:00at this not in query here so we are
97. 4:03comparing ID with ID so there's only one
98. 4:07column in the outer query that we are
99. 4:09comparing with one column in the
100. 4:11subquery now what do you think is going
101. 4:13to happen if this subquery is going to
102. 4:15return maybe two columns so when we
103. 4:18execute this again we are going to get
104. 4:20an error and if you look at the error it
105. 4:22says only one expression can be
106. 4:24specified in the select list when the
107. 4:27subquery is not introduced with exess so
108. 4:30those are the key differences between
109. 4:33accept and not in operators in SQL
110. 4:36Server thank you for listening and have
111. 4:38a great day