8 ms·
Why you should learn SQL
- simonw 5y agoOf all of the skills I started to pick up twenty years ago, SQL and Bash are the two that have continued to serve me well for my entire career.
- Akronymus 5y agoAnd probably learning where and how to find information you need.
- throwaddzuzxd 5y agoAgree. Regex literacy is a big skill to have too.
- tanin 5y agoI've built a desktop app that enables you to load CSV files and perform SQL on the CSVs. I imagine it would be handy for anyone who starts learning SQL because you don't need to setup the database or use command-line. If you are interested, please try it out: https://superintendent.app https://superintendent.app
- rmnclmnt 5y agoSo it’s a visual wrapper around SQLite?
- tanin 5y agoYes! with few additionalfunctionality added (e.g. regex, parse_date, tsv). Initially, I wanted it for myself because I used Excel a lot but I never loaded CSVs into a database (dunno why). Using SQL is much more convenient than Excel's formula.
- Pamar 5y agoIt's nice. Windows only? (I mostly use a Mac at home). EDIT: no, it's Win/Mac/Linux, apologies...
- zeta0134 5y agoI use SQL, or something like it, on pretty much any project that needs to store any significant amount of data. Easily the most bang for my buck out of any of the skills I picked up in high school. Even if it's not SQL proper, you should learn a query language of some sort and get used to the idea of asking an engine to help you sort and filter your data in a consistent way. Once you have that mental model down, you'll find you can migrate between the SQL-likes and the SQL-not-so-likes comfortably, and pick the best tool for the job at hand.
- asutekku 5y agoEven as a PM, SQL is one of my most valuable skills. I can handle trivial tasks myself without bothering data analyst and speed up the processes by a lot
- eurasiantiger 5y agoSQL is going to eventually die because of its three-pronged relationship model that implies query-time complexity. Learn it if you want, but especially backend and analytics people would be better off learning Gremlin or Cypher. Edit: downvoting this is not going to make it untrue.
- Pamar 5y agoFor everyone (like, for example, me) who has never heard these two names before (or not as replacements for SQL at least...) maybe the following might be useful: https://stackoverflow.com/questions/13824962/neo4j-cypher-vs-gremlin-query-language https://stackoverflow.com/questions/13824962/neo4j-cypher-vs...
- holoduke 5y agoYou are getting doenvoted because it's untrue. So many faang companies using traditional SQL. And for good reasons. Not to say your two examples are bad. They have their own benefit. But don't become the guy who believes in one replacement solution for all without the knowledge of all. It's annoying
- eurasiantiger 5y agoAnd how many FAANG companies are using a graph database?
- helsinkiandrew 5y agoThat is a truism everything is eventually going to die - but SQL has been around for longer than just about all development technologies today and probably will be around a lot longer than things that are popular now. I would guess that it will be around when people have move on from React, Gremlin, and Cypher.
- mr_toad 5y agoFor simple queries, row adjacency will beat the graph hands down. And RSBMSs are hard to beat for transactional processing.
- Hypergraphe 5y agoThe point of this article is more on the importance of data integrity in databases than on SQL itself.
- abhinav22 5y agoSQL is an important skill to have, in particular one should learn about database normalization and the normal forms - very important stuff!
- eurasiantiger 5y agoAll of it is completely irrelevant unnecessary complexity. Learn to use Tinkerpop.
- bottled_poe 5y agoCurious that 99.99% of applications use an SQL database, must just be a lot of really bad developers. Am I so out of touch? No, no, it’s the children who are wrong.
- eurasiantiger 5y agoYou can patronize me all you want, it does not rectify the fact that you are out of touch with modern database technologies and prefer what you know works.
- Akronymus 5y agoAnd even if you don't want to normalize, at least don't make the DB basically incomprehensible. Speaking from experience, a "abnormalized" db sucks. https://news.ycombinator.com/item?id=27842820 https://news.ycombinator.com/item?id=27842820
- abhinav22 5y agoYeah one should know why it’s important to normalize, and then when they go a different path, it is done consciously and not because they are inexperienced and didn’t really know any better. Folks out there who don’t want to learn the ins and outs of database theory - please don’t; read up on it, it’s a very important skill & knowledge to have and doesn’t take long to master. It does take a few days, perhaps weeks, for all the ideas to “click” in your head. Whilst we having many fads in IT, there are certain sound principles of computer science developed over 40-50 years which one should learn :-) Know the rules before you break them!
- holoduke 5y agoI am using spring hibernate. It's so powerful. I don't need to write and understand SQL anymore. Sarcasm off: I see more and more juniors without SQL skills. Various reasons they give me. From the nosql plague to some cloud solutions to the above example. SQL seems to be not so fun anymore. I have a hard time understanding that. Since data storage is such a fundamental part of your application. I would even argue that the data structure to some degree influences the application architecture. Specifically if you need performance and security.
- laurent92 5y agoSQL is useful but we’ve spent decades to abstract it away, because the Java/SQL articulation is always circumvoluted, and juniors do without. Then they load two tables in Java and join them manually in memory; or they fetch the principal record, and use the getter to retrieve the dependency, so Hibernate does one query per row. I accuse all ORMs, especially Hibernate, of making it too easy to skip SQL, and I accuse Java of not supporting multiline strings. We have failed, in most languages, at making it easy enough to write SQL yourself. JPA is nice to cache records in memory, but it’s been way more harmful to performance that it became hard to write SQL directly.
- pjmlp 5y agoAnyone that does work on the client that should stay on the server, sending wasted data across the wire has already lost it. To this day I keep writing stored procedures, no need to multiline strings. And for the rest just use either myBatis or jOOP, run away from Hibernate.
- elmo2you 5y agoYou are right, regarding the wastefulness. Additionally, there are extra risks involved when data needs to be transferred between a storage and compute facility. To a degree I get why architects these days like to separate everything into individual narrow and easier to manage/tuned services. But separating business logic from the data it runs on may equally be a fundamental mistake, when it comes to guarding integrity of both data and the logic acting on it. There are good argument for advocating that actually ALL business logic should be implemented alongside the data it runs on, inside the database itself. Only the logic specific for presentation should exist separately, in whatever front-end is used. I've seen that done successfully, several times in fact. It does require some programming skills which I've not seen in most programmers these days though. Even less among the young dogs. Maybe the key question here is: does that say something about proper software design / architecture, or about the state of what programmers these days actually know about the fundamentals. I believe it's easy to hate on something like SQL from a position of ignorance (to be clear: SQL does have serious shortcomings, without a doubt). Still, in nearly 30 years I've not seen anything come along that is fundamentally better (and gaining enough traction to succeed). As for those who like to complain that stored procedures are too limited because you would have to implement everything in SQL (I've heard that one a few times too many), plenty of databases have extension to program stored procedures in different languages (e.g. python).
- devnull3 5y agoAt times it might make sense not to use SQL. That is fine. But I think there is a value in learning SQL. Some reasons: * Learning effort to Reward ratio is good. A substantial syntax is valid for all of the DBs that support SQL. * Very easy to remember the syntax. I have not used SQL in a long time but I can still write one without much effort (including the nested queries) * Pareto: About 70-80% of data retrieval use-cases are well covered in SQL syntax (or a combination of multiple queries like union, creating temp table, etc) * Tooling: Free & commercial tools which connect to multiple data-sources and provide SQL e.g [1] [1] https://www.jetbrains.com/datagrip/ https://www.jetbrains.com/datagrip/ (I am not associated with jetbrains)
- scns 5y agoOr Idea Ultimate which contains Datagrip's functionality and the other languages they made IDEs for, except CLion (C/++, Rust debugger) and Rider (C/F#). Android Studio can connect to the Apps SQLite db. There is a plugin for Vim to connect to DBs, Written by Tim Pope. Just a content user. (edit) Links for the curious: https://github.com/tpope/vim-dadbod https://github.com/tpope/vim-dadbod There are a UI and completions for it too: https://github.com/kristijanhusak/vim-dadbod-ui https://github.com/kristijanhusak/vim-dadbod-ui https://github.com/kristijanhusak/vim-dadbod-completion https://github.com/kristijanhusak/vim-dadbod-completion
- drloser 5y agoselect * from argument a where a.subject like '%sql%'
- KingOfCoders 5y agoWe're currently moving our Druid/Kafka/Spark setup to BigQuery because of how simple SQL is.
- hashhar 5y agoYou might find https://trino.io/ https://trino.io/ interesting. It allows you to bolt on a MPP SQL execution engine on top of any data source including pre-built connectors for Druid and Kafka. It's all ANSI SQL and the best part is you can combine data from heterogenous sources. e.g. You can join data between a topic in Kafka and a table in Druid or even between Kafka, S3 and your RDBMS. Disclaimer: I'm a maintainer of the project.
- dominotw 5y agoMy employer is going to the other way from snowflake -> druid to reduce high response latencies/ improve freshness. I am assuming your use cases can tolerate increased latency?
- KingOfCoders 5y agoYes they can, we will move low latency to a smaller, different pipeline.
- lordnacho 5y agoYou should learn SQL because it's likely to be a very different language to the ones you already know. In SQL you don't tell the DB how to get the data (for loops and pointers) but what the result should look like, and the DB figures out an execution plan. This style is well worth practising and appreciating.
- SilverRed 5y ago> and the DB figures out an execution plan. And then you spend the next hour trying to decipher the plan and work out how to get it to do something that isn't insanely slow.
- nesarkvechnep 5y agoMaybe create foreign keys and indexes in your Rails migrations
- SilverRed 5y agoI had a crazy case where after adding indexes to things, queries suddenly become much much slower. I had to look over the plan and realized that removing these indexes made queries fast again. Turns out you can't just take for granted that an index will speed things up and its something you actually have to test and record before/after results. I'm still slightly at a loss as to what the issue was but the best I can find is that if you index something with lots of duplicate values, you make the query slower as it has to scan the index and then scan the long list of results.
- scns 5y agoThank you for sharing, something to remember, a valuable lesson.
- polynox 5y agoIndices work best when the number of matching entries is low. There is a metric called index selectivity which is the number of distinct values of the indexed columns divided by the total number of records. For a boolean value, this would be 2/N records, effectively the worst possible index. For a perfect index, it would be 1 (every row has a unique entry in the index). It could be possible for the query planner to get the answer wrong for which index to use if it happens to be wrong about the selectivity of your particular query, because the selectivity must be approximated. See for example PostgreSQL's (a fantastic database) documentation on these approximations and imagine a number of ways it could fail [1]. > Assuming a linear distribution of values inside each bucket, [...] > This amounts to assuming that the fraction of the column that is not any of the MCVs is evenly distributed among all the other distinct values. > Using some rather cheesy assumptions about the frequency of different characters [1] https://www.postgresql.org/docs/current/row-estimation-examples.html https://www.postgresql.org/docs/current/row-estimation-examp...
- cratermoon 5y agoI've spent most of my career hoping that something - anything that's better than SQL will come along and replace it. It's like FORTRAN, except FORTRAN has had the decency to stay in use where it's really the best choice. But SQL is out there, like Clippy. "Hey, I see you're collecting some data. SELECT TRUE FROM HELP WHERE COLLECTING_DATA IS TRUE
- urthor 5y agoNobody else has invented any other declarative language it seems. Come on aspiring post-docs, do your thing and launch a new programming language. If MIT can launch Julia someone can launch a language that's not SQL.
- pjmlp 5y agoLike Kusto? https://docs.microsoft.com/en-us/azure/data-explorer/kusto/concepts/ https://docs.microsoft.com/en-us/azure/data-explorer/kusto/c... https://docs.microsoft.com/en-us/azure/data-explorer/ https://docs.microsoft.com/en-us/azure/data-explorer/
- urthor 5y agoHmm. It's read only, but holy shit please become popular and displace non-ANSI SQL in my life.
- BiteCode_dev 5y agoEdgeql is trying.
- masklinn 5y ago> Nobody else has invented any other declarative language it seems. Postgres was originally built on one: https://en.m.wikipedia.org/wiki/QUEL_query_languages https://en.m.wikipedia.org/wiki/QUEL_query_languages There’s also D/D4/Dataphor, though i’m less convinced. Unsurprisingly that mostly died out after sql took over everything.
- goto11 5y ago
- helsinkiandrew 5y ago> The SQL langauge is old, strange, and important. I've always found it strange that something hasn't replace SQL, particularly for traditional 'web' client/server applications. But SQL is probably the only technology/language that has survived in the past 30 years of my career. 99% of their SQL databases probably don't require transactions and ACID behaviour. There nearly always needs to be a layer to convert the rows into a format for use (blobs of JSON for a front end etc). Complex queries are hard enough to be used as standard interview questions. I guess the out of the box performance, availability and reliability of SQL databases, that 0.1% of times when you need ACID writes and transactionality, and the (just about) interoperable SQL makes it an easy choice - no one was fired for choosing SQL.
- gopalv 5y ago> 99% of their SQL databases probably don't require transactions and ACID behaviour 99% of applications do not require multi-statement transactions or rollbacks, but basic ACID is hard to live without (at least the 'D' and 'C' parts). You probably don't need a massively parallel transaction system, perhaps you just need to isolate writers out but the fact is that most of what you need comes for "free" with a standard SQL database + failovers. It's not that no one was fired for picking it, but that people can be hired to fix it when it doesn't quite work as well as it should.
- kumarvvr 5y ago> 99% of their SQL databases probably don't require transactions and ACID behavior. Until they do. Developer mind share for the data storage aspect of their applications is much simplified due to ACID. Just look at the documentation of NoSQL databases. A lot of code is for integrity checks and verification.
- jeltz 5y agoOver 90% of the projects I have been involved in have required ACID. In fact I can't think of any that used a database and did not require ACID so I think your 99% should be inverted: 99% of all databases require ACID. This is evident from how even MonogoDB is ACID now with their new storage engine.
- 5y ago
- boiler_up800 5y agoWorking on a new SQL client https://arctype.com https://arctype.com based on this very thesis! SQL tooling is rough tho. My first intro was pgadmin and MySQL workbench which I feel serves to turn a lot of beginners off. If you have to get Python 3 packaging right to even start writing queries.. it’s just a very tough journey. My other gripe is about ‘intermediate SQL’ - CTEs, window functions, stored procedures. Intentional practice was needed for me to be able to use those! Unlike Python where you pretty much become a functional programmer without realizing it over time.
- robertwt7 5y agoIt is indeed very helpful, actually my first class in CS colleague was SQL in oracle. I think that's one of the class that is most valuable to me without my realising it. Until now I still remember most of the language because I used it every day, it has similar syntax and understanding even when I convert to Mysql or Postgres. Without realising it is one of the most important fundamentals that is nailed in your head, suddenly you just understand when you look at raw SQL from your ORM to debug, what went wrong etc.
- NorwegianDude 5y agoSQL is constantly getting new features. The newest is SQL:2016. So while it's old, there is a lot of newer things too.
- ideamotor 5y agoI just keep getting faster at SQL. It's fun. I used CROSS LATERAL JOIN last night to find the nearest regions to points. Super fast and easy: https://carto.com/blog/lateral-joins/ https://carto.com/blog/lateral-joins/. Right on the data. SQL does change over time, as Postgres adds features, but especially when you use MADlib, Postgis, and other extensions. I mean ... https://postgis.net/docs/reference.html https://postgis.net/docs/reference.html. And everything I learned when I started is still relevant. It's my go-to crunch tool for data processing.
- ideamotor 5y agoI just want to throw out there, one of the weaknesses I've seen is the lack of fast options for pivoting (wide <-> long). I've used the tablefunc module: https://www.postgresql.org/docs/13/tablefunc.html https://www.postgresql.org/docs/13/tablefunc.html but it's not particularly fast in my experience (maybe they've improved it since it has been awhile) and the syntax is a bit odd. I'm spoiled with R (newer version) tidyr's pivot_wider and pivot_longer. Gosh, yes it's been awhile. I posted this, ahem, over 8 years ago: https://stackoverflow.com/questions/15415446/pivot-on-multiple-columns-using-tablefunc https://stackoverflow.com/questions/15415446/pivot-on-multip.... Guess what, while to many things change, I still use SQL. I should, uh, try it again. Or try PL/R, PL/Python, or PL/pgsql. There are options.
- frogcoder 5y agoI used to love ORM, I used it everywhere. Writing another language in the language I am coding is wrong. ORM simplifies my programs. No, ORM does not simplify coding! It's a big complex adapter which does not fit many cases. RDBM itself is complex enough, let's put another complex abstraction above it so we can forget about the tables and columns and joins and foreign keys. Complexity added upon another complexity does not make a polished interface. If you want to use ORM, you have to learn both SQL and ORM with some depth at least. When problems occur, you have to debug both. 20 years in, I am still learning about RDBM. I still hate to compose SQL in code, but there is no better way. Databases are used in every situation. Want to store data? Database. Want to communicate between processes? Database. Want to store logs? Database. There are different solutions for different scenarios, but as long as the DB chugs along, why? Sometimes it's sickening. So 20 years in, the ROI of learning SQL is great. Please don't avoid learning about RDBM by using an ORM. Just learn it. Actually, I'm not without ORM now a days. Light-weight ORM like Dapper serves well, it does not abstract out the concepts of RDBM, but make things easier.
- BulgarianIdiot 5y ago> Writing another language in the language I am coding is wrong That's not what's wrong. But rather writing in some language within a string literal is what feels wrong to you. With better integration it'd be natural. The problem is, of course, there's no single SQL dialect.
- takeda 5y agoIf you use IntelliJ IDE in their commercial version (at least of PyCharm) they integrated DataGrip. Basically if you connect to your database and give an option to fetch your schema the IDE starts scanning for strings and if it detects SQL it provides IDE features to it as well (like auto completion, and some refactoring etc). I think ORM and query builders were trying to hack around to make IDEs understand SQL, when in reality this approach is what actually was needed.
- BiteCode_dev 5y agoNo, ORM provides an inspectable central entry point for your models with a standardized API. That's the feature. That's why you get a great django ecosystem: the ORM abstractions allow all libs to rely on the fact the rest of the code access the model the same way.
- axiosgunnar 5y agoThis article is an ad for an online course.
- aslkdjflkajsdf 5y agoI'd like to see SQL as a first class citizen in a static typed language with tuples & record polymorphism. disclaimer: I hate ORMs. Something of interest: https://smlsharp.github.io/en/ https://smlsharp.github.io/en/
- rawoke083600 5y agoWow I've been digging into the ML languages these last few months. First time I heard of SMLSharp. Looks very good :)
- throwaway81523 5y agoYou might look at the now defunct ur/web if you haven't. An old snapshot of the web page: https://web.archive.org/web/20170429201116/http://impredicative.com/ur/ https://web.archive.org/web/20170429201116/http://impredicat...
- Digit-Al 5y agoIt's a real testament to Chamberlin and Boyce that no one has been able to come up with anything better in ~45 years.
- xupybd 5y agoAbstracting the persistence layer is important, but also unique to your application. ORMs come with some nice features however relying on a full ORM feature set can lead to leaky abstraction. Not to mention you have to learn another DSL to use most ORMs. Why not just use SQL. A nice custom wrapper around binding query parameters is all I need most of the time. If you use an Onion or Clean Architecture you'll push the SQL into its own layer and you don't need an ORM.
- mikewarot 5y agoIf you learn SQL, please take the time to learn how to pass parameters without putting them in the query. Example in python: query = """Update employee set Salary = %s where id = %s""" tuple1 = (8000, 5) cursor.execute(query, tuple1) It is very important that the parameters of a query never become part of the command string, or a whole class of injection attacks become possible. At this point, you could still run into problems if your parameters and command string aren't in sync, so watch out for that as well. (Usually this would happen later as the code was modified for new requirements)
- ChrisArchitect 5y ago(2019)
- cultofmetatron 5y agoya'll need to try elixir's sql system Ecto. It doesn't try to wrap sql in objects. instead it exposes a dsl that lets your write sql in elixir and get elixir structures. The end result is that the impedance mismatch is minimal https://www.irrationalpixels.com/posts/database-modeling-with-ecto-part-1/ https://www.irrationalpixels.com/posts/database-modeling-wit...