Finally someone who simplify an explanation. I found that most IT guys try to show their flex more than teaching others and end up losing the listener completely. Thanks for being straight to the point and not over complicate things!
I just found your channel now, too bad you don't upload new videos any more. You explained these queries very well. I'll definitely watch more of your videos.
Just a question: what would we do if we need to the union all for two tables and then save it as a new table. For example 1 union 2 makes a new table 3. what would be the command for it.
very clear tutorials thanks so much. would have enjoyed if you could give some practice q's to help us reinforce learning ! this is much clearer than another tutorial course i paid for (i came on here was was confused and your vid made it very easy to understand.thanks so much
Great job! Would be good if you elaborate a bit more on the logic behind the results at some points like "the except all here would bring back all the countries that are within customers, as many times as it appears there (the duplicates) and as long as it does not appear in the suppliers table" - something like that... When you say "countries that aren't in both tables", it is vague - try to be specific. great job tho
A simple example for those who are looking to understand the difference between except and except all: create table x (V1 numeric); create table y (V1 numeric); insert into x values (1),(2),(2),(2),(3),(4),(4); insert into y values (2),(3),(4),(4),(4),(5); EXCEPT [ALL] matches on all columns from both tables, column types and order must match. select * from x except select * from y; | v1 ---- | 1 select * from x except all select * from y; | v1 ---- | 1 | 2 | 2 In EXCEPT sql process distinct datasets, so any duplicates are automatically removed leaving only single copy of each row. This results to exclude a row based on match just one in row in second dataset. On the other hand EXCEPT ALL process datasets taking duplicate rows quantities into account. This results to return the exact difference in duplicate rows between tables. Thanks, @omar for pointing out the issue in the video and I'm trying to help other people who want to get more clarity on understanding the concept.
I have a large table. I want to retrieve ALL the records that have duplicates in the name field. So if John Smith appears once in the table then I want to exclude him completely from the result. I am not interested in anyone who only showed up one time. But if he appears more than once (let's say 7 times) in the table then I want ALL 7 of his records shown. And the same for Mike Jones and Amanda Brown and everyone else. I am only interested in those who appear more than once, and then, I want to see ALL the records they appear in, since the different fields have additional info about those people. Can you show me how to write such a query? Thanks!
"Except" returns only unique countries that present in the left set, but not in the right one. And you said "There is a list of the countries that aren't in both tables." I don't think that statement is correct because we have 21 countries in "Customers" and 16 countries in "suppliers" and the intersection is 12. That means 9 unique counties in "customers" and "4" countries in "suppliers". So your statement says "that aren't in both tables" which means 9 + 4 = 13 countries. They are not in both tables, that's correct. But you received 9 in data output which means that we got just unique countries from the left set "customers", but not in the right one "suppliers". That's why in my opinion you can't claim that there is a list of the countries that aren't in both tables. Maybe i am wrong because I am not native speaker and I misunderstood something... Best regards to this channel
hey :) i really love ur videos...currently, i'm taking a course about database development, basically, i'm learning it from 0 so from the most simple SELECT statement...i think my course is horrible, very hard to follow for someone like me who doesn't have any kind of IT knowledge whatsoever and ur videos are helping me a lot clear up the ppt-s i see in class. near that it's hard to follow the presentations, we make very little practice so i have very little idea if i can use what i learn or not...could u pls recommend me some website (free or with a subscription) where i can practice sql/mssql? cuz like with UNION or EXCEPT, i understand the idea and logic of this statement but i have no idea if i could actually use/recognize them in an exercise. thanks in advance. Agnes
Finally someone who simplify an explanation. I found that most IT guys try to show their flex more than teaching others and end up losing the listener completely. Thanks for being straight to the point and not over complicate things!
Very well done! Concise, to the point and no fluff. A+ 🙂
Thanks man, I'm working and taking a course of SQL (I use it at work) and this class cause me a little confusion about how to use it
I just found your channel now, too bad you don't upload new videos any more. You explained these queries very well. I'll definitely watch more of your videos.
thank you !!!!!, your channel is helping me so much in university !
Thanks for the Great Explanation!
this is perfect explanation im crying this is too good to be real, thank you
Just a question: what would we do if we need to the union all for two tables and then save it as a new table. For example 1 union 2 makes a new table 3. what would be the command for it.
wayyy better than my teacher! thanks for the video4
very clear tutorials thanks so much. would have enjoyed if you could give some practice q's to help us reinforce learning ! this is much clearer than another tutorial course i paid for (i came on here was was confused and your vid made it very easy to understand.thanks so much
Great job!
Would be good if you elaborate a bit more on the logic behind the results at some points like "the except all here would bring back all the countries that are within customers, as many times as it appears there (the duplicates) and as long as it does not appear in the suppliers table" - something like that...
When you say "countries that aren't in both tables", it is vague - try to be specific. great job tho
A simple example for those who are looking to understand the difference between except and except all:
create table x (V1 numeric);
create table y (V1 numeric);
insert into x values (1),(2),(2),(2),(3),(4),(4);
insert into y values (2),(3),(4),(4),(4),(5);
EXCEPT [ALL] matches on all columns from both tables, column types and order must match.
select * from x except select * from y;
| v1
----
| 1
select * from x except all select * from y;
| v1
----
| 1
| 2
| 2
In EXCEPT sql process distinct datasets, so any duplicates are automatically removed leaving only single copy of each row. This results to exclude a row based on match just one in row in second dataset.
On the other hand EXCEPT ALL process datasets taking duplicate rows quantities into account. This results to return the exact difference in duplicate rows between tables.
Thanks, @omar for pointing out the issue in the video and I'm trying to help other people who want to get more clarity on understanding the concept.
Thank you for the video. Please do upload more :) :).
well done, I watch your videos cause they are short, explained well and make DB look easy!
I have a large table. I want to retrieve ALL the records that have duplicates in the name field. So if John Smith appears once in the table then I want to exclude him completely from the result. I am not interested in anyone who only showed up one time. But if he appears more than once (let's say 7 times) in the table then I want ALL 7 of his records shown. And the same for Mike Jones and Amanda Brown and everyone else. I am only interested in those who appear more than once, and then, I want to see ALL the records they appear in, since the different fields have additional info about those people. Can you show me how to write such a query? Thanks!
"Except" returns only unique countries that
present in the left set, but not in the right one. And you said "There is a list of the countries that aren't in both tables." I don't think that statement is correct because we have 21 countries in "Customers" and 16 countries in "suppliers" and the intersection is 12. That means 9 unique counties in "customers" and "4" countries in "suppliers". So your statement says "that aren't in both tables" which means 9 + 4 = 13 countries. They are not in both tables, that's correct. But you received 9 in data output which means that we got just unique countries from the left set "customers", but not in the right one "suppliers". That's why in my opinion you can't claim that there is a list of the countries that aren't in both tables. Maybe i am wrong because I am not native speaker and I misunderstood something... Best regards to this channel
yooooo!!! i watch your videos about cryptocurrency when i was trying to learn what DeFi is. Now you're on my field too! Thats pretty cool!
Beautifully explained 👍
Thanks, buddy. You helped me a lot this semester.
i really love your videos, it's helping me to understand the sql. simple and very clear, thankssss:)))
Hi from Brazil! Thank u very much man!!
amizing
The 'except' cannot be found in suggested commands. I use xampp. Is xampp lack something?
شكرا
Thank you for this exercise
Love what you're doing!
Good job
Great explanations!
Great video!! Thanks man.
the like is because I loved his intro
Great. Thanks so much!
hey :)
i really love ur videos...currently, i'm taking a course about database development, basically, i'm learning it from 0 so from the most simple SELECT statement...i think my course is horrible, very hard to follow for someone like me who doesn't have any kind of IT knowledge whatsoever and ur videos are helping me a lot clear up the ppt-s i see in class.
near that it's hard to follow the presentations, we make very little practice so i have very little idea if i can use what i learn or not...could u pls recommend me some website (free or with a subscription) where i can practice sql/mssql? cuz like with UNION or EXCEPT, i understand the idea and logic of this statement but i have no idea if i could actually use/recognize them in an exercise.
thanks in advance.
Agnes
how to combine table with different column without using join
Very helpful thank you!
awesome!!
Tnx
Why do we even go to school and pay a lot of money anymore?
Thank u so much broo