Thank you for everyone's feedback on this video. The intention of this video was to compare the UI and management tools for each of these, but the title and thumbnail was not representative of that. I updated the title and thumbnail to reflect it better. I do plan on diving into the actual databases to look at things like scalability, speed, and more. Thank you again for your feedback!
I have used all these, MS SQL Server and PostgreSQL extensively for decades, nearly all in large system / large data applications. MySQL has been used for small systems. Also used Oracle quite a bit, especially in the 90s. Review mostly focuses on the DB front end tool, rather than the DB capabilities, so a very misleading title / blurb to the video. There are multiple DB user tools, other than the standard ones supplied by the DB, including ones that work across multiple database types. The front end is a factor, but should NOT be the driving choice when looking at DB systems, unless you are a rank beginner, or have very simple DB needs. This review is NOT highlighting how to actually select the right database, as behind the toolset they offer very different capabilities. For example, Postgres has unparalleled support for time series, sharding and geospatial systems. PostGIS is the most powerful and widely used GIS DB extension / capability available on any DB type. PostgreSQL is ultra-extensible. The extension system is designed in from inception, and in addition to sharding, time series, and so on, also provides multiple database programming languages. In addition to pgplsql (the equivalent of MS SQL TransactSQL), you can program in Python, C/C++, and many others via suitable extensions. The list of extensions to support various capabilities likely runs in the 100s, and includes adding in different indexing and datatype options. MS SQL: I used this extensively as well. The big stand out here for me is Transact SQL. If you are doing complex, large data set manipulations, or are really locking down the database against misuse via the use of triggers, rules and so on (can do the same in Postgres), this can be a life saver. MySQL: their procedure / procedural support is very young, and nowhere near as robust as MS SQL or Postgres. MySSQL mostly came out of the embedded / small system / front end developer space, and has grown from there, where both MS SQL and Postgres have large system DB roots. (See ORM mention below) So, I would say, if you are a beginner / front end developer, who is not going to focus on the DB capability, and has a fairly standard requirements list. look at MariaDB (open source MySQL), but you may want to consider PostgreSQL depending on your target application environment. MS SQL is great, but not open source. ORM: The other use case for MySQL is if you are using the DB behind an ORM (object relational mapper), where the DB is just a data repository. Then it really doesn't make a huge difference which DB you select, as they are mostly black boxes. You should choose the DB that your ORM supports best, and is cheapest to own / operate. Obviously issues such as reliability and performance are important, and here Posgres and MS SQL have decades of experience to show. In our case, PostgreSQL was the only choice. Our application is heavily GIS (Geographical Information System) and time series focused, where we have to analyse 100s of thousands of records for a single transaction to provide the functionality we need. We also use pgplsql functions heavily for update and extract transactions as we have multiple front ends for the application. MS SQL was tested but the GIS component proved to just be too slow and not nearly as robust / functional as PostGIS. MySQL just didn't have the functionality to any useful degree. We could have used Oracle, but the costs were prohibitive....
I would only add that the Enterprise features in MS SQL Server are pretty nice and easiest of the three to implement. PostgreSQL is my top choice as well though.
It looks like he took your comment to heart because the video is now titled to specifically mention "Management Tools" as opposed to the databases themselves. I don't specifically recall the original title, but I have to assume you didn't write such a thoughtful reply and miss that. I remember thinking something similar when I first watched it, and that the video seems focused on a choice for someone first trying to learn SQL and data analysis rather than someone choosing a DB for serious application development, which is what I thought when I first clicked on it. EDIT: I just noticed the author posted a comment saying he did change the title & thumbnail to reflect the focus on the tools.
There's very little comparison between the databases shown in this video. It would be better if you would show us how the databases excel one above the other in use cases. 80% of the video is comparison between MySQL Workbench vs SSMS vs PG Admin. These are actually tools to access these databases. You can access these databases with other tools like SQL Assistant. These are actually comparisons of tools not the actual database.
I agree. Half-way through the video I was wondering why he was explaining the interfaces so much. These interfaces are completely separate from the databases themselves. Absolute click-bait title.
Saw this after I posted my comment. Good to know I'm not the only one not happy about this video, ended up watching only because it kept appearing on my feed but I had a sense it's clickbait
I couldn’t have said it better myself. Anyone seriously working with a relational database is interacting with it via some API such as JDBC (for Java) or something similar. The GUI tools are helpful when composing queries but don’t generally come into play once your application is deployed, at least not for the end users. More important are things like how the database handles locking and concurrency, rollback, backup, restore, replication. MySQL was bought out by Oracle, but there is an open source fork called MariaDB which was not mentioned. Amazon offers a commercially supported fork of PostgreSQL which is available in the cloud. I haven’t used SQLServer in about 20 years, but I wasn’t clear what extra functionality was being referred to that was so great in T-SQL. Is it the client software or the programming language built on top of the DB. If the latter, PostgreSQL offers Pl/PgSQL which is very similar to Oracle’s PL/SQL. The biggest difference there is that PG/PlSQL does not support the package notion ore some of the more sophisticated data structures that PL/SQL offers. No mention was made of the data types supported or the different types of indexes, how partitioning is supported, what kinds of triggers are available or the differences in how they work. PostgreSQL offers some interesting data types including ranges and coordinates for geospatial data. The issue about the quotes in PostgreSQL appears to be peculiar to the client tool. As I understand the SQL standard, and rom my experience with both Oracle and PostgreSQL, if you use double quotes around an identifier (say a table or column name), to refer to that object, you must use the exact case. If you define it without the quotes, it is matched case insensitively, although Oracle, for example internally upper cases such identifiers while PostgreSQL lower cases them.
PostgreSQL does have a SQL extension it's called pg/plsql, it's very powerful, in fact, PostgreSQL is much more advance than MySQL only drawback is administration it's not as easy as MySQL and MSSQL
The quotation marks in PostgreSQL are only needed for establishing names to columns which could collide with system key words - for example a column called "name". And using the Schema name before the table is only needed when you have duplicate table names. So in both cases it's not a "weakness" compared to other databases, but simple features that can be used given a necessary scenario. And in terms of interface, all these applications run on the actual database, having multiple options for each engine, so again, it's hard to say the least which is the "better option", when it's a matter of personal preference.
You can also connect to most databases from each others interfaces. And I know I've seen other databases where you can put multi-word field names in quotes.
My bet is the mixed case of the column names is causing the quotes in the generated select statement for the example in the video. All my table/column names are lowercase, and I don't get quotes in the generated selects.
@@SP30305ATL - Exactly, if all lower case or all upper case - no quotes are required. SQL Server does not care but it’s predecessor Sybase prefers all lower case definitions. Most designers of data tables capitalise first letters of multiple words in column names but without word separator. A bit of extra work to copy such data to databases that object to such format without quotes.
@@peterc5512 Oh, has it been your experience that most db designers you've encountered use mixed case column names w/o separators? I used to only see all-caps with underscores working with DB2 and Oracle in the 90s, and since the 2000s I've mostly seen all lowercase with underscores in Informix and PostgreSQL in the US. I'm curious if it mostly varies based on the database (or original db of the designer) or the dominant programming language? In Java, the predominant ORM by default converts Java standard camel case attributes like "productName" to lc/underscore like "product_name", which tells me that is a commonly used standard. I'd be curious what db/programming language/part of the world you find "ProductName" style most common? Perhaps it's a Microsoft standard? No big deal--I'm just curious.
@@SP30305ATL I cannot claim to have broad industry-wide experience except in few PACS systems running Sybase, SQL Server and our own state wide repository under Oracle 12C. When migrating data from source systems the Toad Data Point tool does not offer the same automated convenience you describe so I manually redefine column names on the target table to capitals with underscore separators. The newer versions of Oracle accept longer column names, luckily. However if I miss the definition step it still works, but all queries need quotes around them… I commented initially because I agree the mixed case was triggering it.
Having to play with the 3. Nowadays I liking Postgress better. It was much better support to JSON and it provides a plethora of built-in functions for time series and others
I learned SQL on Postgres and importing/exporting was frustrating if you got an error. I really enjoyed seeing the comparison of these platforms. Good stuff!
Nearly all the companies I’m interested in applying for use SQL Server, so I guess on the bright side, that’s made it a lot easier for me to decide which RDBMS to learn. lol
Open source: You are not mentioning MariaDB, which is the open source fork of MySQL, and is becoming the default available in most Linux repositories, rather than MySQL which isn't true open source.
PostgreSQL is usually pronounced "post gress" and if you included the QL, you say " Q L" at the end, but since Postgres can also be a NoSQL DB (key pairs, JSON document storage, etc.), most people who use it regularly call it just Postgres..
This is one thing that I know Postgres excels at. It's Json datatype performance and the rich functions around it makes it an excellent NoSQL db. I've read an article a few years ago that compares the performance of pg json system with MongoDB and it outperformed MongoDB, not sure if that still valid today. However, what's sure is that the Types in PG are richer than that of MySQL.
@@ChronicSurfer You mean inconsistent data types and structure? Wide use of MongoDB has only encouraged that and perpetuates the inability to follow a data structure.. Document store DBs have their place and are wonderful when used correctly...but using a document store DB to avoid data structure is just asking for trouble at scale.
My understanding, as a developer, is that as a data analyst you probably shouldn't really care about differences between different RDBMSs except query language and I hoped to see why I might be wrong in this video. But no, you just confusing RDBMS (Relational Database Management System such as MySQL, SQL Server or Postgres) and its client application (MySql Workbench, SSMS, PGAdmin4 respectively). So the only difference you mentioned between those RDBMSs is their query language. All other differences are really just some UI elements of their default clients. Anyone watching this, imo, should understand that distinction. You are not bound to single client app. There are many other clients, some of them even can work with several RDBMSs. And some of them are even can affect the query language! SSMS, for example, lets you write GO in the query editor as a delimiter between queries, but there is no GO statement in T-SQL, it's SSMS specific (their official command line client supports that too, I believe) delimiter, which makes your queries run as separate batches instead of one per editor tab. Some other clients give you more convenient features - Forge Studio for MySQL has quite good graphical query constructor. HeidiSQL is just very lightweight, can work with mysql, postgres and sql server, and probably something else, and it has freaking search bar in object explorer pane that even supports some regex magic (looking at you, SSMS, why with all Microsoft's resources there is no f-ing search bar right on top of object explorer??? Why search is buried in context menu and doesn't support regular expressions???). DBeaver is quite powerful and works with several different RDBMSs... That's just some clients that I remember and use or have used in past, there are surely way more of them. In fact, any application that connects to a database is a client for that database, just not every app lets you write queries yourself and shows results as boring tables. Some let you instead, I don't know, watch a video on your screen and read some comments below, and even write one yourself :)
Well, you should compare the SQL language dialect differences, because some of the DB servers have the built-in programming language 'extensions' and they are really quite different. But the SQL language is quite standardized (by ISO , IEEE standard or something) and the difference between the SQL dialects is mostly the servers' built-in functions, like string or data values processing and some special language extensions which are using the specific features of each DB-server. For example, the MS SQL server is using the T-SQL (Transact-SQL) programming language extension, but the PostgreSQL is using the PL/pgSQL (Procedural Language/PostGres Structured Query Language) and the Oracle DB-servers are using the PL/SQL (Programming Language/SQL) extension. The PL-extensions are used for the server-based business logic programming, mostly, for writing stored procedures, user functions, triggers, packages, etc. So, in my opinion, you should compare the SQL language dialect differences, not the PL-extensions and not the IDEs (which is not a property of the SQL language itself).
Not to sound harsh so I hope that you take this criticism to heart, but this video was more a comparison between SQL Clients (MySQL workbench, SSMS, PGAdmin) and not so much between the actual SQL languages. And not to mention that when you reached the PGAdmin part where you touched upon the dashboard tab, it became clear to me that you aren't actually that familiar with them. MySQL Workbench literally has something similar where you can see the server status (slow queries, queries per second, etc) which makes me think that you don't even know Workbench that well. And I am 100% sure that SSMS would have something similar as well. Also, there is a reason why postgres is a favourite amongst especially startup companies. Maybe do a bit more research on the topic next time. Again, I apologize if sound a bit too harsh. Just trying to share some feedback.
Sorry, but those are the user interfaces that comes by default with the DBMs. Not a comparison by itself, which should start by how well they implement the relational algebra, and then how they implement the the desired properties about transactions, concurrency, indexing, consistency, security, permissions granularity, logging, recovery, escalation, mantainance, troubleshooting support, isolation levels and scopes, hinting and how much you'd rely on them, and so forth.
I've used SQL Server at extremely large scale (taking in 1TB of data per day) and MySQL at very large scale. You get what you pay for. The performance, tools, configurability, SQL features, stability, etc are vastly better with SQL Server. It's not even close. I've only used Postgres in small instances but I would choose it over MySQL.
Agreed. The database administrator and reasonable leadership need to accept the best tool for the job. MySQL is great for regional small & mid-size business. Not for mega cap corporations.
Thank you Alex for keeping it direct and easy to understand. I worked for more than 20 years as a software developer since Turbo Pascal, Delphi, Visual Basic, Java, SQL, and after 8 years I'm getting updated to come back programming. Thank you for these videos.
OpenSource does NOT mean it is free, just that you can see the code but might not be allowed to use it/modify it/execute it. This is a common and important misunderstanding.
I really don't know who the audience for this video is. The content in this is not what I expected, you should've included *for beginners* . You also should've mentioned that renting a server you'll have to choose Linux or windows and that to run MS SQL you'll have to rent a Wondows server which is more costly than a Linux server. Also writing APIs to access and consume the data plays a big part in which DB is best given your requirements and skillset. Sure same points are beyond the scope of a short video but the title is then misleading. Anyway my backend starter pack recommendation would be: - learn php - use mySql Easy to learn, setup, cheap and fast to implement and make changes.
MySQL for small to mid-size business with a regional focus. Postgress for National and International focus. Best for this type of relational large scale database. MS SQL for a National and International focus, plus the MS discount for mixing it with Azure and Office 365. Best for those all into using Microsoft only.
Overall I think this video is good exposure for people who are unfamiliar with database options, but its lacking on accuracy in some ways. For example you mention T-SQL and that its only available for SQL Server and while this is true, you failed to mention that MySQL and Postgres also have something similar. T-SQL is just the scripting language, in MySQL its just regular ANSI sql, but in Postgres theres PG/SQL which is heavily influenced by Oracles PL/SQL. Also you mention not being able to show T-SQL which was again not particularly accurate. The easiest way to do that would have been to show that you can declare variables and use them in queries. Again the same applies to Postgres. the other thing that I found could have been a bit better is how you mentioned the sys objects in SQL server. There are equivalents in both MySQL and Postgres as well. To me it seemed that you were not necessarily comparing databases, but rather comparing the administrative and development tools for each database type. I've worked with all three tools, and a few others and I find that MySQL workbench is the jankiest, but then again its an Oracle product so its to be expected.
got my first job as a DA! the work is incredibly hard though. first five tickets and it feels levels above me for entry level DA work. hope i can pick up their material and not be fired
Dreyson, this is my fear! I have done the Google Data Analytics course but don't feel I know enough yet. I'm trying to fill in with some practice on my own. May I ask what skills you knew going into your job and what skills your employer was looking for? I hope it gets easier for you!
@@AlisaHof i only use SQL at my job but i knew tableau and excel pretty well and started to touch on R but yeah it’s just not the type of problems i was expecting to work. SQL tickets more about databases and servers vs like figuring out how many ppl make more than X or whatever. it’s my second week and they don’t expect me to be productive until like 3 months in but i still feel insecure on job security until i feel more competent
@@singleplystink3426 That would be very much over my head (also right now everything feels over my head). I've noticed that there are many job listings for data analysts/ business analysts, etc with descriptions that don't seem to be related to anything I've learned so far. Have you been able to find any resources to help you learn about these topics? Do you think your manager could help- or is that too awkward to ask? Have you been on any of the reddit data analytics-related boards? You might get some good feedback there. Anyway, I hope things get smoother soon !
@@AlisaHof i sprinkle in some questions but she seems annoyed almost any time that i do. i’m not sure if you can prepare for it since it’s just such a giant volume of data which muddies the water. i feel like i’m picking up on it slowly but surely, wish i could a little quicker but i’m basically on my own for 95% of the time. my regular manager is helpful but can’t really help with the meat and potatoes of my technical questions. it’s a lot but they pay me well for it lol it’s gonna be alright
I have 15 years experience with SQL and I think you explain it like there are big differences between them. You can very easily switch between them depending on the project you are working on. Unless you are the db Admin 🤣
It depends on how you are using them. If you're using an orm abstraction and basic tables, doesn't matter much. Geospatial, time series, and JSON data features it becomes massive. PostgreSQL is so much better as a developer than the other two. In MySQL/Maria, utf8 isn't and binary fields are indexed as strings by default. Magic ticks are treated like single quotes and a huge number of other inconsistencies and annoyances.
Not all switches are created equal! I grew my SQL skills under Sybase - going to MS SQL Server was easy enough, but Oracle took a bit of a learning curve due to many minor differences on gets to use in other environments. For example - try your sample of “select top 10 * from table” in Oracle and it spits the dummy, until you change it to “select * from table where rownum < 11, as well as variable assignments are very different.
Also, just one more thing in today's NoSQL database trend: Postgres has really strong support for JSON / JSONB storage types, including indexiing. Use this quite extensively in my applications. It's not _just_ a vanilla SQL database, which is where MySQL probably scores well. MS SQL historically has also been SQL+ due to the emphasis historically on TransactSQL
I used SQL Server extensively from about 1997 to 2003. One thing I recall is they had was ability to view the entity relationship diagram which is important for design. Does MySQL server client have this as well? I really want to get into SQL Development again, on my mac.
Postgres delivers a level of features and performance that far outranks both MySQL and MSSQL. It comes out of the box with a bunch of features and optimizations such as native json support, gis support and pg/psql. I agree MySQL is a good "beginner friendly" DB, but for serious work I only typically use two databases; PostgreSQL (stand alone deployment) and SQLite (embedded).
I have Microsoft SQL server and on pasting the code to create tables i get this error -A table can only have one timestamp column. Because table 'encounters' already has one, the column 'STOP' cannot be added. how to resolve this.
I'd be interested to know a feature Postgres lacks, which SQL Server has... Postgres is what I suggest people replace Oracle with. MySQL is what I suggest people who want to use just SQL use, and I don't recommend anyone use SQL-Server
Converted an app from SqlServer to Postgres. One huge issue is that Postgres does not allow temporary variables (you'd have to use a function), while TSQL does. There's a way to do that sort of functionality in Postgres outside of a function, but it's not intuitive, and it's barely written up on the internet, so it took a while to get an optimal solution. That said, I do like Postgres a bit more - seems a bit more "pure". Postgres also seems to have a lot of datatypes. This might be relevant for some, though it's probably not an issue for the vast majority of apps. And yes, Postgres is free and SqlServer licensing is very complex and can get expensive (in the lower tier, you need server licenses as well as client licenses, and the client licenses are per device).
Will focus on Posgres vs SQL Server in a quick post.Postrgres peformance is awful for complex sql with many joins, plus security is bad in most scenarios when you compare with SQL Server. This is from my experience with large tables. Things that take minutes and hours in postgres will take seconds in SQL Server. That's a fact. Also the default Postgres sql tool is not up to standard when you compare it to SSMS. Also if you are trying to locate a performance issue i have so many optons and tools in SQL Server but it's all very basic in postgres.
Standard SQL is called “ANSI” Standard (DB2). PostgreSQL follows the “PL/pgSQL” SQL Dialect (similar to Oracles flavor - but Im reading DB2 now supports PL/1 too). Transact-SQL was created by a company called “Sybase.” Microsoft as is their pattern “partnered’ with Sybase and basically took their tech to make MSSQL-Server. To day MSSQL-Server is my favorite flavor of DB - but I’ve been in Oracle/PostgreSQL recently.
Excellent comparison video - thank you!!! I used MongoDB (NoSQL) for a project because I wanted to be able to store ragged data formats for program preferences that wouldn't form neatly into a row/column table. I have since migrated to PostgreSQL since it has support for JSON and JSONB types which are even "indexable". Another big reason I went to Mongo was its ability to index spatial data (maps). Given Postgres's tight integration with mapping using PostGIS, it makes it a no-brainer for any map based database usage. BTW, I can't stand PG Admin, either when it was a compiled Windows/Mac program or now as a web based solution. It's slow and painful to use. I found a great multi-use SQL client, DBeaver, which in the free/community version supports many SQL versions and is lightning fast! Note: I have no relationship whatsoever to DBeaver - just found it, starting using it and became a huge fan!
Great video! Thanks! Well edited, you showed the interface and usage, the main differences, everything quickly and clearly, without wasting our time, and even gave an honest answer about which one you think is better instead of just saying "it depends"! Congratulations +1 subscribe
but only postgresql is 100% for free even for commercial use, right? and it provides geo spatial functions and it has much easier syntax for uuid and json object selector than mysql.
For common usage I agree. If you want most reasonable default setting on pages with low traffic MySQL will work fine without tweaking. BUT if you want o scale your project UP, Postgres on Linux OS is better because everything in it is configurable. Note: never trust anyone who tells you Master: Master geolocation separated replica works, and it is not advantage of MySQL, just a trap. Lost a lot of production data this way.
Thanks for the video. I use SSMS with TSQL for work, but want to create something myself and don't want to pay. Although there are free options with Microsoft, I would like to not worry about scaling up. Think I might test out MySQL.
You are comparing database system that are different. The syntax is part of the tool but never should be the first choice when it comes to decide which database system we are about to choose. For example, mysql is normally not durable (unless they changed it); it means that you might loose some information in case of crash. So if you own a company that needs to be sure that no data is lost in case of crash and you choose mysql because it's easier to code/understand, you might get a lot of trouble when you server will crash and part of your data is gone. When it comes to maintenance, there is always a trade off between a system that has great support and a system that has less (paid vs free). Same comes with community. The bigger the community, the less pain you will have when looking for someone having a solution to your problems... These are important criteria too. For the rest, every database system has most in common in the language.
Did you clarify that this is your recommendation for which one to learn on, not which is the better one to use in a company? I actually learned Oracle first - although I really started learning SQL in MS Access, which does let you edit the sql for your queries. I built datamarts in Oracle though But in the past 2 years, I've worked in SQL Server, Oracle, and Snowflake - sure I have to google what this DBMS uses for date functions or various other functions, but thats not a big deal. Its kinda like switching between MS word and Google Docs, or MS excel and Google Sheets . . . basically doing the same thing in a different interface with a few minor differences in how to do the same task
As a note, T-SQL was invented by SYBASE, not MS Both SQL Server and PostgreSQL are fully transactionals, but MySQL IS NOT The worst in MySql is the "Backup System", just a bunch of SQL Commands, not a real Backup. I like MySql, it's simple and cost nothing, it's also quite fast. There are diferent tools for different needs. For Enterprise Solutions, SQL Server, it has a HUGE amount of enterprise Features (Audit, policyes, different levels of encryption and security options)!!! In a second place PostgreSQL, which is related with SQL Server... SQL Server (based on Sybase 4.2 perhaps 1992)
Indeed Sybase invented TSQL, but only us silverback geeks remember. It was started as a codevelopment project with Sybase, MS, and Ashton Tate, who was the owner of the leading PC based database server at the time. It was actually to write a sql server for OS/2. This lasted for about six years or so till MS decided to go it alone since they’d sucked up as much knowledge as Sybase had to offer and they were in a position to use their knowledge of the server OS. It had always been a sideline project for Sybase who was really a Unix house. But both continued to release Windows server version. Sybase was better for a couple releases but eventually MS threw enough resources at it and turned out a better product.
are you aware, that you're comparing not the databases themselves, but merely just some selected (by you) GUI tools (and there are much more gui tools out there to compare) to interact with the databases?
Thank you so much, Alex. I have a question. I uploaded a data containing more than 1000 rows. I want to see all rows, but it always return 135 rows. How can it happen?
You seem to be relying on the frontend dashboard to evaluate these DB systems. For example, if u you use datagrip as your dashboard for Postgres your experience and evaluation would be different, ie better. Also, and more importantly, there are reasons to choose one of these DBs over the others based on concerns that have nothing to do with the frontend ui.
Great video! Thought I'd share my thoughts here on using MS SQL Sever on Mac, you probably might have heard this before -- it is possible to do so, using applications like Azure Data Studio or VS Code directly run on Mac, rather than on a VM. I personally like working in Postgres among the 3 :)
Ironically enough the MySQL in Steps book is the main one I am currently learning. It hasn't had any steps on learning how to import databases in the book. But that is ok. though. I do find it easier to learn. But I am going to go through the SQL Server one to understand the fundamentals too.
Hi great content!!! Question...I've been learnig from SQLite and now I want to learn MySQL but just upload a dataset is hell! it so SLOW!!! does are another way to practice in MYSQL or may be to upload data in a way that it does not too sloooooow?! your chanel is awesome!
Talking about importing the files into server SQL server is the best we don't need to take care about the data it takes all the data but and no need to convert to csv if it's an excel file. comming to postgresql it's a pain we need to input all column names even after inputing all the columns there is no gaurantee that the file will import without errors we need to take care about the formatting of the csv such a large porcess And comming to MySQL the import is some what easy but when exporting a large data set it sucks and takes more time even after waiting for the import for me sometimes it doesn't import any data these were faced 8n MySQL workbench,for MySQL we can use dbeaver which is better than MySQL workbench and import time a lot faster
My biggest issue with postgre is importing data and the quotation marks. I had to create the table Schema with exactly same column before it will work.
in my opinion as PostgreSQL user, Microsoft SqlServer in another level With t-Sql which enable you to create awesome functions run on server , Which Done by programming languages in another database engines , MySQL is so popular because of powerful but because of easy reach and host , but in SqlServer send some variable by the name of stored procedures and let hem do the validation entries calculation and return you confirm or he rollback all changes as bulk of entries done by one procedures
You can write server side functions in Postgresql as well, and you have severa programming languages to chose from,. E.g, PL/pgsql, PL/pythonn, PL/tcl, and java, javascrippt, R, maintained seperate from postgresql
I'm a MS-SQL guy but have a decent amount of experience with MySQL as well. I've always thought Management Studio was pretty awful when it came to general features from a UI perspective, when compared to something like dbForge Studio for MySQL. I WISH Microsoft would release comparable features (like having a table filter that supported multiple filters or even being able to colorize which DB you're looking at - small things). Ah well... some day, some day...
Hello i found your channel yesterday & watched some of you videos they are really great !!! good job on !!!! all the information very helpful i just have one question based on your experience & this video seems to me that the way to go to learn SQL would be MYSQL i saw that you you have some good video's about Microsoft SQL tutorial. so my question is where could be a place to learn specifically MySQL ?? i looked for courseara but could not find some options for Mysql i found a lot for IBM SQL but not Mysql so any place you can recommend me to look for Mysql learning ? im very interested in Data analytics skills because that is the way im moving my professional grow. so thanks a lot & im watching today your tutorials for Tableau because I'm interested as well to learn this tool so thank you for your time & dedication for these videos that help a lot.
Double-quotes for quoted identifiers identifiers are part of the ANSI SQL standard. Sybase SQL Server used single-quotes and double-quotes interchangeably to delimit strings. Microsoft inherited that behavior. But, they each changed their syntax to conform with the ANSI standard at som point.
Thanks @Alex for taking the liberty to explain the differences and similarities on different SQLs. Really informative. However, we don't choose what to use. It's the employer's choice.
Alex , although you are 100% right but in poor countries the options as long they are free they are welcome :D big fan of your SQL mentorship from Egypt ^_^
Tried using postgre and ran into problems with the group by clause. If I have * and an aggregate function in the select clause, I then have to state all the columns in the group by clause even if i'm only grouping by a single variable, let's say department. I do not encounter the same problem with MySQL which is so far my most preferred. Question, is it a must to mention all available columns in the group by clause even if grouping for only a single variable? Regardless of the SQL type?
you would have same problem with MsSql. grouping with * in select works rly in MySql afaik. in GROUP BY clause you put only columns you want to group the result by. Example (for MsSql): SELECT FirstName, LastName, COUNT(*) FROM Users GROUP BY FirstName, LastName; - counts all different occurences of first and last name in the table. Hope it helps
Quoted column names are also required in Oracle if the column names are mixed case. To avoid having to quote all required names in queries I always capitalise on import of data.
You probably should have differentiated better between the databases and the UIs. None of these databases are/have UIs; the UIs are separate third-party (except for Microsoft) applications. The pros and cons between databases are different than the pros and cons of the UIs.
The first DB I use was .dat file and then db from dbase and goes to ParadoxDB, then I move to Interbase/Firebird and MySQL and totally move to SQL Server. For small business dbase n paradoxdb is the best out there. For medium Interbase/Firebird is a good move.
Dear Alex, Thank you for your videos. Is there any way i could ask you a question personally? I really need to know what you think about that. Stay blessed.
From my irrelevant and useless point of view, one of the most important thing is how fast you can query your data, how fast you can import your data, what can you do with your data, and how hard is to use your data. Taking about a program for how it looked is a little bit useless. To me, a it doesn't matter how pretty is the user interface for your database, the real important thing is the power and robustness behind the engine that keep my data. Not all databases are suited for all projects. For example, a lightweigth service that requieres a db and is running in a battery powered device (your phone) can't use something like postgresql or mysql embebed in the device, you use sqlite for that. In the other hand, if your software runs in a huge server located in a well powered datacenter, and you must process 500K transactions per second, perhaps is not a good idea to use sqlite. Before I saw your video, I was expecting to see some metrics and plots when you compared the engines in terms of inserts per second, reads per second, etc. I don't want to be rude in any way with your opinion, I just want to go to a point: You didn't compared the DB engines, you just compared the user interface. All of them are Database servers that programs can connect and work with bypassing the UI and go straigth via a remote client connection to do the work. UI are for humans and are no longer requiered once you application is up and running. This is my opinion and in no way must be take away as the truth or the only way of thinking. I would have chamged this video name to: which DB has the best user interface. I'm not a fan of oracle database, but is one of the most used db server where the data must be secured. Why? I don't know. you forgot to metion that here. have a nice day!
Oracle SQL is still big and a lot of larger companies use it. It’s similar to SQL server in that it’s a commercial product, but it’s just not as popular as SQL Server.
@@AlexTheAnalyst it is popular for very large enterprises that can afford the license. As an OCP, in my opinion, it is the best in terms of recovery, speed, security, scalability and High Availability.
Hey Alex. Avid consumer of your content here! I am sorry Alex but I have to say something... you kept on referring to MySql, Postgresql, and Microsoft SQL Server Management Tools as SQL, or types of SQL. MySql, Postgresql, and Microsoft SQL Server are database management systems, NOT SQL. These 3 database management systems store data using the relational model, thus they are also referred to as relational database management systems. SQL is a query/programming language. It is the standard language for relational database management systems. Thus your SQL knowledge can be used/applied to any of these 3 relational database management systems. Peace! 🙂
MySQL is not free for certain commercial uses such as if you wanted to bundle it into your product. Postgres has a completely open license - you can do anything with it for free. MySQL can be extremely fast, but plays a bit faster and looser with the SQL standard and there are a number of important "gotchas" you have to watch out for.
If you start using special functions in any SQL it becomes difficult maintaining cross SQL platform functionality. One of the reasons that TSQL becomes a PAIN. For enterprise level functionality Postgresql is very nice. Replication and pooling its hard to beat. Although if as you say you are just learning.... Mysql is a very good option or another that you didnt mention SQLITE... Super simple to install and GREAT for small applications..
![ILLSLICK - WINTER IS COMING [Official Video]](http://i.ytimg.com/vi/dwTMeM1zIhQ/mqdefault.jpg)
Thank you for everyone's feedback on this video. The intention of this video was to compare the UI and management tools for each of these, but the title and thumbnail was not representative of that. I updated the title and thumbnail to reflect it better. I do plan on diving into the actual databases to look at things like scalability, speed, and more. Thank you again for your feedback!
Hi Alex, what about Oracle SQL?
I have used all these, MS SQL Server and PostgreSQL extensively for decades, nearly all in large system / large data applications. MySQL has been used for small systems. Also used Oracle quite a bit, especially in the 90s.
Review mostly focuses on the DB front end tool, rather than the DB capabilities, so a very misleading title / blurb to the video.
There are multiple DB user tools, other than the standard ones supplied by the DB, including ones that work across multiple database types.
The front end is a factor, but should NOT be the driving choice when looking at DB systems, unless you are a rank beginner, or have very simple DB needs.
This review is NOT highlighting how to actually select the right database, as behind the toolset they offer very different capabilities.
For example, Postgres has unparalleled support for time series, sharding and geospatial systems. PostGIS is the most powerful and widely used GIS DB extension / capability available on any DB type.
PostgreSQL is ultra-extensible. The extension system is designed in from inception, and in addition to sharding, time series, and so on, also provides multiple database programming languages. In addition to pgplsql (the equivalent of MS SQL TransactSQL), you can program in Python, C/C++, and many others via suitable extensions.
The list of extensions to support various capabilities likely runs in the 100s, and includes adding in different indexing and datatype options.
MS SQL: I used this extensively as well. The big stand out here for me is Transact SQL. If you are doing complex, large data set manipulations, or are really locking down the database against misuse via the use of triggers, rules and so on (can do the same in Postgres), this can be a life saver.
MySQL: their procedure / procedural support is very young, and nowhere near as robust as MS SQL or Postgres. MySSQL mostly came out of the embedded / small system / front end developer space, and has grown from there, where both MS SQL and Postgres have large system DB roots. (See ORM mention below)
So, I would say, if you are a beginner / front end developer, who is not going to focus on the DB capability, and has a fairly standard requirements list. look at MariaDB (open source MySQL), but you may want to consider PostgreSQL depending on your target application environment. MS SQL is great, but not open source.
ORM: The other use case for MySQL is if you are using the DB behind an ORM (object relational mapper), where the DB is just a data repository. Then it really doesn't make a huge difference which DB you select, as they are mostly black boxes. You should choose the DB that your ORM supports best, and is cheapest to own / operate. Obviously issues such as reliability and performance are important, and here Posgres and MS SQL have decades of experience to show.
In our case, PostgreSQL was the only choice. Our application is heavily GIS (Geographical Information System) and time series focused, where we have to analyse 100s of thousands of records for a single transaction to provide the functionality we need. We also use pgplsql functions heavily for update and extract transactions as we have multiple front ends for the application.
MS SQL was tested but the GIS component proved to just be too slow and not nearly as robust / functional as PostGIS. MySQL just didn't have the functionality to any useful degree.
We could have used Oracle, but the costs were prohibitive....
Well explained. You should do a TH-cam video on this
I would only add that the Enterprise features in MS SQL Server are pretty nice and easiest of the three to implement.
PostgreSQL is my top choice as well though.
Wow. You described use case for each DB provider so clearly. It seems like explaining things is your hidden talent.
It looks like he took your comment to heart because the video is now titled to specifically mention "Management Tools" as opposed to the databases themselves. I don't specifically recall the original title, but I have to assume you didn't write such a thoughtful reply and miss that. I remember thinking something similar when I first watched it, and that the video seems focused on a choice for someone first trying to learn SQL and data analysis rather than someone choosing a DB for serious application development, which is what I thought when I first clicked on it.
EDIT: I just noticed the author posted a comment saying he did change the title & thumbnail to reflect the focus on the tools.
I got attracted by the video title and find the takeaway in this comment. Salute.
There's very little comparison between the databases shown in this video. It would be better if you would show us how the databases excel one above the other in use cases. 80% of the video is comparison between MySQL Workbench vs SSMS vs PG Admin. These are actually tools to access these databases. You can access these databases with other tools like SQL Assistant. These are actually comparisons of tools not the actual database.
I agree. Half-way through the video I was wondering why he was explaining the interfaces so much. These interfaces are completely separate from the databases themselves. Absolute click-bait title.
Saw this after I posted my comment. Good to know I'm not the only one not happy about this video, ended up watching only because it kept appearing on my feed but I had a sense it's clickbait
I totally agree
I couldn’t have said it better myself.
Anyone seriously working with a relational database is interacting with it via some API such as JDBC (for Java) or something similar. The GUI tools are helpful when composing queries but don’t generally come into play once your application is deployed, at least not for the end users.
More important are things like how the database handles locking and concurrency, rollback, backup, restore, replication.
MySQL was bought out by Oracle, but there is an open source fork called MariaDB which was not mentioned.
Amazon offers a commercially supported fork of PostgreSQL which is available in the cloud.
I haven’t used SQLServer in about 20 years, but I wasn’t clear what extra functionality was being referred to that was so great in T-SQL. Is it the client software or the programming language built on top of the DB. If the latter, PostgreSQL offers Pl/PgSQL which is very similar to Oracle’s PL/SQL. The biggest difference there is that PG/PlSQL does not support the package notion ore some of the more sophisticated data structures that PL/SQL offers.
No mention was made of the data types supported or the different types of indexes, how partitioning is supported, what kinds of triggers are available or the differences in how they work. PostgreSQL offers some interesting data types including ranges and coordinates for geospatial data.
The issue about the quotes in PostgreSQL appears to be peculiar to the client tool. As I understand the SQL standard, and rom my experience with both Oracle and PostgreSQL, if you use double quotes around an identifier (say a table or column name), to refer to that object, you must use the exact case. If you define it without the quotes, it is matched case insensitively, although Oracle, for example internally upper cases such identifiers while PostgreSQL lower cases them.
right. and no mention of DBeaver which is an excellent gui for postgres
PostgreSQL does have a SQL extension it's called pg/plsql, it's very powerful, in fact, PostgreSQL is much more advance than MySQL only drawback is administration it's not as easy as MySQL and MSSQL
The quotation marks in PostgreSQL are only needed for establishing names to columns which could collide with system key words - for example a column called "name". And using the Schema name before the table is only needed when you have duplicate table names. So in both cases it's not a "weakness" compared to other databases, but simple features that can be used given a necessary scenario. And in terms of interface, all these applications run on the actual database, having multiple options for each engine, so again, it's hard to say the least which is the "better option", when it's a matter of personal preference.
You can also connect to most databases from each others interfaces. And I know I've seen other databases where you can put multi-word field names in quotes.
My bet is the mixed case of the column names is causing the quotes in the generated select statement for the example in the video. All my table/column names are lowercase, and I don't get quotes in the generated selects.
@@SP30305ATL - Exactly, if all lower case or all upper case - no quotes are required. SQL Server does not care but it’s predecessor Sybase prefers all lower case definitions. Most designers of data tables capitalise first letters of multiple words in column names but without word separator. A bit of extra work to copy such data to databases that object to such format without quotes.
@@peterc5512 Oh, has it been your experience that most db designers you've encountered use mixed case column names w/o separators? I used to only see all-caps with underscores working with DB2 and Oracle in the 90s, and since the 2000s I've mostly seen all lowercase with underscores in Informix and PostgreSQL in the US. I'm curious if it mostly varies based on the database (or original db of the designer) or the dominant programming language? In Java, the predominant ORM by default converts Java standard camel case attributes like "productName" to lc/underscore like "product_name", which tells me that is a commonly used standard. I'd be curious what db/programming language/part of the world you find "ProductName" style most common? Perhaps it's a Microsoft standard? No big deal--I'm just curious.
@@SP30305ATL I cannot claim to have broad industry-wide experience except in few PACS systems running Sybase, SQL Server and our own state wide repository under Oracle 12C. When migrating data from source systems the Toad Data Point tool does not offer the same automated convenience you describe so I manually redefine column names on the target table to capitals with underscore separators. The newer versions of Oracle accept longer column names, luckily. However if I miss the definition step it still works, but all queries need quotes around them… I commented initially because I agree the mixed case was triggering it.
Having to play with the 3. Nowadays I liking Postgress better. It was much better support to JSON and it provides a plethora of built-in functions for time series and others
I learned SQL on Postgres and importing/exporting was frustrating if you got an error.
I really enjoyed seeing the comparison of these platforms. Good stuff!
MySQL is great but the best one is always gunna be the one your company uses... so Microsoft SQL server it is! 😂
True!
haha Exactly.
Nearly all the companies I’m interested in applying for use SQL Server, so I guess on the bright side, that’s made it a lot easier for me to decide which RDBMS to learn. lol
Correction: The one you tolerate is the one you have to work with. Like family you may not like.
Open source: You are not mentioning MariaDB, which is the open source fork of MySQL, and is becoming the default available in most Linux repositories, rather than MySQL which isn't true open source.
PostgreSQL is usually pronounced "post gress" and if you included the QL, you say " Q L" at the end, but since Postgres can also be a NoSQL DB (key pairs, JSON document storage, etc.), most people who use it regularly call it just Postgres..
This is one thing that I know Postgres excels at. It's Json datatype performance and the rich functions around it makes it an excellent NoSQL db. I've read an article a few years ago that compares the performance of pg json system with MongoDB and it outperformed MongoDB, not sure if that still valid today. However, what's sure is that the Types in PG are richer than that of MySQL.
I hate dealing with bad data shoved into JSON from front-end devs. FML.
@@ChronicSurfer You mean inconsistent data types and structure? Wide use of MongoDB has only encouraged that and perpetuates the inability to follow a data structure..
Document store DBs have their place and are wonderful when used correctly...but using a document store DB to avoid data structure is just asking for trouble at scale.
My understanding, as a developer, is that as a data analyst you probably shouldn't really care about differences between different RDBMSs except query language and I hoped to see why I might be wrong in this video. But no, you just confusing RDBMS (Relational Database Management System such as MySQL, SQL Server or Postgres) and its client application (MySql Workbench, SSMS, PGAdmin4 respectively). So the only difference you mentioned between those RDBMSs is their query language. All other differences are really just some UI elements of their default clients.
Anyone watching this, imo, should understand that distinction. You are not bound to single client app. There are many other clients, some of them even can work with several RDBMSs. And some of them are even can affect the query language! SSMS, for example, lets you write GO in the query editor as a delimiter between queries, but there is no GO statement in T-SQL, it's SSMS specific (their official command line client supports that too, I believe) delimiter, which makes your queries run as separate batches instead of one per editor tab.
Some other clients give you more convenient features - Forge Studio for MySQL has quite good graphical query constructor. HeidiSQL is just very lightweight, can work with mysql, postgres and sql server, and probably something else, and it has freaking search bar in object explorer pane that even supports some regex magic (looking at you, SSMS, why with all Microsoft's resources there is no f-ing search bar right on top of object explorer??? Why search is buried in context menu and doesn't support regular expressions???). DBeaver is quite powerful and works with several different RDBMSs... That's just some clients that I remember and use or have used in past, there are surely way more of them.
In fact, any application that connects to a database is a client for that database, just not every app lets you write queries yourself and shows results as boring tables. Some let you instead, I don't know, watch a video on your screen and read some comments below, and even write one yourself :)
As a begginner, which one shoud l focus more or start with for thos two main sql MySql or Postgre?
Well, you should compare the SQL language dialect differences, because some of the DB servers have the built-in programming language 'extensions' and they are really quite different. But the SQL language is quite standardized (by ISO , IEEE standard or something) and the difference between the SQL dialects is mostly the servers' built-in functions, like string or data values processing and some special language extensions which are using the specific features of each DB-server. For example, the MS SQL server is using the T-SQL (Transact-SQL) programming language extension, but the PostgreSQL is using the PL/pgSQL (Procedural Language/PostGres Structured Query Language) and the Oracle DB-servers are using the PL/SQL (Programming Language/SQL) extension. The PL-extensions are used for the server-based business logic programming, mostly, for writing stored procedures, user functions, triggers, packages, etc. So, in my opinion, you should compare the SQL language dialect differences, not the PL-extensions and not the IDEs (which is not a property of the SQL language itself).
Not to sound harsh so I hope that you take this criticism to heart, but this video was more a comparison between SQL Clients (MySQL workbench, SSMS, PGAdmin) and not so much between the actual SQL languages. And not to mention that when you reached the PGAdmin part where you touched upon the dashboard tab, it became clear to me that you aren't actually that familiar with them. MySQL Workbench literally has something similar where you can see the server status (slow queries, queries per second, etc) which makes me think that you don't even know Workbench that well. And I am 100% sure that SSMS would have something similar as well. Also, there is a reason why postgres is a favourite amongst especially startup companies. Maybe do a bit more research on the topic next time. Again, I apologize if sound a bit too harsh. Just trying to share some feedback.
Useful video, but the audio sounds like it was recorded off Single Side Band. Excessive compression?
Sorry, but those are the user interfaces that comes by default with the DBMs. Not a comparison by itself, which should start by how well they implement the relational algebra, and then how they implement the the desired properties about transactions, concurrency, indexing, consistency, security, permissions granularity, logging, recovery, escalation, mantainance, troubleshooting support, isolation levels and scopes, hinting and how much you'd rely on them, and so forth.
I've used SQL Server at extremely large scale (taking in 1TB of data per day) and MySQL at very large scale. You get what you pay for. The performance, tools, configurability, SQL features, stability, etc are vastly better with SQL Server. It's not even close. I've only used Postgres in small instances but I would choose it over MySQL.
Agreed. The database administrator and reasonable leadership need to accept the best tool for the job. MySQL is great for regional small & mid-size business. Not for mega cap corporations.
Thank you Alex for keeping it direct and easy to understand. I worked for more than 20 years as a software developer since Turbo Pascal, Delphi, Visual Basic, Java, SQL, and after 8 years I'm getting updated to come back programming. Thank you for these videos.
OpenSource does NOT mean it is free, just that you can see the code but might not be allowed to use it/modify it/execute it. This is a common and important misunderstanding.
I really don't know who the audience for this video is. The content in this is not what I expected, you should've included *for beginners* . You also should've mentioned that renting a server you'll have to choose Linux or windows and that to run MS SQL you'll have to rent a Wondows server which is more costly than a Linux server. Also writing APIs to access and consume the data plays a big part in which DB is best given your requirements and skillset. Sure same points are beyond the scope of a short video but the title is then misleading.
Anyway my backend starter pack recommendation would be:
- learn php
- use mySql
Easy to learn, setup, cheap and fast to implement and make changes.
MySQL for small to mid-size business with a regional focus.
Postgress for National and International focus. Best for this type of relational large scale database.
MS SQL for a National and International focus, plus the MS discount for mixing it with Azure and Office 365. Best for those all into using Microsoft only.
Great video Alex, what is missing in free version of sql server? Just wanted to know
Overall I think this video is good exposure for people who are unfamiliar with database options, but its lacking on accuracy in some ways. For example you mention T-SQL and that its only available for SQL Server and while this is true, you failed to mention that MySQL and Postgres also have something similar. T-SQL is just the scripting language, in MySQL its just regular ANSI sql, but in Postgres theres PG/SQL which is heavily influenced by Oracles PL/SQL. Also you mention not being able to show T-SQL which was again not particularly accurate. The easiest way to do that would have been to show that you can declare variables and use them in queries. Again the same applies to Postgres.
the other thing that I found could have been a bit better is how you mentioned the sys objects in SQL server. There are equivalents in both MySQL and Postgres as well.
To me it seemed that you were not necessarily comparing databases, but rather comparing the administrative and development tools for each database type. I've worked with all three tools, and a few others and I find that MySQL workbench is the jankiest, but then again its an Oracle product so its to be expected.
got my first job as a DA! the work is incredibly hard though. first five tickets and it feels levels above me for entry level DA work. hope i can pick up their material and not be fired
Dreyson, this is my fear! I have done the Google Data Analytics course but don't feel I know enough yet. I'm trying to fill in with some practice on my own. May I ask what skills you knew going into your job and what skills your employer was looking for? I hope it gets easier for you!
@@AlisaHof i only use SQL at my job but i knew tableau and excel pretty well and started to touch on R but yeah it’s just not the type of problems i was expecting to work. SQL tickets more about databases and servers vs like figuring out how many ppl make more than X or whatever. it’s my second week and they don’t expect me to be productive until like 3 months in but i still feel insecure on job security until i feel more competent
@@singleplystink3426 That would be very much over my head (also right now everything feels over my head). I've noticed that there are many job listings for data analysts/ business analysts, etc with descriptions that don't seem to be related to anything I've learned so far. Have you been able to find any resources to help you learn about these topics? Do you think your manager could help- or is that too awkward to ask? Have you been on any of the reddit data analytics-related boards? You might get some good feedback there. Anyway, I hope things get smoother soon !
@@AlisaHof i sprinkle in some questions but she seems annoyed almost any time that i do. i’m not sure if you can prepare for it since it’s just such a giant volume of data which muddies the water. i feel like i’m picking up on it slowly but surely, wish i could a little quicker but i’m basically on my own for 95% of the time. my regular manager is helpful but can’t really help with the meat and potatoes of my technical questions. it’s a lot but they pay me well for it lol it’s gonna be alright
@@singleplystink3426
You gat this Drey! 💪
I have 15 years experience with SQL and I think you explain it like there are big differences between them. You can very easily switch between them depending on the project you are working on. Unless you are the db Admin 🤣
Oh I absolutely agree - I use both SQL Server and MySQL at work all the time. I think everyone should learn multiple types.
15 year experience now how much salary
We prefer mysql been used since mysql 3.0 . If something wrong , we can restore . Sql awesome on store proc . Postgrs rare use so as oracle.
It depends on how you are using them. If you're using an orm abstraction and basic tables, doesn't matter much.
Geospatial, time series, and JSON data features it becomes massive. PostgreSQL is so much better as a developer than the other two.
In MySQL/Maria, utf8 isn't and binary fields are indexed as strings by default. Magic ticks are treated like single quotes and a huge number of other inconsistencies and annoyances.
Not all switches are created equal! I grew my SQL skills under Sybase - going to MS SQL Server was easy enough, but Oracle took a bit of a learning curve due to many minor differences on gets to use in other environments. For example - try your sample of “select top 10 * from table” in Oracle and it spits the dummy, until you change it to “select * from table where rownum < 11, as well as variable assignments are very different.
Also, just one more thing in today's NoSQL database trend: Postgres has really strong support for JSON / JSONB storage types, including indexiing.
Use this quite extensively in my applications.
It's not _just_ a vanilla SQL database, which is where MySQL probably scores well.
MS SQL historically has also been SQL+ due to the emphasis historically on TransactSQL
I use Jsonb heavily, I like the mix of noSQL and SQL. Postgres wins by a mile
Not to mention GIS support
This is really helpful for me as I am studying and trying to get in the analytics field, knowing which to focus on is really useful. Thanks Alex.
I used SQL Server extensively from about 1997 to 2003. One thing I recall is they had was ability to view the entity relationship diagram which is important for design. Does MySQL server client have this as well? I really want to get into SQL Development again, on my mac.
Did you just base your decision for which rdbms to get based on the frontend they ship? Pretty bad way of choosing a database system.
Postgres delivers a level of features and performance that far outranks both MySQL and MSSQL. It comes out of the box with a bunch of features and optimizations such as native json support, gis support and pg/psql. I agree MySQL is a good "beginner friendly" DB, but for serious work I only typically use two databases; PostgreSQL (stand alone deployment) and SQLite (embedded).
Oracle if it’s one of those massive applications
I prefer SQLite to use as data mart.
I have Microsoft SQL server and on pasting the code to create tables i get this error -A table can only have one timestamp column. Because table 'encounters' already has one, the column 'STOP' cannot be added. how to resolve this.
SQL Server has advantage in diagram view, you can easily design the database at SSMS.
I'd be interested to know a feature Postgres lacks, which SQL Server has... Postgres is what I suggest people replace Oracle with. MySQL is what I suggest people who want to use just SQL use, and I don't recommend anyone use SQL-Server
Converted an app from SqlServer to Postgres. One huge issue is that Postgres does not allow temporary variables (you'd have to use a function), while TSQL does. There's a way to do that sort of functionality in Postgres outside of a function, but it's not intuitive, and it's barely written up on the internet, so it took a while to get an optimal solution. That said, I do like Postgres a bit more - seems a bit more "pure".
Postgres also seems to have a lot of datatypes. This might be relevant for some, though it's probably not an issue for the vast majority of apps.
And yes, Postgres is free and SqlServer licensing is very complex and can get expensive (in the lower tier, you need server licenses as well as client licenses, and the client licenses are per device).
Will focus on Posgres vs SQL Server in a quick post.Postrgres peformance is awful for complex sql with many joins, plus security is bad in most scenarios when you compare with SQL Server. This is from my experience with large tables. Things that take minutes and hours in postgres will take seconds in SQL Server. That's a fact. Also the default Postgres sql tool is not up to standard when you compare it to SSMS. Also if you are trying to locate a performance issue i have so many optons and tools in SQL Server but it's all very basic in postgres.
So, if I want to become a Data Analyst will MS SQL be good to start with ?
Standard SQL is called “ANSI” Standard (DB2). PostgreSQL follows the “PL/pgSQL” SQL Dialect (similar to Oracles flavor - but Im reading DB2 now supports PL/1 too). Transact-SQL was created by a company called “Sybase.” Microsoft as is their pattern “partnered’ with Sybase and basically took their tech to make MSSQL-Server. To day MSSQL-Server is my favorite flavor of DB - but I’ve been in Oracle/PostgreSQL recently.
Excellent comparison video - thank you!!! I used MongoDB (NoSQL) for a project because I wanted to be able to store ragged data formats for program preferences that wouldn't form neatly into a row/column table. I have since migrated to PostgreSQL since it has support for JSON and JSONB types which are even "indexable". Another big reason I went to Mongo was its ability to index spatial data (maps). Given Postgres's tight integration with mapping using PostGIS, it makes it a no-brainer for any map based database usage. BTW, I can't stand PG Admin, either when it was a compiled Windows/Mac program or now as a web based solution. It's slow and painful to use. I found a great multi-use SQL client, DBeaver, which in the free/community version supports many SQL versions and is lightning fast! Note: I have no relationship whatsoever to DBeaver - just found it, starting using it and became a huge fan!
Great video! Thanks! Well edited, you showed the interface and usage, the main differences, everything quickly and clearly, without wasting our time, and even gave an honest answer about which one you think is better instead of just saying "it depends"! Congratulations +1 subscribe
Great Video! using MySQL just because of macOS
Hey Alex, can you make a video on importing and exporting data in each of these SQL?
but only postgresql is 100% for free even for commercial use, right? and it provides geo spatial functions and it has much easier syntax for uuid and json object selector than mysql.
For common usage I agree. If you want most reasonable default setting on pages with low traffic MySQL will work fine without tweaking. BUT if you want o scale your project UP, Postgres on Linux OS is better because everything in it is configurable.
Note: never trust anyone who tells you Master: Master geolocation separated replica works, and it is not advantage of MySQL, just a trap. Lost a lot of production data this way.
Thanks for the video. I use SSMS with TSQL for work, but want to create something myself and don't want to pay. Although there are free options with Microsoft, I would like to not worry about scaling up. Think I might test out MySQL.
You are comparing database system that are different.
The syntax is part of the tool but never should be the first choice when it comes to decide which database system we are about to choose.
For example, mysql is normally not durable (unless they changed it); it means that you might loose some information in case of crash. So if you own a company that needs to be sure that no data is lost in case of crash and you choose mysql because it's easier to code/understand, you might get a lot of trouble when you server will crash and part of your data is gone.
When it comes to maintenance, there is always a trade off between a system that has great support and a system that has less (paid vs free).
Same comes with community. The bigger the community, the less pain you will have when looking for someone having a solution to your problems... These are important criteria too.
For the rest, every database system has most in common in the language.
Did you clarify that this is your recommendation for which one to learn on, not which is the better one to use in a company?
I actually learned Oracle first - although I really started learning SQL in MS Access, which does let you edit the sql for your queries. I built datamarts in Oracle though
But in the past 2 years, I've worked in SQL Server, Oracle, and Snowflake - sure I have to google what this DBMS uses for date functions or various other functions, but thats not a big deal. Its kinda like switching between MS word and Google Docs, or MS excel and Google Sheets . . . basically doing the same thing in a different interface with a few minor differences in how to do the same task
oh - you can use optimizers in other ones too that show you how expensive your query is. you just dont know it.
SPAM . . . not sure how to report, but since he's imitating you?
You're a very effective communicator. Love your videos! Thanks, Alex.
As a note, T-SQL was invented by SYBASE, not MS
Both SQL Server and PostgreSQL are fully transactionals, but MySQL IS NOT
The worst in MySql is the "Backup System", just a bunch of SQL Commands, not a real Backup.
I like MySql, it's simple and cost nothing, it's also quite fast. There are diferent tools for different needs.
For Enterprise Solutions, SQL Server, it has a HUGE amount of enterprise Features (Audit, policyes, different levels of encryption and security options)!!!
In a second place PostgreSQL, which is related with SQL Server...
SQL Server (based on Sybase 4.2 perhaps 1992)
Indeed Sybase invented TSQL, but only us silverback geeks remember. It was started as a codevelopment project with Sybase, MS, and Ashton Tate, who was the owner of the leading PC based database server at the time. It was actually to write a sql server for OS/2. This lasted for about six years or so till MS decided to go it alone since they’d sucked up as much knowledge as Sybase had to offer and they were in a position to use their knowledge of the server OS. It had always been a sideline project for Sybase who was really a Unix house. But both continued to release Windows server version. Sybase was better for a couple releases but eventually MS threw enough resources at it and turned out a better product.
are you aware, that you're comparing not the databases themselves, but merely just some selected (by you) GUI tools (and there are much more gui tools out there to compare) to interact with the databases?
Thank you so much, Alex. I have a question. I uploaded a data containing more than 1000 rows. I want to see all rows, but it always return 135 rows. How can it happen?
Hi, mysql es down in the principal page. Any other page to download MySQL?
This is me right here... my college DB course textbook was in Oracle, the class project was in MySQL, and now my internship is SQL Server
Super Informative video. Please make videos on how to write complex SQL queries. Thank you
Alex, before finding you out i learned MySQL and would like to do your portfolio projects in MySQL, can you please guide me a bit there?
Thank you Alex. You have helped me all the way through college, and now with my first interview in the real world. I appreciate all the help
That's awesome! Good luck with your interviews!! :D
Can you do a video about data modeling and what a newcomer to data analysis should know about it, if anything?
I was just waiting you making a video about that! Thanks!
In terms of queries, Is it same for pgsql and MS server or else.
You seem to be relying on the frontend dashboard to evaluate these DB systems. For example, if u you use datagrip as your dashboard for Postgres your experience and evaluation would be different, ie better. Also, and more importantly, there are reasons to choose one of these DBs over the others based on concerns that have nothing to do with the frontend ui.
Great video! Thought I'd share my thoughts here on using MS SQL Sever on Mac, you probably might have heard this before -- it is possible to do so, using applications like Azure Data Studio or VS Code directly run on Mac, rather than on a VM.
I personally like working in Postgres among the 3 :)
Are there major differences in the syntax of each of these?
Ironically enough the MySQL in Steps book is the main one I am currently learning. It hasn't had any steps on learning how to import databases in the book. But that is ok. though. I do find it easier to learn. But I am going to go through the SQL Server one to understand the fundamentals too.
Hi great content!!! Question...I've been learnig from SQLite and now I want to learn MySQL but just upload a dataset is hell! it so SLOW!!! does are another way to practice in MYSQL or may be to upload data in a way that it does not too sloooooow?! your chanel is awesome!
Talking about importing the files into server SQL server is the best we don't need to take care about the data it takes all the data but and no need to convert to csv if it's an excel file.
comming to postgresql it's a pain we need to input all column names even after inputing all the columns there is no gaurantee that the file will import without errors we need to take care about the formatting of the csv such a large porcess
And comming to MySQL the import is some what easy but when exporting a large data set it sucks and takes more time even after waiting for the import for me sometimes it doesn't import any data these were faced 8n MySQL workbench,for MySQL we can use dbeaver which is better than MySQL workbench and import time a lot faster
It depends on the project and how scalable the project will be in the nearest future.
My biggest issue with postgre is importing data and the quotation marks.
I had to create the table Schema with exactly same column before it will work.
in my opinion as PostgreSQL user, Microsoft SqlServer in another level With t-Sql which enable you to create awesome functions run on server , Which Done by programming languages in another database engines , MySQL is so popular because of powerful but because of easy reach and host , but in SqlServer send some variable by the name of stored procedures and let hem do the validation entries calculation and return you confirm or he rollback all changes as bulk of entries done by one procedures
You can write server side functions in Postgresql as well, and you have severa programming languages to chose from,. E.g, PL/pgsql, PL/pythonn, PL/tcl, and java, javascrippt, R, maintained seperate from postgresql
dang it.. mysql workbench doesn't run on ARM (mac mini m1).
I just love comparison videos. Thank you Mr. Alex.
Yes, he should do Power BI VS QLIK VS TABLEAU next!
Thanks Alex Bot
Can someone pls guide me, which database is preferable for core banking domain between mssql and postgresql?
apart from Oracle.
postgresql
MySQL does implicit commit on DDL commands when run in transaction. Which ruins the purpose of using a transaction.
I'm a MS-SQL guy but have a decent amount of experience with MySQL as well. I've always thought Management Studio was pretty awful when it came to general features from a UI perspective, when compared to something like dbForge Studio for MySQL. I WISH Microsoft would release comparable features (like having a table filter that supported multiple filters or even being able to colorize which DB you're looking at - small things). Ah well... some day, some day...
Very useful video.. Thank you Alex. This is exactly what I was looking for!!!!
Been making a good living with T-SQL. I like MySQL because it's easy to pick up start working with any data or to use on your own projects.
Hello i found your channel yesterday & watched some of you videos they are really great !!! good job on !!!! all the information very helpful i just have one question based on your experience & this video seems to me that the way to go to learn SQL would be MYSQL i saw that you you have some good video's about Microsoft SQL tutorial. so my question is where could be a place to learn specifically MySQL ?? i looked for courseara but could not find some options for Mysql i found a lot for IBM SQL but not Mysql so any place you can recommend me to look for Mysql learning ? im very interested in Data analytics skills because that is the way im moving my professional grow. so thanks a lot & im watching today your tutorials for Tableau because I'm interested as well to learn this tool so thank you for your time & dedication for these videos that help a lot.
Do people compare all these with MS Access?
I've been using Postgres for almost a year and I never used quotation marks to SELECT. To be honest I didn't even know this was a thing
Double-quotes for quoted identifiers identifiers are part of the ANSI SQL standard. Sybase SQL Server used single-quotes and double-quotes interchangeably to delimit strings. Microsoft inherited that behavior. But, they each changed their syntax to conform with the ANSI standard at som point.
As a data analyst you don't need to write sql. Just connect to your data source with Power BI and use query editor. It's like a gui for sql.
How about if you have to analyze data from a web-based environment?
Thanks @Alex for taking the liberty to explain the differences and similarities on different SQLs. Really informative. However, we don't choose what to use. It's the employer's choice.
Thanks Alex! I used MySQL in my earlier years and totally agree with your points here :)
Alex , although you are 100% right but in poor countries the options as long they are free they are welcome :D big fan of your SQL mentorship from Egypt ^_^
Can't you just run SQL server through Docker on a Mac?
I'm getting post installation failed for database cluster while installing postgresql.
Can anybody help please?
Alex, Sybase invented T-SQL. Sybase ASE and Microsoft SQL Server uses T-SQL.
Tried using postgre and ran into problems with the group by clause. If I have * and an aggregate function in the select clause, I then have to state all the columns in the group by clause even if i'm only grouping by a single variable, let's say department. I do not encounter the same problem with MySQL which is so far my most preferred. Question, is it a must to mention all available columns in the group by clause even if grouping for only a single variable? Regardless of the SQL type?
you would have same problem with MsSql. grouping with * in select works rly in MySql afaik. in GROUP BY clause you put only columns you want to group the result by. Example (for MsSql): SELECT FirstName, LastName, COUNT(*) FROM Users GROUP BY FirstName, LastName; - counts all different occurences of first and last name in the table. Hope it helps
Quoted column names are also required in Oracle if the column names are mixed case. To avoid having to quote all required names in queries I always capitalise on import of data.
You probably should have differentiated better between the databases and the UIs. None of these databases are/have UIs; the UIs are separate third-party (except for Microsoft) applications. The pros and cons between databases are different than the pros and cons of the UIs.
The first DB I use was .dat file and then db from dbase and goes to ParadoxDB, then I move to Interbase/Firebird and MySQL and totally move to SQL Server. For small business dbase n paradoxdb is the best out there. For medium Interbase/Firebird is a good move.
Dear Alex,
Thank you for your videos. Is there any way i could ask you a question personally? I really need to know what you think about that. Stay blessed.
I don't think the words: "SQL" and "Database" mean what you think they mean...
What is the role of sql in data analysis?
From my irrelevant and useless point of view, one of the most important thing is how fast you can query your data, how fast you can import your data, what can you do with your data, and how hard is to use your data.
Taking about a program for how it looked is a little bit useless. To me, a it doesn't matter how pretty is the user interface for your database, the real important thing is the power and robustness behind the engine that keep my data.
Not all databases are suited for all projects. For example, a lightweigth service that requieres a db and is running in a battery powered device (your phone) can't use something like postgresql or mysql embebed in the device, you use sqlite for that. In the other hand, if your software runs in a huge server located in a well powered datacenter, and you must process 500K transactions per second, perhaps is not a good idea to use sqlite.
Before I saw your video, I was expecting to see some metrics and plots when you compared the engines in terms of inserts per second, reads per second, etc.
I don't want to be rude in any way with your opinion, I just want to go to a point: You didn't compared the DB engines, you just compared the user interface. All of them are Database servers that programs can connect and work with bypassing the UI and go straigth via a remote client connection to do the work. UI are for humans and are no longer requiered once you application is up and running.
This is my opinion and in no way must be take away as the truth or the only way of thinking. I would have chamged this video name to: which DB has the best user interface.
I'm not a fan of oracle database, but is one of the most used db server where the data must be secured. Why? I don't know. you forgot to metion that here.
have a nice day!
Hey Alex what about oracle SQL. I have about 200 questions on how to query Oracle database using SQL in a Windows operating system?
Oracle SQL is still big and a lot of larger companies use it. It’s similar to SQL server in that it’s a commercial product, but it’s just not as popular as SQL Server.
@@AlexTheAnalyst it is popular for very large enterprises that can afford the license. As an OCP, in my opinion, it is the best in terms of recovery, speed, security, scalability and High Availability.
Thanks for your honest review Alex...
Awesome! I was really looking for clear clarification like yours!
Hey Alex. Avid consumer of your content here!
I am sorry Alex but I have to say something... you kept on referring to MySql, Postgresql, and Microsoft SQL Server Management Tools as SQL, or types of SQL.
MySql, Postgresql, and Microsoft SQL Server are database management systems, NOT SQL. These 3 database management systems store data using the relational model, thus they are also referred to as relational database management systems.
SQL is a query/programming language. It is the standard language for relational database management systems. Thus your SQL knowledge can be used/applied to any of these 3 relational database management systems.
Peace! 🙂
MySQL is not free for certain commercial uses such as if you wanted to bundle it into your product. Postgres has a completely open license - you can do anything with it for free.
MySQL can be extremely fast, but plays a bit faster and looser with the SQL standard and there are a number of important "gotchas" you have to watch out for.
Mysql is fast for simple select '' from table queries but not so fast if you have more complex queries with lots of joins and subqueries,
Do we need machine learning to be a data analyst??
No
If you start using special functions in any SQL it becomes difficult maintaining cross SQL platform functionality. One of the reasons that TSQL becomes a PAIN.
For enterprise level functionality Postgresql is very nice. Replication and pooling its hard to beat. Although if as you say you are just learning.... Mysql is a very good option or another that you didnt mention SQLITE... Super simple to install and GREAT for small applications..
it is more like of overview of mysql clients with does not make much sense in terms of db differences
@AlexTheAnalyst Is there a version for mac users ?