Intermediate SQL Tutorial | Unions | Union Operator
HTML-код
- Опубликовано: 25 мар 2020
- Take my Full MySQL Course Here: bit.ly/3tqOipr
In today's Intermediate SQL lesson we walk through Unions.
All Topics Covered: Joins, Unions, Case Statements, Updating/Deleting Data, Partition By, Data Types, Aliasing, Views, Having Clause, GetDate(), Primary vs Foreign Key
____________________________________________
SUBSCRIBE!
Do you want to become a Data Analyst? That's what this channel is all about! My goal is to help you learn everything you need in order to start your career or even switch your career into Data Analytics. Be sure to subscribe to not miss out on any content!
____________________________________________
RESOURCES:
Coursera Courses:
Google Data Analyst Certification: coursera.pxf.io/5bBd62
Data Analysis with Python - coursera.pxf.io/BXY3Wy
IBM Data Analysis Specialization - coursera.pxf.io/AoYOdR
Tableau Data Visualization - coursera.pxf.io/MXYqaN
Udemy Courses:
Python for Data Analysis and Visualization- bit.ly/3hhX4LX
Statistics for Data Science - bit.ly/37jqDbq
SQL for Data Analysts (SSMS) - bit.ly/3fkqEij
Tableau A-Z - bit.ly/385lYvN
Please note I may earn a small commission for any purchase through these links - Thanks for supporting the channel!
____________________________________________
Websites:
GitHub: github.com/AlexTheAnalyst
____________________________________________
All opinions or statements in this video are my own and do not reflect the opinion of the company I work for or have ever worked for
I see you inserted new rows in EmployeeDemographics and created WareHouseEmployeeDemographics, but you did not put the code on Git. I will let it here, hope it helps:
Table 1 Insert:
Insert into EmployeeDemographics VALUES
(1011, 'Ryan', 'Howard', 26, 'Male'),
(NULL, 'Holly', 'Flax', NULL, NULL),
(1013, 'Darryl', 'Philbin', NULL, 'Male')
Table 3 Query:
Create Table WareHouseEmployeeDemographics
(EmployeeID int,
FirstName varchar(50),
LastName varchar(50),
Age int,
Gender varchar(50)
)
Table 3 Insert:
Insert into WareHouseEmployeeDemographics VALUES
(1013, 'Darryl', 'Philbin', NULL, 'Male'),
(1050, 'Roy', 'Anderson', 31, 'Male'),
(1051, 'Hidetoshi', 'Hasagawa', 40, 'Male'),
(1052, 'Val', 'Johnson', 31, 'Female')
Good catch and thanks for adding this! Super helpful!
@@AlexTheAnalyst You should pin this comment, would be much easier for people to see it! Luckily I scrolled down and noticed it :P
Thank you Paul!
Thank You Paul
Thank you Paul!
tldr: join = concat columns, union = concat rows
Hi Alex, Just stopping by making a break from watching your videos in a row, to say thank you not only for the great and prompt explanatory videos but also keeping the eagerness on learning more about SQL and waiting for every next video to learn more. Having left SQL memories back to my IT college years because of following slightly different direction , but now after many years that i decided to refresh my knowledge i feel like i've been taught SQL so smoothly! We are definetely lucky to have inspiring people like you!
You're most welcome my friend!
You are really not boring. I could watch your tutorials for hours without resting LOL. Keep up the good work Alex.
Hey Alex, i just wanted to say thank you so much!!
Your way of explaining concepts is actually amazing.
That ven diagram in the Join video was fantastic! That really helped me make sense of joins. Thank you! Great video!
Can't thank you enough Alex! Have learned so much from your videos, simply thanks!
You've explained this concept more clearly than any other learning platform! Thank you!
Really glad to hear it!
hello alex thanks to ur data analyst boot camp it have helped me so well in my DA journey i used it not just as a starting point into my DA journey but also as a guide as to the important things i need to learn even when i watch other tutorials u did so well on how u explain things and make it easier for anyone to understand. thank u for what u do may God bless u
On my journey through the boot camp...
New learning again. Awesome!
Thanks Alex, this is indeed great.
You made join so easy to understand, gracias man!
Amazing work! Can't believe I am actually enjoying learning this
You are such a great teacher a looking forward to finish the Bookcamp and also perfecting my skills on SQL. Thank you so much. I follow you everywhere on your social media.😊
The WarehouseEmployeeDemographic came from?
Great tutorial - thank you, Alex! This was super helpful
CTRL + SHIFT + R
Refresh local cache for Intellisense. Sometimes Intellisense doesn't recognize newly-created objects; this shortcut forces SSMS to refresh its object list in cache
Hi Alex, just stumbled upon your videos while searching for best ways on how to be a Data Analyst and I can say that, man, your videos are super helpful, better than when I was in my days as an IT student. Your videos are very easy to understand and so much engaging. Hope to be able to finish this all in just a few months and then I will take the certification with Google to complete my adventure to being a Data Analyst. Keep up the great work and videos and here's to hoping you will post more as the trend nowadays about technology are fast. Thank you!
Really glad to hear it!
Hey, have you been able to get the certification with Google? If so, then has it helped you in the Data Analyst job hunt?
Outstanding as usual!
This is very similar to a concat/append when working with python pandas data frames.
Very cool
Thank you so much for this videos alex
Thanks very, very much. I think I finally understand the joins concepts. I'll need to practice a lot more because I think the hardest part of SQL.
Anyway, Thanks a lot for your help in changing my career.
Great Video! Hoping that you can do some explanation on Pivot function in SQL. Cheers!
Excellent Video
Thank you for these kinds of videos! I'm a management information system major right now, and this is a great refresher for me. can't for all the other videos!
Thanks Tausif! So glad it is helpful, I look forward to releasing them soon! Thanks for your support!
Good stuff as always, Alex! Do you have a rule of thumb to avoid UNIONS if you can get the result you need with JOINS? I've always considered connecting data based on a clear relationship / keys in the data as the best way to go. In any case, cheers and thanks!
Awesome. I am growing bit by bit
Thank you so much sir...these videos are just awesome and you make it so easy to understand❤❤❤❤
Sir, your tutorials are superb !
Thank you! So great to hear!
@@AlexTheAnalyst Sir, please try to make videos on Advanced SQL.
great boot camp for begineers
THANKS, ALEX!
Hi Alex 🙋♀️ Thanks for making these good videos. I want to add - the order of datatype in selected columns should be same. Like in our case: int, varchar, int for both select queries. If this is not same in both (or more) select queries, then union fails.
YES, I got error.
I certainly love the way you are presenting SQL. I do not remember creating data warehousing in the beginning. Did I miss something? LOL
Hey @Alex The Analyst. Very good tutorials. Any idea when you would able to upload some more intermediate and advanced tutorials for SQL. Thanks buddy! 👊🏾
hi, how is the journey has been for you so far ?
Thank you !
Loving this great series. Is there an advanced series?
Only seen the beginner and intermediate.
I am however very grateful for these Alex
I'll be adding a few more videos to the intermediate series and then moving onto the advanced!
@@AlexTheAnalyst Thanks Alex
Thank you Sir
Hey Alex, it's 2024 and i just wanna say God bless you and your family.
brilliant sir
I haven't seen this video yet.
I started this playlist yesterday now I got my first star in SQL at hackerrank you are Amazing Keep going legend
You got this!
This was helpful
Thank you
Thanks.
Had to watch this like 3 times 😅 but yeah! Join = merge columns, Union = merge Rows
Hi! Thanks for the videos!
When I create new tables, then when writing, the system does not suggest them like it does with previous tables, what what could be happening? (The query works well anyway..)
Hello Alex thanks for the WareHouseEmployeeDemographics. How did you set that up. Thanks
may i ask what a practical use of the union would be in cases where tables don't have the same column structures / input as in the example on this lecture.
I have a question, where did you get the Warehouse Demographics from? Is it just an example... or? We needed to create another table?
Awesome vide. Why in your join the employee ID column is duplicated ? I mean in the beginning of the video, after you joined them , you should only see one column for employee ID, thats the purpose of join to create one table. Is this presentation unique to SQL server? because i dont have this with MySQL. Thanks
Thanks!
Thanks for the support!
Good evening Alex, I’m enjoying your tutorials.
Question, do you have a tutorial in entering the warehouse employee demographics table?
Yes, I believe it was in the beginners series
I also learned that when using UNION and UNION ALL, There has to be an exact pairing of the columns for each table.
It will produce an error if you try to use UNION and SELECT different columns for each table.
You mean different no. Of columns?
Please where is the data for the table "WareHouseEmployeeDemographics"?
can you make a video on semi joins and anti joins please?
hi u did not show creating warehouse table and inserting rows.. so what data should we insert ?
hi Alex i love your content it's very helpful for me to spawn my SQL technique, just wonder if I have two columns n each column nothing's similar and don't have an equal number of expression. what's code should I use? i tried to use union it didn't work same as join either. thanks in advance
*if i have two tables
If nothing is the same but you still want to join them you can use a cross join in MySQL or a Full Outer Join in SQL Server
@@AlexTheAnalyst thank you for answering my question Alex:)
Question - if i made a UNION, how can i save the output as a new table?
Hi I have a problem, anyone help please. So, on my table 1001 is jim but instead of 1002 for the next row , it shows 1011 (ryan) . I do not know why. Thanks for your response.
U didn’t mention the video of employee warehouse details on this bootcamp ?? … suddenly a table appeared 😅
When the Table is created in the database and it's not showing the in the database after refreshing of many times.
What should I do now?
Still making progress.
How do I get the database you’re using
Hi Alex, in video 8 how did you come up with the warehouse employee demographics table. Thanks
Hi, you can see it has been answered by Mr. Paul above.
Done
Do we only use union if we have the same table?
2:22 You might think like "Where that the 1st row came from?"
Explanation: At 2:01, you do not see the all rows from the first query; only 10 rows of that are shown.
When I tried the UNION (not UNION All) Darryl showed up twice for me. Does anyone know what might have caused this?
Msg 205, Level 16, State 1, Line 5
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
Dwight earns almost as much as Michael? Hahaha, got to love those sales commissions.....
sir, you have not provided the document or file of warehouse Employee Demographic in this video or in the previous video. So please can you offer us that database or file
Question for anybody !
If i am attempting to clean data that I unioned and I want to keep for data visualiziation later, how do I save that union as a new table ? TIA :)
3:36 But how do you identify based on the result of the query which employees belong to warehouse employee table and which employees belong to employeedemographics table? Assuming you have hundreds of data in the real scenario? In excel it is easy to identify.🤔🤔
So wouldn't you just use unions and where the columns contain the same data joiner (say employee id) make that the filtering attribute?
It seems to me that unions are far more flexible but you still be careful to qualify it otherwise a union will just jam disparate data elements together
Physician, know thy data 😀
So, I experimented with what would happen even if the number of tables is the same but the column name is different. It doesn't work when the value types are different. lol
Creating the warehouse table:
Table 3 Query:
Create Table WareHouseEmployeeDemographics
(EmployeeID int,
FirstName varchar(50),
LastName varchar(50),
Age int,
Gender varchar(50)
)
Table 3 Insert:
Insert into WareHouseEmployeeDemographics VALUES
(1013, 'Darryl', 'Philbin', NULL, 'Male'),
(1050, 'Roy', 'Anderson', 31, 'Male'),
(1051, 'Hidetoshi', 'Hasagawa', 40, 'Male'),
(1052, 'Val', 'Johnson', 31, 'Female')
So ultimately, unions do not need a common table to combine the data?
That is correct, but if the data is not the same you're going to create a mess lol
@@AlexTheAnalyst Oh I see! Thank you!
PLEASE EXPALIN THE CONTENTS OF WAREHOUSEEMPLYEEDEMOGRAPHICS AND HOW AND WHY YOU MADE IT YOU DIDNT EXPLAIN THAT IN THE PREVIOUS VIDEO AT ALL . THANKY YOU
why don't I get NULL values
No love for Phyllis and Creed?
where the fuck the ware house data base came from ?
you really need to edit sound of your videos. im so annoyed of your basses and sizzles
Table 1 Insert:
Insert into SQLTutorial.dbo.EmployeeDemographics VALUES
(1011, 'Ryan', 'Howard', 26, 'Male'),
(NULL, 'Holly', 'Flax', NULL, NULL),
(1013, 'Darryl', 'Philbin', NULL, 'Male')
Create new table:
CREATE TABLE SQLtutorial.dbo.WareHouseEmployeeDemographics
(EmployeeID int,
FirstName varchar(50),
LastName varchar(50),
Age int,
Gender varchar(50)
)
Add data to new table:
INSERT INTO SQLTutorial.dbo.WareHouseEmployeeDemographics VALUES
(1013, 'Darryl', 'Philbin', NULL, 'Male'),
(1050, 'Roy', 'Anderson', 31, 'Male'),
(1051, 'Hidetoshi', 'Hasagawa', 40, 'Male'),
(1052, 'Val', 'Johnson', 31, 'Female')
I appreciate you
🐐
Done
Thanks!