MySQL vs Postgresql vs Microsoft SQL Server Management Tools | Which Option is Best?

what's going on everybody welcome back to another video today we're going to be comparing some of the most popular types of sql including mysql postgresql and microsoft sql server [Music] now when i was first starting out i had no idea that there were different types of sql i just thought sql was sql just like excel is excel it's just a tool and everybody uses it the same way but i started learning my sql and then the first job that i got was microsoft sql server and it blew my mind it was completely different than what i had studied and that may be exactly where some of you guys are at right now which is you're either studying it or you're using one of these types of sql and you just realized that there are different types of sql out there and you don't know if the one that you're studying or the one that you're using is the best option so in this video we're going to be comparing three of the most popular types of sql out there i'm also going to pull up on my screen each of these sql databases kind of walk you through the user interface as well as some of the differences between them so you can really see the differences between these different types of sql and then at the end i'll give you my thoughts on which one i think is the best for different uses and the ones that i personally like the most so without further ado let's jump on my screen and take a look at these different types of sql so again we're going to be taking a look at mysql postgresql and microsoft sql server and i did not choose these randomly i chose these based off popularity so if you look at these graphs or actually surveys taken from stack overflow where people were able to vote on their favorite type of sql and these were the results and so each one of these represents a different year that they took the survey which was 2016 2017 and 2018. and so if you look at each of these you'll notice that mysql postgre sql and microsoft sql server are consistently ranking amongst the top so now we just want to look at a really high level view of some of the similarities and differences between these three different types of sql the first thing i want to look at is whether it's open source or not and open source just means it is freely available you can go and download it and use it in any way you want completely free so looking at mysql my sequel is open source and it's actually developed by oracle and oracle has an oracle sql which is kind of their commercialized version which is paid and you cannot get for free so mysql is oracle's free or open source option although they do have upgrades you do not have to purchase them and then there's sql server which is not open source is a commercial product made by microsoft but there is a free version called sql server express and you can download that and use it but it has a lot of limitations and so it's definitely not open source and then there is postgre sql which is open source and really doesn't have a paid version so it is truly open source now the next thing that i want to look at is what operating systems these types of sql are able to work on and my sql and postgre sql are really going to work on any type of machine or operating system that you have really the only outlier here is microsoft sql server which only runs on linux and microsoft and so if you have a mac and you really want to learn microsoft sql server you might have to boot up a virtual machine or a remote desktop or something like that in order to actually use it which is just really inconvenient to be honest and lastly what kind of sql do these types of sql actually use well there's the standard version of sql which is basically your base level of sql and it's going to have a lot of the same functionalities and so my sql and postgre sql are really that core type of sql the only outlier here again is microsoft sql server where they have something that they invented called t-sql which is called transact sql and that sits on top of standard sql and has a lot of extra functionalities and so they all are really built on the standard version of sql it's just microsoft to win a little above and beyond to add some functionality that the others don't have so now i'm going to pull up each of these different types of sql databases you can see the user interface as well as kind of how they work so we're going to be taking a look at mysql first and i'm using mysql workbench that's what almost everyone's gonna be using when they're using my sql in order to query their data so right here i just have a test script written so you can kind of see what the syntax of it is and you'll note that all of the syntaxes are very similar but there are some key differences i'll try to point those out as we go through but you know we're mostly looking at the user interface here so here's a query window and we kind of have that open so over here we have our test database and we have our tables and in this table right here called bike buyers clean we have all of these columns right here and so that's the actual table that we're querying off right here now if i take this really quick and i put it down here i just say select star which means select everything i can highlight this and i can click this button right here which is going to execute and it's going to show me all the data in here and now if i want to run this i can select this right up here and run and i can get you know this aggregated query that i've done right here where we're taking the average income and if you look right down here there's actually this output and you can kind of see the different um the different queries that we've done in the past how long they took and some you know useful information and right up here on the top left there's lots of different things that we can do i'm not going to go through all of them but you can get a new tab for sql and you can start writing new queries you can create new schemas and new tables and views as well as stored procedures and if you go up here you can connect to different databases or manage the connections that you already have you can also come over here to this server where you can import and export data and it kind of helps you do that and so just looking at mysql as a whole it is very simple it is very clean and understandable there's nothing really confusing about it and the syntax is pretty easy and pretty simple to pick up and understand all right so let's look at microsoft sql server and we're actually in something called microsoft sql server management studio called ssms and so it's somewhat similar in a way to mysql right you have all these options up here and we'll look at those in just a second you have this editor window you have this output window and then on the left you kind of have this object explorer that shows you the hierarchy right so we have our databases within there we have our tables and so if we come down here this is the one we're looking at right here this employee demographics if we right click on it we can select top 1000 and it's just going to select the top 1000 of all of these and so you know it's pretty simple it's pretty self-explanatory once you actually kind of get a feel for it it's very simple um just like my sequel one difference i will say and it's hard to really show this um but getting data into a one of these databases i've noticed that it's significantly easier at least from my opinion to get data into microsoft sql server it's a little bit more challenging i found to get it into my sql again it's kind of not something i can easily show in a video and so just like the last one i have this script right here that kind of shows just a little um you know aggregate query where we're grouping things and kind of filtering on things and we get this output and so the one of those differences that you may have noticed is that at the end of my sequel you're going to get the semicolon we don't need it we don't have to have it traditionally with mysql you need to have it and so earlier i mentioned that microsoft sql server has something called t-sql that sits on top of standard sql that only microsoft has and so to give you an example you can do select top 10 and unless you have everything and we'll do from employee demographics and this is the exact same thing as what limit 10 is in my sql but it does it a little bit differently in microsoft sql server and there's also another really cool thing that you can do which is you can kind of see the inner workings of the back end of sql using these sys tables so i'm going to do select everything and we'll do from and we're going to say sys dot tables and we're just going to run this now right now if we just run it without using something like use and we do sql and we choose a database it's just going to run off this sql or this database right here but what we're going to do is we'll select sql tutorial and we're going to hit execute and these are the three tables that are right here and so if you have hundreds of tables then you can start filtering on these tables you can start filtering on when you added these tables or when you created them and you can even do go as far as the columns and you can really use these and i've used these a lot myself you can really use these to dig in and and find things much faster than having to manually go in there and find it yourself and then up top here just like in my sequel we have all these different options and i will say i think it's a little bit more confusing than in my sql because there's just more options there's more things that you can do and so you know you can just like before you can create a new query and we can also do things like commenting out so you know if i just want to comment this out there's lots of different random things that you can do we can save files we can open files and if we want to add a new connection we can go over here we can select file and choose you know how we want to import that data so overall sql server is really good and if you compare it to my sql the ui is actually quite similar they look a little bit different but you know they have the editor window they have the output they have this object explorer where you can see you know their tables and your databases and they have this area up here to add some extra functionality probably the biggest difference is that microsoft sql server is a paid option it has a lot more functionality and it has t-sql which i haven't been able to demonstrate super well but if you really dig into it and look at it and use it you'll notice that t-sql has a lot of really great features for querying your data it's actually really helpful which is why i personally really like it so now let's go into postgresql and to do that we are using pg admin 4. it's kind of like using the mysql workbench or the sql server management studio for this we use pg admin 4 and right off the bat you'll notice it's pretty similar except it's even i would say more simple than mysql so on this left-hand side we have our database or we have our servers up top we have our database all the way down here we have our tables and then we can look at you know our columns within it and right off the bat just when glancing at it you notice right away that the syntax is a little bit different right i have these quotation marks and i believe there are different versions where you don't need the quotation marks but i've always used pg admin 4 where it does have it and i know pg admin 4 is pretty popular we can go in here we can right click and we can go to scripts and we can go to select script and it's just going to pull up the select statement which we can then go over here and we can execute and so now we can look at this data it has this explain messages and notifications uh which is pretty interesting as well something that is unique to postgre sql something that i think is just really interesting although i don't use it a lot if i'm being honest is it has these other options up here like this dashboard where you can see how many queries you're running how long it took to run the transactions per second really interesting information again not something that i go into too much or i've ever really gone into too much and you can look at the you know properties this one specifically it's kind of shown in a different way in the different types of sql but it just has it as an option at the top so that you can kind of just go in here click around instead of having you know click on drop downs click on the menu items it's all just kind of right in front of you so again if we look back up here we have an option to just create a query window like we did before we can look at our data so we can just view this data if we click on that button so it doesn't just pull up by script for us it's actually viewing the data um and then there's other options up here so you know we have this file object and tools and then you can go down here to import and export data so you actually import your data from here and and if i'm being completely honest um i would say that in postgre sql i've had the most issues with importing data it's a little bit tougher than my sql it's a little bit tougher than microsoft sql server that's just my opinion but um you know you you just got to make sure you have everything exactly right otherwise you know you'll definitely hit some errors and it can be a little bit frustrating but you know each type of sql has its issues this one to me is just always one that i have personally had issues with so thought i would mention it all right so now that we've actually gone into each type of database and we've taken a look and we've kind of seen the differences between all of them i'm going to give you my overall thoughts on each of them starting with my seagull i think it is a fantastic option if i'm being completely honest with myself i think that mysql is probably the one that most people should start with because my sequel is probably the most straightforward syntax-wise it's very easy to get up and install the user interface is very straightforward and it's completely open source which means you can actually use it for data or your business or for learning or really whatever you want and so i genuinely just think it is a fantastic option for a lot of people out there and i think a lot of people agree because if you saw those surveys from earlier a lot of people really like my sequel and use my sql the next one is microsoft sql server and if i'm being honest this is the one that i have used the most in my career and i'm super impartial too because i absolutely love it but i don't recommend it over my sequel by just a hair because my sequel is open source anybody can use it for anything and i think that is a beautiful thing and a lot of people are not going to pay for microsoft sql server although i think t-sql is the best i absolutely love it i've used it so much i just think that if i had to choose between mysql and sql server i would choose mysql because it's completely free and it always will be and has no limits because of the free version like sql server has and lastly we have postgresql which is really just the biggest competitor to my sequel because they're both open source i really do like the simplicity of postgresql but if i'm being honest i like my sequels user interface a little bit more i'm also not the biggest fan of the postgresql syntax again it's just it's just not as intuitive as my sequel it's absolutely not as intuitive as sql server which is what i think is the easiest syntax out of all of them but to me postgre sql is really the third option out of all of these i think that the best one the one that i would start with and one that i recommend right now is my sequel if you were just picking up sql so if i had to rank them on which ones i would recommend to you to learn first or to use i would recommend my sequel first i just think that it is the best one for the most amount of people i would then recommend sql server that is the one that i personally have used the most the one that i personally love the most that does not mean that i recommend it to everyone again it costs money and the free version does not have everything and so i don't recommend it as much as my sequel and then in third place it'd be post gray sequel again quite a good competitor to my sequel i just i think my sequel is a little bit better in a few different aspects which we discussed but postgre sql is still a really good option if you're looking for something open source and you're really wanting to learn sql so that is my comparison that's my overview of these three different types of sql i hope that you found that helpful if you did be sure to like and subscribe below [Music] you

Channel: Alex The Analyst

Views: 50,573

Keywords: Data Analyst job, Data Analyst Career, Data Analytics, Alex The Analyst, mysql, postresql, sql server, Microsoft sql server, difference between mysql and postgresql, mysql vs postgresql, mysql vs postgresql vs sql server, mysql vs postgresql vs Microsoft sql server, different types of sql, best type of sql, is mysql good, is postgresql good, best sql type, best version of sql, sql types

Id: Q7i6zxHVj2Q

Length: 16min 5sec (965 seconds)

Published: Tue May 17 2022