SQL Server except operator — Transcript

Full transcript

1. 0:00This is part 87 of SQL Server tutorial.
2. 0:03In this video, we'll discuss the SQL
3. 0:05Server accept operator with a few
4. 0:07examples. So, what does this accept
5. 0:09operator do? This operator returns
6. 0:11unique rows from the left query that are
7. 0:14not there in the right queries results.
8. 0:16Let's understand this with an example.
9. 0:18We'll be using these two tables, table A
10. 0:20and table B. In this demo, I've already
11. 0:22created these tables and here is the SQL
12. 0:24script to create them and populate with
13. 0:26test data. I'll have the script
14. 0:28available on my blog in case you need
15. 0:29it. Now let's write a query that's going
16. 0:31to involve both these tables and the
17. 0:34except operator. So what we want to do
18. 0:37is select ID, name, gender from table A.
19. 0:40So when we execute this query, as
20. 0:42expected, we are going to get all the
21. 0:43five rows that are present in this
22. 0:45table, table A. Now along with that
23. 0:48query, I also want to use the accept
24. 0:50operator and we want to select ID name
25. 0:54gender from table B. Now when we execute
26. 0:57this query, what is the result that we
27. 0:59expect? Now what is this accept operator
28. 1:01going to do? It's going to return us the
29. 1:03rows from the left query that are not
30. 1:05there in the right queries results. And
31. 1:07if you look at these two tables, these
32. 1:09records that is employee John and Sarah,
33. 1:12those two records are present in both
34. 1:14the tables. So when we execute this
35. 1:16except query, it should only return us
36. 1:19the first three rows. So let's look at
37. 1:22that in action. Notice that we get the
38. 1:24rows from 1 2 3. So that's what the
39. 1:27except operator is going to do. This
40. 1:29operator is introduced in SQL Server
41. 1:322005. The most important thing to keep
42. 1:34in mind is that for the except operator
43. 1:37to work, the number and the order of the
44. 1:39columns must be the same in both the
45. 1:41queries and the data types must also be
46. 1:44either same or compatible. Now what's
47. 1:46going to happen if the number and the
48. 1:48order of the columns is not same? For
49. 1:50example, let's remove this gender column
50. 1:52here. So in this query, we only have two
51. 1:55columns. Whereas in this query, we have
52. 1:56got three columns. And if I execute
53. 1:59this, notice that we get an error.
54. 2:02Similarly,
55. 2:04you know, in this case, the number is
56. 2:06the same,
57. 2:08but
58. 2:10the order is different.
59. 2:14Again, when we execute this, we are
60. 2:16going to get the same error. Okay? So
61. 2:18that's very important. And the number
62. 2:20and the order of the columns and the
63. 2:22data types must be either same or at
64. 2:24least compatible. And if you are from
65. 2:27Oracle background, we can relate this
66. 2:30except operator to minus operator in
67. 2:32Oracle.
68. 2:35Now in this example,
69. 2:38we have you know returned the results
70. 2:41that are present in table A but not in
71. 2:44table B. Now what if you want the other
72. 2:46way around? I want the rows that are
73. 2:48present in table B but not in table A.
74. 2:50If that's the case, you can reverse
75. 2:52these queries. So, select ID, name,
76. 2:54gender from table B except ID, name,
77. 2:57gender from table A. So, when we execute
78. 3:00this, we only get the rows that are
79. 3:01present in table B but not in table A.
80. 3:04So, we only get here in this case 6 7 8
81. 3:07which are present in table B but not in
82. 3:09table A. Now, here we have seen how to
83. 3:12use the except operator on two different
84. 3:14tables. Is it possible to use it on a
85. 3:16single table? Absolutely, you can use it
86. 3:19on a single table and you can even have
87. 3:22wear clause. So for the single table
88. 3:24example, we'll use this table tbl
89. 3:26employees. Again, I've already created
90. 3:28that and populated it with test data.
91. 3:30And here is the SQL script to do that.
92. 3:32So what we want to do is select name, id
93. 3:35name, gender, and
94. 3:38salary from table tbl employees.
95. 3:44So when we execute this query, it's
96. 3:46going to give us all the employee rows.
97. 3:49Right? Now what I want to do is apply a
98. 3:51wear clause where salary is greater than
99. 3:54or equal to 50,000. So what is this
100. 3:57going to do? It's going to give us all
101. 3:59the employees who has a salary greater
102. 4:01than or equal to 50,000. Now I'm going
103. 4:05to use except operator and I'm actually
104. 4:08going to make a copy of this select
105. 4:10query and then I'm going to change the
106. 4:12wear clause here. So I'm going to say
107. 4:15where salary is greater than or equal to
108. 4:1860,000. Now when we execute this we
109. 4:22should only get those employees whose
110. 4:24salary is between 50,000 and 60,000.
111. 4:29So we are using it on the same table.
112. 4:32accept operator.
113. 4:35Now we can also use order by class when
114. 4:37we use the accept operator but you have
115. 4:40to use it after the right query that is
116. 4:42after the second query. You can't use
117. 4:45order by right here. Let's say for
118. 4:47example we want to order by ID in
119. 4:49descending order. If I say order by ID
120. 4:52descending here we will get an error.
121. 4:56You have to use it after the second
122. 4:59query that is the right query. So now
123. 5:01when we execute that we get the result
124. 5:03and notice that the result is sorted by
125. 5:05ID in descending order.
126. 5:10Thank you for listening and have a great
127. 5:12day.