
SQL is 43 years old – Here’s why we still use it today - d4nt
http://blog.sqlizer.io/posts/sql-43/
======
cyberferret
Been programming for 30+ years, and 99% of my projects use SQL databases. I've
tried and dropped NoSQL many times. I still wake up in a cold sweat thinking
about the earliest version of Firebase where I had a project that tried to
join three tables together to get some meaningful data.

I still remember the response of a Firebase team member to my forum question
about it - "These days, storage and computing power is cheap - just duplicate
the child table as nodes in the main table and do it all that way for every
parent/child relationship you have. Don't worry that you have to duplicate the
same set of data for every parent that is related to the same child...That's
how NoSQL works..." <shudder>

Even though I use ORMs in my project these days, every time I have to test a
complex query, I write it in raw SQL first and check it before trying to make
the ORM duplicate the same query.

Granted, NoSQL has its place and its advantages, but for me, when it comes to
"money code", I will stick to SQL.

~~~
matwood
I feel like this is something I have to keep repeating, "the data always
outlives the app code." If app code is required to make sense of the data, you
are going to have problems. Of course NoSQL has a place, but only in very
certain cases should it be your primary datastore. SQL (and the RDBMSs that
leverage it) are built to store, protect, and provide structure to the data.

The second annoyance I have is the push for schemaless. Schemaless does not
exist. There is always a schema, except in a schemaless data store, the schema
has been moved to app code and then my earlier comment applies.

One thing I do agree with is that most ORMs are not very good. The best ones I
have used are very thin layers over the sql, like jOOQ. I think the lack of
understanding of SQL and bad ORM experiences (Hibernate WTF) is what led
people to think SQL/RDMBs were the problem when in fact they were not.

~~~
fnord123
>The second annoyance I have is the push for schemaless. Schemaless does not
exist. There is always a schema, except in a schemaless data store, the schema
has been moved to app code and then my earlier comment applies.

This is a great point. It's like types for programming languages. They always
exist; it's just a matter of whether you have the compiler managing it or
whether you need to keep it in your head when you're hacking.

> I think the lack of understanding of SQL and bad ORM experiences (Hibernate
> WTF) is what led people to think SQL/RDMBs were the problem when in fact
> they were not.

Again, like types in programming languages, I think the ability to iterate
quickly without having to predict how the data will be used in the future
leads to 'schemaless' approaches which can be kicked out the door more quickly
(maybe) than ponderous schema laden tables.

~~~
mindcrime
_I think the lack of understanding of SQL and bad ORM experiences (Hibernate
WTF)_

I don't understand this comment. I've never had a bad experience with
Hibernate. If anything, Hibernate has saved me tons of time over the years.

~~~
matwood
I'd be curious about the size of the systems you have used with Hibernate. Do
you know off the top of your head what will cause Hibernate to issue a flush?
How about the order of operations in a batch? I've read the entire Hibernate
book multiple times while trying to squeeze out speed, and finally decided
enough.

The problem with Hibernate is I have to learn all of these Hibernate quirks to
maybe get decent speed out of a system, when I could have just written SQL and
been done. This isn't even including debugging when Hibernate decides to issue
N+1s.

~~~
mindcrime
_I 'd be curious about the size of the systems you have used with Hibernate._

I wouldn't even know what metric to use to quantify that. Number of tables? On
the order of hundreds, probably low hundreds... definitely not thousands.

It may just be that I've been lucky in that the data models I've worked with
have been things that map well to using Hibernate. I will say that I haven't
worked with a lot of data models where you need more than 3 or 4 joins in any
given query. I know some people write far more complex queries than that, and
maybe those are the kinds of scenarios that Hibernate doesn't handle well?

 _The problem with Hibernate is I have to learn all of these Hibernate quirks
to maybe get decent speed out of a system, when I could have just written SQL
and been done_

Do you hand-roll your own caching mechanism when just writing plain SQL?

~~~
matwood
> Do you hand-roll your own caching mechanism when just writing plain SQL?

Using something like jOOQ has meant I rarely need any caching, unlike
Hibernate where caching almost seems required.

~~~
mindcrime
Interesting. I've never used jOOQ, but I'll give it a look. Hibernate has
worked well for me, but I'm not dogmatic about it. Always interested to try
new tools.

~~~
matwood
I've been writing SQL for almost 20 years, so the pain for me is the grunt
mapping of data to and from. jOOQ does much of the grunt work of mapping, and
lets me write typed SQL that it smartly translates to queries. More advanced
features like various types of fetch make my job even easier.

[https://www.jooq.org/doc/3.9/manual-single-page/#arrays-
maps...](https://www.jooq.org/doc/3.9/manual-single-page/#arrays-maps-and-
lists)

------
ransom1538
SQL has the great side effect of creating structures that future employees can
understand. Its a set of tables with relationships. Given these you can
quickly inspect the structures _independent_ of the programming language
dejour. With a few commands a new employee can understand: business logic, hr,
billing, reporting, and other major backbone principals in a matter of hours.
On the other hand, I have been at companies that jumped on the noSQL / ORM /
wrap it until you can't wrap it / hide sql (rails). When new employees show
up... well... its a bunch of semi/no structured stuff spread across thousands
of lines of specific logic.

~~~
forgotpwtomain
Rails tried to hide SQL so much that one of the consequences was not
supporting proper table level constraints (FK) till AR ~4.2 or so. Just take a
look at the DB of a company using Rails in production: hundreds of keys to
non-existent relations.

~~~
jgeraert
Seeing this as well in proprietary software sold by one of the biggest erp
software vendors... And it's still not fixed and likely won't be fixed anytime
soon.

~~~
moron4hire
There is a perception amongst lower programmers that FKs are only a thing you
enable in production, that they are an impediment to rapid iteration.

I mean, it's completely wrong, I know that. But try being the new guy telling
a team of 10 people that.

There is also a perverse corruption of "if it ain't broke, don't fix it" that
goes on. If you can spend 100 hours manually validating every relationship in
your application code, that's 100 hours you can put into your estimate and you
know you can complete. If you only have a cursory understanding of SQL, then
"learn more about FKs and implement them across the DB" seems like a big,
unknowable blob of time that is impossible to estimate. It doesn't matter that
it might only be 5 hours of work, at least we can be certain about 100 hours
and bill the client for it.

Finally, there are a small set of things that are fundamentally wrong about
all modern RDBMS implementations. For example: it's nonsensical to have a
foreign key that isn't indexed. You always want an index on foreign keys,
there is never a scenario where you don't want them. But while primary keys
are indexed by default, foreign keys are not. And those sorts of things give
the anti-RDBMS crowd enough of a foot-hold to argue for continued ignorance.

~~~
aidos
Curious, this doesn't fit my mental model: _it 's nonsensical to have a
foreign key that isn't indexed_

I guess you normally want indexes on parent - child tree relationships (book
-> chapter), but you don't want them all the time. What about when you have an
'article' with a 'status and a 'category' and you only ever find all articles
by combination of category and status? In that case you'd be maintaining 3
indexes, category, status and status_category, but the only one you'd need
would be status_category.

Indexes are a tool to to allow for optimising lookups while foreign keys are a
tool to allow you to keep your data consistent.

~~~
moron4hire
I don't see how your example doesn't require an index, specifically because,
as you said, indexes are a tool to allow for optimizing lookups.

I imagine that maybe you're suggesting you first query the DB for the ID of
the combined status_category, and then query the article by that ID. That's
not a good idea, for a couple of reasons. First, you're making two round trips
to the DB when you could, with no additional effort (just effort in a
different place) be doing one. Second, you've introduced a data race
condition. If someone deletes that status_category after you've queried for it
but before you've queried the articles, you aren't going to get the results
you want.

It would be better to do a join across articles to status_category to status
and category, then query based on the status and category values you want.
Without an index on the FKs between status_category and status and category, a
relatively small table can have a big impact on query performance.

Finally, while I know your example is arbitrary, it's a little hard to argue
against a design that is probably wrong. I doubt the suggested schema for
articles and categories is a good one. If I argue "you should never have to
arbitrarily subtract '1' from a result just to get the results you want", it
would not be a good counterargument to say, "yes, but sometimes you want to
add 2 and 2 and get 5, so then you need to subtract 1". The problem isn't
where you see it.

FKs aren't just a consistency tool. Consistency and referential integrity are
features that results from having an FK, but the FK is a signal that data can
be searched in a certain way.

~~~
dragonwriter
> I don't see how your example doesn't require an index, specifically because,
> as you said, indexes are a tool to allow for optimizing lookups.

If you never lookup by a column alone, you don't need a single-column index on
that column. An FK need not ever be a lookup target (the target column[s] it
references are necessarily a lookup target, but not necessarily vice versa.)

An FK is probably usually going to want some kind of index, but it's not
nonsensical to have a non-indexed FK.

------
danso
I've already posted on HN how I use SQL in my public affairs data journalism
class [0]. To me, there is no better, in terms of accessibility and return on
investment gateway language to the power of computation and programming than
SQL, with the exception of spreadsheets and formulas. Even if you don't go
further into programming, SQL provides the best way for describing what we
always need to do with data for journalistic purposes -- joining records,
filtering, sorting, and aggregating. Ironically, I learned SQL late in my
programming career, and initially thought its declarative paradigm to be
mysterious and inferior to procedural languages. In fact, I don't know how to
do anything in SQL beyond declarative SELECT queries (and a handful of
database create/update admin queries). Turns out this is just powerful enough
for me for most app dev work (Rails, Django), and the simplicity is a boon for
non-programmers.

ProPublica just published a bunch of data-related jobs and positions. The
phrase "Proficiency in SQL is a must" makes an appearance:
[https://www.propublica.org/atpropublica/item/propublica-
is-h...](https://www.propublica.org/atpropublica/item/propublica-is-hiring-a-
data-fellow-2017)

[0]
[https://news.ycombinator.com/item?id=8505000](https://news.ycombinator.com/item?id=8505000)
[https://news.ycombinator.com/item?id=10585009](https://news.ycombinator.com/item?id=10585009)

~~~
wodenokoto
I've recently started learning Pandas (dataframe library for python) and i
find most queries cumbersome compared to the absolute minimal SQL I know

~~~
lowmagnet
Pandas is mostly columnar in nature. If you're heavy into Python idioms it's
fairly easy to grok, imo, but there is a subtle learning curve about what is
in and out of kernel. Since in-kernel operations are about 50-100x faster,
it's obvious when you did the wrong thing, but it doesn't show until data sets
are huge.

I tried doing something regarding browser hits from Akamai data in Pandas and
3 different SQL databases (mysql, postgres, sqlite) and nothing came close to
pandas for holding 150m hits (one day's worth across our properties) in memory
as well as Pandas. Especially with Dask Dataframes mixed in. No competition
for the effort involved.

------
konradb
Can anyone shed light on why there has been a phenomenon of people finding SQL
'too complex' and moving to noSQL? (Not sure if that's entirely fair but, from
the outside, it is what it looks like). Is it hype driven? Are courses at
university not tending to cover SQL that much?

~~~
charles-salvia
The main reason is the object-relational impedance mismatch[1]. Basically,
programmers like working with objects that have data fields. This is because
most modern, widely-used programming languages treat objects/aggregates with
data fields as a first class concept. But SQL isn't designed around objects
with fields, it's designed around tables, rows, and result sets from queries.
Therefore, working with SQL in most modern programming languages generally
requires layers of annoying result-set->object or object->row
plumbing/conversion code. (Not to mention the vagaries of type conversions.)
Of course, these days, this problem can be substantially mitigated to a
certain extent by clever ORMs, but an ORM is generally a leaky abstraction at
best. Obviously, whether or not any of this bothers you will depend on your
use cases and a lot of other factors.

[1] [https://en.wikipedia.org/wiki/Object-
relational_impedance_mi...](https://en.wikipedia.org/wiki/Object-
relational_impedance_mismatch)

~~~
MarHoff
_But SQL isn 't designed around objects with fields, it's designed around
tables and rows._

I think a more correct analogy would be that table are like classes, columns
are the properties, and rows are instances. And so defining foreign keys is
like setting a pointer to a parent instance.

There is not direct analogy for methods, but you can use function/trigger to
do the same job.

PostgreSQL is actually an object-oriented RDMBS, it's not because you are
meant to manipulate these objects through SQL that they are less powerful. And
SQL is actually Turing Complete with PostgreSQL.

It's clearly not convenient for general programming, but as soon as data
manipulation is involved you benefit from a lot of built-in optimization.

~~~
judah
This breaks down when you deal with classic object oriented concepts like
inheritance, encapsulation, or polymorphism.

This is why object/relational mapping (ORM) has been called the Vietnam of
Computer Science[0].

[0]: [http://blogs.tedneward.com/post/the-vietnam-of-computer-
scie...](http://blogs.tedneward.com/post/the-vietnam-of-computer-science/)

~~~
MarHoff
Sorry if I was unclear, I'm not advocating for ORM.

And yes it breaks down because it is similar but different. Row "instances"
can perfectly exists outside a table and even be defined outside any table via
composites types or even on the fly.

Just saying a RDMS can be a powerful object-oriented environment using solely
SQL and no ORM at all.

------
jackfoxy
There just is no substitute for SQL. Some thoughts on what has given it a bad
name:

1) The pervasive use of artificial keys. USE NATURAL KEYS. Unfortunately
probably 99% of real-world databases were designed with artificial keys. I
wish I could point to some literature on this topic. It is very rare and I
only came to learn about this from a DBA who is well-versed in designing with
natural keys. I'm trying to get him to publish more on this topic.

2) ORMs. This is just a bad practice. Their use in part derives from the awful
schemas designed with artificial keys, requiring another layer of complexity
to get a more intuitive model of the data. Fortunately for me over the past 3
years I've been doing almost all my application I/O with F#'s SQL Type
Provider, SqlClient,
[http://fsprojects.github.io/FSharp.Data.SqlClient/](http://fsprojects.github.io/FSharp.Data.SqlClient/)
, which strongly types native query results, functions, and SPROCS. Just does
not work if you need to construct dynamic SQL. I've been trying to goad the
author into also providing meta data retrieval. That would be the icing on the
cake.

3) SQL does not seem to be a required topic for undergrads. There are really
no unsolved problems (of note), so it's not interesting to academics.

4) Most app programmers don't get much practice writing difficult queries or
tuning problem queries, so that one time every 9 months when you do something
hard, it is hard. (And again, often compounded by the complexity introduced by
artificial keys.)

~~~
pjungwir
> There are really no unsolved problems (of note), so it's not interesting to
> academics.

For anyone who likes SQL and is looking for a research topic, read about
temporal databases and suggest some ways to handle DDL changes over time (e.g.
adding a new NOT NULL column, or changing a relationship from one-to-many to
many-to-many).

Here is your starting bibliography:

Richard Snodgrass, _Developing Time-Oriented Database Applications in SQL_.

Hugh Darwen & C.J. Date, "An Overview and Analysis of Proposals Based on the
TSQL2 Approach".

Krishna Kulkarni & Jan-Eike Michels, "Temporal features in SQL:2011".

Tom Johnston, _Bitemporal Data_

Magnus Hagander, "A TARDIS for Your ORM":
[https://www.youtube.com/watch?v=TRgni5q0YM8](https://www.youtube.com/watch?v=TRgni5q0YM8)

I would love to read what you come up with!

~~~
nickpeterson
I would argue that
[[http://www.AnchorModeling.com](http://www.AnchorModeling.com)] is a pretty
good take on it. I think it shares some genealogy with Fact-Based Modeling
techniques (FCO-IM?). It has a really decent story for evolving the schema
over time.

I've read Darwen/Date, Snodgrass, and Johnston's (two books) on the subject.
Johnston's seems the best practical choice, but they patented the ideas :/
That said, one could probably implement Johnston's model without violating
patents if you ignore explicitly modeling the episode structure and just
follow the theory.

Date and Darwen make decent points about wanting to use the relational model
rather than some baked-in concept, but ultimately they do almost no legwork on
practically putting any of their notions to use. This seems to be par for the
course on Date books.

Weis and Johnston handle the problem more directly. They also tackle a harder
problem overall (BiTemporal, vs just Temporal). Also Johnston is just easier
to read than Date. (Side note, I feel like a blog where I just read chapters
of Date writings and condense the content basic points would end in most of
Date's arguments fitting in 50 pages.

That said, Weis and Johnston still punted on schema evolution. Anchor Modeling
sort of starts at supporting an evolving schema and moves outward from there.
In Anchor modeling there isn't the traditional Temporal/Bi-temporal notion,
but rather positors than have varying degrees of certainty about posits. There
is basically a concurrence of facts that can be retracted or changed over
time. Useful for modeling varying degrees of certainty or alternate
perspectives on data.

The downside of Anchor Modeling is that the datamodel is basically 6th normal
form with a bunch of table valued functions, triggers, and views to aid in
making it palatable for devs. Johnston sort of acknowledges this style in one
of his books but I believe argues against the concept of an entities
information being spread throughout various tables. In his mind, a table is a
type, that is made of attributes (columns). Anchor modeling is more along the
lines of 'Attributes are types', and you can relate them to form larger types.

I wish there was more work on the ORM side supporting some of these concepts
(Schema evolution and temporality).

------
skc
I've always thought the main reason NoSQL solutions became popular is that
developers could finally get at and manipulate the data the way they wanted
to.

I've known some very, very prickly DBA's in my time who referred to the
databases they looked after as "My database". So they would say things like
"Don't put junk in my database"

And would give you endless grief over how you wrote your queries or asked you
a million questions about why you needed a new table and why your proposed
design was shit.

As a result, many of us devs tended to view SQL Databases as some sort of dark
art. So in this regard, NoSQL is freeing at first glance.

But if I'm honest, once I got over my fear, the "pros" of NoSQL solutions in
comparison to good ol' SQL seem to be relatively feeble.

I think it's easier to get up and running with a NoSQL solution because there
is far less friction when it comes to rapid prototyping of ideas, but things
get complex pretty quickly.

I'd also say that for the vast majority of applications out there, the
difference between the two will mostly be a wash.

~~~
hackits
By any chance did these DBA's be the Linux server administrators at all?

My own personal experience is DBA's/Linux server administrators have authority
issues. I had one situation where I had a encrypted file on one of the Linux
server and the server administrator requested for months to have the
decryption key so he could inspect the file.

~~~
duozerk
That seems highly unprofessional. I won't even ls into users' directories
without their express permission.

~~~
hackits
.... yup, also him going in randomly through the day and killing proc's id's
and clearing out log files at random was other issues where reported. I think
for development to avoid him and get some level of sanity we used AWS and
install ubuntu/postgres/java sdk to get work done.

------
agentultra
I've been programming for most of my life. SQL has been a big part of my
career. And I love it. It's one of my top 5 languages.

It's a nice, functional, declarative language in the vein of prolog and such.
You just tell it the shape of the data you want, where to materialize it from,
filter, aggregate, calculate the window of, etc... and the system figures out
how to execute it as efficiently as it can. It beats out procedurally munging
data by a long shot. It's more concise for many operations than ML-like
variants.

It's a great tool to have. And understanding the underlying maths, relational
algebra, is beautiful. I've found trying to implement your own rick-shod
relational database is a good way to try to mechanically understand the
theory. Then move on to implementing datalog... etc. The reason why SQL
continues to stick around is that the fundamental theories are quite sharp!
I'd appreciate a more concise syntax some days but overall I can't say I'm
displeased. It's great!

------
cousin_it
One of my dreams is building a hybrid of RDBMS and Protocol Buffers. It would
look like a bunch of nested structs that can be kept in memory, fetched from
disk or over the network. The schema would be kept in .proto files, and you'd
be able to reload it at runtime (existing data would be handled according to
protobuf schema evolution, because each field in each struct is numbered and
old numbers aren't reused). Most joins would be gone because nested structs
are allowed, but you'd still have an SQL-like language for more general
queries (designing such a language when nesting is allowed is a bit tricky,
but not very). Things like indices and transactions would also exist at every
level (memory, disk, network) because they are useful at every level.

The end goal is eliminating impedance mismatch, because your current in-memory
state, your RPCs and your big database would be described by the same schema
language, somewhat strongly typed but still allowing for evolution. I have no
idea if something like that already exists, though.

~~~
weavie
The problem is that what joins to what differs depending on the context for
what you are looking for.

Sometimes you join an order to supplier, other times it will be to customer
and other times it will be to a list of products. Sometimes, all of them need
to be included.

With nested structs the parent/child relationship is fixed. If your query
needs to invert this relationship, you essentially have to search through your
entire database which will be incredibly slow and resource intensive.

Either that, or you just store your data and their relationships separately
and then allow for highly optimised searches to be conducted between these
relationships according to the query and statistics you hold about the data
... but then you have just reimplemented an RDBMS..

~~~
cousin_it
I guess the idea is to make the schema as readable as possible using nesting,
and then use indices for the rest.

------
dghf
> SQL and relational database management systems or RDBMS were invented
> simultaneously by Edgar F. Codd in the early 1970s.

Codd didn't invent SQL. Donald Chamberlin and Raymond Boyce did.

> SQL is originally based on relational algebra and tuple relational calculus

Maybe "originally based on", but not "an implementation of". For example, it
is perfectly possible for an SQL query to return duplicate rows, which isn't
possible under relational algebra/calculus, a relation being by definition a
set of rows (or, more precisely, of tuples).

------
baldfat
SQL is the second most underused tool we have with dealing with data. AWK
being the first. SQL is great because the logic works with dealing with data
and forces you to make good decisions earlier.

Everyone should spend a day learning SQL if for no other reason they the
ability to think logically about data.

~~~
majewsky
awk is really nice. I'm starting to use it more and more in places where I
previously used a long pipe of grep, cut and sed.

~~~
GuB-42
I use perl when grep/cut/sed show their limits. I never really got into awk.

I suppose we all have our favorite tools.

~~~
majewsky
I'm a heavy Perl user, too, but some stuff that is very succinct in awk is a
bit more awkward in Perl. Most prominently when you're using $1, $2, etc.
which are just there in awk but need a split/\s+/ in Perl.

~~~
skinniks
-a is your friend.

------
zephyrfalcon
"Why do we still use SQL" and "Why do we still use relational databases" are
two very different questions. They seem like much the same thing, because SQL
is pretty much the only query language offered by relational database systems
nowadays... so if you use SQL, you use an RDBMS, and vice versa. But other
query languages used to exist. There was QUEL [1], for example. It seems to
have fallen by the wayside; most people have probably never heard of it. I
guess there is very little room for multiple languages in this particular
space.

[1]
[https://en.wikipedia.org/wiki/QUEL_query_languages](https://en.wikipedia.org/wiki/QUEL_query_languages)

~~~
default-kramer
Absolutely. Relational databases are so useful that I happily use SQL even
though it is not a very good language. I have thought about making a "compile-
to-SQL" language in the same vein as Typescript. (Does this already exist? You
would think so, but I can't find one.)

HTSQL has some great examples of where SQL could be better:
[http://htsql.org/doc/overview.html#why-not-
sql](http://htsql.org/doc/overview.html#why-not-sql). I would love to get that
goodness in a language not tied to the rest of HTSQL.

~~~
Zak
There have been a few languages that compile to SQL. CLSQL for Common Lisp and
Korma for Clojure come to mind.

------
jaked89
"It’s like how MailChimp has become synonymous with sending email newsletters.
If you want to work with data you use RDBMS and SQL. In fact, there usually
needs to be a good reason not to use them. Just like there needs to be a good
reason not to use MailChimp for sending emails, or Stripe for taking card
payments."

Wow, that's a subtle, almost unnoticeable promotion of MailChinp. /s

------
vinceguidry
I came expecting a treatment on Structured Query Language, was disappointed
when it turned out to be on Relational Database Management Systems. It doesn't
take a rocket scientist to figure out why RDBMSes won out over non-relational
systems.

What I want to know is why nobody ever came up with a better query interface.
Every abstraction I've ever seen was built on top of SQL.

~~~
jrochkind1
Because SQL is so good at representing operations in the relational algebra
that rdbms are based on, and such a mature technology, that it's awfully hard
to replace it with something better? SQL and rdbms kind of go together, and
always have -- it's the query language that parsimoniously represents what you
can do to a relational source, whose development has happened in concert with
rdbms themselves.

What don't you like about SQL?

~~~
wolfgang42
Not OP, but I find the syntax to be arcane and bizzarely restrictive. Every
query has to be SELECT FROM JOIN WHERE GROUP BY HAVING ORDER BY; for some
reason the table comes after its fields and the ORDER BY can't be expressed
using field aliases defined at the start of the query.

Instead, I've been toying with the idea of a language where queries are
expressed as a pipeline of operations on result sets, like this:

    
    
        FROM employee
          /* result set: all employees */
        LEFT JOIN department ON employee.DepartmentID = department.DepartmentID
          /* result set: all employees + their department */
        ORDER BY employee.last
          /* result set: all employees + their department, sorted */
        WHERE department.name IN ('Sales', 'Engineering')
          /* result set: Sales and Engineering employees + their department, sorted */
        FIELDS employee.first, employee.last, department.name
          /* result set: Sales and Engineering employees' first, last, and department names, sorted */
    

This is a SELECT, but you can for example turn it into an update just by
adding a clause to the end:

    
    
        UPDATE employee.salary = employee.salary * 1.05
    

This is a much more regular language; there's nothing enforcing you to write
these operations in a certain order, and you can add or remove clauses as
necessary. For complex queries, I find that I write in this style anyway,
using a chain of WITH clauses as a pipeline with a final SELECT on the end to
get the results.

~~~
jrochkind1
So it's just like SQL but with the order of statements being more flexible?
Does not seem entirely unreasonable.

~~~
wolfgang42
Basically yes, though I'd also make it so if you alias a field it's referred
to by the alias from then on (e.g. FIELDS foo AS bar ORDER BY bar instead of
SELECT foo AS bar ORDER BY foo). I'd probably be inclined to rename some of
the keywords as well (SORT, FILTER, MAP, REDUCE perhaps).

There are some query builder libraries that let you specify clauses in a
different order, like "query.from(table).where(col,
value).select([...fields])", but I have yet to find one that lets you specify
the query as a series of transformations on tables. (I think the word I'm
looking for is "isomorphic" but that doesn't seem right somehow.)

~~~
stephenhuey
Have you ever seen Arel?

[https://github.com/rails/arel](https://github.com/rails/arel)

------
menzoic
Is it fair to compare the age of SQL to the age of JavaScript or that SQL has
survive rapid change? SQL is a class of languages, while Javascipt is a
specific language. The concepts behind JavaScript are older than SQL, and the
modern SQL languages we use today are younger than 43yrs.

~~~
majewsky
> the modern SQL languages we use today are younger than 43yrs.

99% of people use a miniscule subset of SQL, most of which easily dates back
decades.

------
swalsh
I'm not as old as some of you guys, been programming professionally for 11
years. The one constant in my life is SQL. I like it. My C# code is all
obsolete now, the javascript code I wrote a year ago is obsolete, the Ruby
code I've written never became a successful business. About half the PHP code
I've written has been rewritten by now probably. But the SQL I've written is
still living on, the tables I designed are still in use, the queries are still
querying.

------
davnicwil
Just for fun: does anyone have any stories about initially using a SQL
database for a project, later hitting problems, then switching to (or
augmenting with) a NoSQL solution that solved those problems?

~~~
Kiro
Kind of. I started building my game using SQL but realized everything became
so much easier if I didn't have to follow a schema. For example I like to add
arbitrary properties to the player objects all the time. With MongoDB it's a
no-brainer. I just add the property and save.

~~~
elmigranto
> no schema

Do you read them back? Does your code expect property "X" to be on a "Player"
object? Do you put a check everywhere for when it doesn't? Do you have default
value for objects that were created before new property was added?

All that work could've been done by your database (which is probably has some
kind of JSON implementation anyways), but somehow it's a "no-brainer" to
implement data consistency at app-level. Okay.

~~~
scarface74
_Does your code expect property "X" to be on a "Player" object? Do you put a
check everywhere for when it doesn't? Do you have default value for objects
that were created before new property was added_

Why would you put checks _everywhere_ a properly written code base would have
one module/service that handles the player object and every consumer would use
that one module.

~~~
aidos
There have to be _some_ checks, somewhere. Even though it's limited to one
module, you can get temporal inconsistencies.

e.g. you add a new field and an application level default, then later that
default is changed. Now there's no notion of which default the docs that never
had the property should be using. Sure, you just need to patch the old docs to
set the property where it's missing. But the GPs point was that by not keeping
a consistent schema you end up moving the definition of the data into the
application, and you need to be very aware of the trade-off.

Personally I'm really happy using postgres and dropping into jsonb when it's
the correct model to use. It's the best of both worlds as far as I'm
concerned. I've built and worked on systems using nosql solutions that have
been migrated back to sql and the data inconsistencies we found have made me
very wary of having a primary datastore that doesn't enforce strict
constraints.

Though, everybody's use case is different.

~~~
scarface74
_But the GPs point was that by not keeping a consistent schema you end up
moving the definition of the data into the application, and you need to be
very aware of the trade-off._

Why wouldn't the defaults be in the application? Why wouldn't it be treated
like any other piece of business logic in code that should be unit tested?

That being said, I only caught the NoSQL religion about six months ago when I
started using Mongo. But SQL Server 2016 has such great support for JSON
natively, as long as I'm spending other people's money, I prefer having the
best of both worlds.

~~~
elmigranto
> Why wouldn't the defaults be in the application? Why wouldn't it be treated
> like any other piece of business logic in code that should be unit tested?

How about we check our data in the place where it lives given how very robust
and mature products that do that were already written, tested, and used in
production by literally thousands of people for decades.

~~~
scarface74
_How about we check our data in the place where it lives given how very robust
and mature products that do that were already written, tested, and used in
production by literally thousands of people for decades._

So by "checking data" can we check all of the business constraints?

~~~
elmigranto
Yes. If you really want to.

Definetly stuff like presence of fields and their basic sanity (string not
being empty or within a certain length), date intervals, uniqueness of certain
fields or combinations, etc.

You can go as far as you want, really; but like with most cases, when there's
a range of possibilities, both extremes are probably a wrong answer.

------
_pmf_
I'd really like for something like K/Kx to pick up. For server applications,
the dichotomy between DB and application seems so artificial for a lot of
applications. Think Erlang + Mnesia, but with a fully relational model backed
by language primitives.

I think LINQ with F#'s type providers would probably be what I have in mind
(which works like JOOQ with a tighter integration).

------
Animats
The great thing about SQL databases is that the expected standard of
performance is "just works". Works all the time, for years, without trouble,
even for the hard cases. All the major SQL databases, SQLite, MySQL, MariaDB,
Postgres, Microsoft SQL Server, and Oracle, achieve that.

Contrast this with most webcrap. Or most of the NoSQL databases.

------
cygned
In my experience, (O)RDBMS and SQL are very good solutions for most of the
business cases. I know a lot of projects that jumped on the "NoSQL for
everything" train and eventually migrated partly to a RDMBS.

I often don't understand why people try to avoid SQL by any costs instead of
just learning and applying it properly. I don't understand those "we do SQL
for everything" teams either.

RDBMS in conjuction with NoSQL solutions can be a very powerful combination.
We do a lot of Postgres + Redis + CouchDB in my projects.

------
dangoldin
A bit of a plug but I wrote about this a week or so ago describing SQL as the
perfect interface. Databases change and evolve but since they all wrap the
underlying engine in SQL it becomes very easy to use new technologies under
the same interface: [http://dangoldin.com/2017/04/11/sql-is-the-perfect-
interface...](http://dangoldin.com/2017/04/11/sql-is-the-perfect-interface/)

------
cm2187
I think most developpers using SQL use it a bit like most office users using
VBA (ie they know how to record VBA macros but not much more). Most developers
know how to write basic queries so have a very superficial understanding but
most likely know very little about the performance implications, structured
indexes, nesting of queries, etc. Whereas I would expect developpers who claim
to use C to have more than a superficial understanding of the language.

~~~
maxxxxx
That describes me. I think it would be good if SQL databases had more
accessible tools. The experience is just totally different from other
programming languages. When I see a complex query I can't read it and don't
understand what its implications could be. It reminds me a litte of regular
expressions. If you don't use them all the time you forget the syntax and have
to look it up every time you need them.

Either a more modern looking language or some easy tools to analyze and build
queries would help a lot in my view. Also refactoring tools that can analyze
the impact of table changes in all stored procedures would be nice.

~~~
72deluxe
But the same is true of any other language. Given a complex piece of C++, no
amount of IDE features is going to help anyone understand it, nor know what is
implications could be in a system (without looking at the entire source code
to see where it is used).

What you are lacking is:

a) knowledge of your chosen database system in sufficient detail to know the
trade-offs and pros/cons, and

b) sufficient knowledge of SQL specific to your chosen database system in
order to get the most out of it.

This is a bit like knowing C++ in sufficient detail to be able to cope with a
complex bit of code that you see, and also knowing your compiler sufficiently
well so that you know it has foibles/bugs in certain areas.

What you need is more knowledge, not more tools. Even if I have a hammer and a
chainsaw, both are useless to me if I don't know enough about wood to build
what I want.

~~~
maxxxxx
You are right. At least for me the problem is that I don't have enough time to
spend on dealing with the database. There are a ton of other components in the
system that need attention so there is not enough time for SQL in depth. And
yes, the tooling could improve a lot. Source control is a pain. Some query
analyzers that explain the execution path would help. Refactoring tools would
be good. Better stored procedure syntax would be nice.

Technically SQL databases are miracles in performance but to me they are
simply not accessible enough.

~~~
72deluxe
I used to write a lot of SQL but now not so much so I am in the same situation
where I forget everything.

I suppose the only way to keep proficient in any of the languages we learn is
to keep using them, either through a side project (if we have time) or in our
main job. I try and keep time set aside to read and keep up to date on
languages etc.

I think just immersing yourself in your database and trying to solve problems
with it, combined with reading of documentation is the only way to improve.

Out of interest, what database system are you using?

~~~
maxxxxx
MS SQL Server and Mongodb at the moment. Have used SQLite and Oracle before.
My problem is that the the database is only a very small part of the system
and doesn't integrate well. I simply don't have time to immerse myself. I have
done this several times before and after a while I just forget. Not the
principles but the syntax. We just had a need for a huge transformation.
Someone wrote it in C# procedurally and it was super slow. I told him to try
pure SQL and it was super fast. Really impressive. But the code is just super
cryptic to most programmers and hard to maintain. And as I said before source
control sucks.

~~~
72deluxe
SQL Server is excellent, truly excellent. You have a bunch of excellent tools
there with the profiler, query analyser and management studio in general.

SQLite is good fun for embedded stuff.

C# being slow with a DB sounds like someone was doing a load of processing in
the application instead of in the database? eg. grabbing a load of data with a
simple SQL query and then doing all the processing in C#?? I have come across
that before with someone who knew very little SQL and it's embarrassing to
see.

For the pure SQL, get the guy to add comments and put it in TFS (or whatever
your version control system is). That might help.

If it is super cryptic to most programmers, get them to educate themselves!
All code looks cryptic if you're not used to it.

------
edpichler
This remembers what my teacher said to me a decade ago: "\- Relational
databases are the most successful software humanity have created.".

------
siscia
I love SQL and I believe it will have a big jump in popularity with
microservices.

Each service should have a separate data source and being the sole responsable
of a specific part of the data.

In this environment however a full RDBMS is a little an overkill.

The solution I am working on is RediSQL:
[https://github.com/RedBeardLab/rediSQL](https://github.com/RedBeardLab/rediSQL)

It is a Redis module that embed SQLite.

Redis is nowadays a common piece in any infrastructure.

The little module plugs into Redis and expose a new command REDISQL.EXEC that
provide the ability to run SQL statement.

It is multithread, does not impact the performance of Redis, and very simple
to use.

Great write performance, I got 50k inserts per second on my machine, that
should be enough for most microservices.

I would love any kind of feedback on the module or if you need any help to get
you started just open an issues.

[https://github.com/RedBeardLab/rediSQL](https://github.com/RedBeardLab/rediSQL)

------
ryanar
A lot of mentions to ORMs being the problem, them being poor abstractions,
etc.

In my work with Django's ORM I have run into problem queries as often as I
have with doing SQL, and Django's ORM has never let me down.

ORM keeps your thinking in line with your object oriented code, and I find it
very easy and natural to use. Who cares if there are a bunch of artificial
keys underneath, imo caching queries is a better solution to slow queries than
trying to optimize SQL. So using the ORM is never a pain point.

The other advantage is automatic escaping to prevent SQL injection, which is
still a top contender on OWASP's list. I never have to worry ahout SQL
injection when using the ORM.

Maybe other ORMs are poor solutions, but at least with Django I have been very
happy using it.

~~~
scriptkiddy
+1 for Dajngo's ORM.

I find it to be extremely simple to work with. It also does a fantastic job
generating efficient queries due to the way it forces you to structure your
models. The `F`, `Q`, and aggregation functions are top-notch as well. If none
of the ORM features suit you for a particular query, Django ORM allows you to
write the raw query yourself in a secure manner. Couple this with the
Migration system, and I doubt you'll find a better ORM suite anywhere else.

I find Python has some of the best ORMs out there between Django ORM,
SQLAlchemy, Peewee, and Pony.

------
rconti
My favorite SQL fact: The San Carlos Airport (which is about 1mi from Oracle
HQ; a plane losing power on takeoff would very plausibly crash into the
towers, and in fact one did fall into the Redwood Shores lagoon a few years
back, likely a choice by the pilot to avoid hitting a populated area) is KSQL,
so airport code SQL. And its existence predates Oracle's headquarters being
located there. It's just a coincidence.

KSQL and Oracle towers:
[http://www.bayareapilot.com/IMG_0317%20Large%20Web%20viewnea...](http://www.bayareapilot.com/IMG_0317%20Large%20Web%20viewnearingSQL.jpg)

------
js8
SQL is good, but it shows its age. Today somebody should come up with
something statically typed and more functional (meaning using lambda calculus
as a starting point).

The biggest pain points of SQL (IMHO) are:

\- lack of statically typing guarantees (for example, no guarantee that a
certain table has certain column)

\- bad capability to abstract over parts of the data model (for example,
queries have to specify the table that they query)

Both of these can be resolved with use of good enough functional language.
There are projects like that in the FP/Haskell community (e.g. Ermine), but
it's fragmented.

~~~
laumars
You might need to explain yourself a little more because I'm confused by your
examples:

> _" \- lack of statically typing guarantees (for example, no guarantee that a
> certain table has certain column)"_

I would have described SQL as statically typed because columns are given a
type when created. The issue you described seems more akin to you wanting
auto-complete features for SQL (which do exist in some DB management tools)
because while you can specify in code a column that doesn't exist, it will
fail to compile on the RDBMS in much the same way that you can still write
code that fails to compile in imperative and functional languages.

> " _\- bad capability to abstract over parts of the data model (for example,
> queries have to specify the table that they query) "_

You don't need to specify the table name for each column if you're only
querying one table. The issue arises with multiple tables. I see this a bit
like having to specify the namespace in Go packages or class name in Java
where you don't want to import every property and method into the global
namespace. I'm not sure what the functional solution you envisage would be but
there are workarounds in imperative languages such as the `using namespace`
declaration in C++ and the `with` block in VB (I can't believe I just
referenced VB in a serious discussion!). Generally though I've found the pain
of referencing table names in SQL to be somewhat mitigated through aliasing
them via their acronyms. eg

    
    
        select * from people p, borrowed_books bb where p.name = "laumars"
    

SQL definitely isn't a pretty language though so I am very interested in your
thoughts for how a more intuitive, functional-inspired, query language might
read.

~~~
marcosdumay
> I would have described SQL as statically typed because columns are given a
> type when created.

The biggest problem is that SQL has no well defined "compile time". For all
practical purposes, it's always an interpreted language. I can't check my code
against the production database without running it.

> You don't need to specify the table name for each column if you're only
> querying one table.

I do think the GP was about doing stuff like that:

    
    
        SELECT * FROM function_that_returns_table();

~~~
laumars
> _The biggest problem is that SQL has no well defined "compile time". For all
> practical purposes, it's always an interpreted language. I can't check my
> code against the production database without running it._

I get what you're saying but I've seen that as a pretty common problem outside
of SQL as well.

Firstly, and sorry for the nitpicking, but it will explain my point, I don't
think the "interpreted language" is the right description here. Nearly all
modern "interpreted" languages are still technically compiled, albeit it's
bytecode that's then executed by the language runtime. Usually when people
talk about interpreted languages they're talking about JIT compiled (like
Perl, Python, PHP, Ruby and Javascript, to name a few) but technically one
could argue that Java and other, similar AOT compiled languages are also
interpreted. Even some AOT compilers that produce a Windows PE's (.EXE files)
and Linux ELF (for example) will be interpreted (either that or JIT compiled
to machine code when executed).

Anyhow, the reason I raise that point is because there are a lot of parallels
between SQL and other JIT compiled languages. With JIT compiled languages,
they are obviously still compiled - even if it is just to bytecode. But in
many cases the compiler will perform many of the same sanity checks that an
AOT compiler would. Likewise SQL is compiled albeit by the RDBMS to something
that's optimised for execution on that platform and it's during that
compilation that table names and types are checked. And much like a JIT
compiled language, you have to make a few assumptions about the running
environment when you write the code .eg the same and correct Perl cpan or
Javascript npm modules are installed on dev, UAT and live environments; or
with databases you have to make assumptions that your dev database schemas
mirror your live database.

The problem RDBMS suffer from is that some of the solutions for the JIT
compiled environment problem (eg docker) don't work so well for databases
because you're dealing with data which can be sensitive and is often in flux,
rather than code which should be pretty much unchanged across the deployment
pipeline (whatever that may be).

The data is the key problem here. If you wrote a C++ routine to handle data
encoded in JSON files then you'd still be at the mercy of executing your
program to check it can parse the data correctly (eg the field names are
correct and the data exists). Likewise if you wrote the same routine in
Haskell. Or if you replaced JSON with XML, YAML or CSV files.

I'm not saying SQL is without its warts though nor that a functional-paradigm
reimagining wouldn't be better. But I do think some of the biggest challenges
with SQL is data related and thus could very easily rear their proverbial
heads in other languages too (particularly if you don't want to trust your
language to perform too much background magic for fear of risking the
integrity of the data you're retrieving).

~~~
marcosdumay
Yes, I'm not much proposing any solution there, just stating problems. I have
no idea how to improve it, except that it probably goes through a standard
metadata interface for client side type-checking, or some kind of online type
checking that a client can request.

In fact, the SQL part is almost ready for it (just not standardized). It's the
client that does not know how to check anything. A SQL+Language-X linter will
probably solve it better than a rethink of database servers.

I also don't think a functional based reimagining would improve it.

------
EternalData
Good old SQL. Your reliable friend that always shows up with the right amount
of booze and gas money, and which, when you stop to think about, basically
hasn't ever majorly fucked up around you.

------
davidw
I think back to my first programming job and some of the stuff I used then:
Perl, early versions of PHP and other such tools that I haven't used in a long
time.

Two of the things I still use to this day, though, are:

* Postgres

* Emacs

------
tannhaeuser
While SQL has a large class of uses, and also a smaller class of problems for
which better solutions exist (CAP constraints, time series and other massive
self-join apps, session storage ...) the reason we're going to use SQL in
another 40 years still is that there's no cross-vendor standardization effort
going on anymore (NoSQL vendors don't seem to find it necessary to drive sales
and market growth, and customers don't demand it either).

------
LeanderK
Is there any work on a successor to SQL (just the language, maybe as an
optional frontend)? I am not a fan of SQL, it works great for simple queries,
but fails (in my opinion) for more complicated ones. They get way to
complicated and hard to understand for something that would be easy in other
languages. This is not a critique of relational databases, only the language.

------
manigandham
The amount of confusion in the comments highlights why we don't have anything
better.

SQL is just a language, it specifically stands for Structured Query Langauge.
It has _nothing_ to do with the underlying database.

Relational databases all implement SQL because that's what the language was
originally created for but it's just an interface. Relational databases can
also implement other interfaces like mysql with its X protocol. Other database
types like key/value, document, graph, columnar, time-series, RDFs, etc can
also implement SQL and many are starting to for easier interoperability, like
Cassandra with CQL.

There is definitely potential for a better query language and there are
examples like ReQL and GraphQL but SQL is still just fine for most use-cases.

------
chrisan
Most(?) of the articles I read on HN, and then their comments, always seem to
put NoSQL in a bad light when people use it for things that "should" be in SQL

What _are_ the "correct" use cases for NoSQL? Everything has always been
relational data for me

~~~
metaphorm
> What _are_ the "correct" use cases for NoSQL?

there are many and the different use cases for it that I know of aren't really
connected by a theme or any criterion that can be generalized. this is my own
observations only, and not a statement based on theory. the wisdom of
experience. take it for what it's worth.

1\. application state persistence. persisting the app state in something
closer to its native representation (JSON, for example) is convenient
sometimes. if all you really want to do is save the state and then reload it
later in another session you've got a strong use case for some kind of NoSQL.
Note that application state persistence is very different than object state
persistence. The app state is specific to a single user at a single point in
time. Object state might have multiple users (anyone that can manipulate that
object) and multiple points in time (e.g. objects that are persisted for long
periods of time).

2\. Key:Value store for cacheing. this is the most basic, canonical use case.
Memcached, redis, etc.

3\. data structures that SQL is poorly suited for. Graphs and Trees come to
mind. if you find your SQL schema suddenly becoming bogged down in M2M through
tables, you probably have a good use case for a Graph oriented datastore.

4\. Time series. arguably this is just a subset of relational data, or a
domain specific language extension over traditional SQL, but still, it feels a
little different in practice.

------
18nleung
Does anyone know how to generate a visualization like the one under reason #2
("Battle Tested")?

Here's the gif: [http://imgur.com/K5a7U9O.gif](http://imgur.com/K5a7U9O.gif)

~~~
krallja
[http://logstalgia.io/](http://logstalgia.io/)

------
rumcajz
Unlike with imperative languages which are dozen a dime, there's almost no
alternative to SQL when it comes to relational languages.

That being the case, people rarely even think about whether SQL is a good
language or a bad language, whether it's lacking something etc.

But once you actually try thinking about it, it turns out that it's a pretty
well designed language and any alternatives you can think of are usually much
inferior to it.

------
Skylled
I think it's funny when the author compares SQL being most loved with least
dreaded. They're the same thing. The percentages all add up to 100.

------
jordanthoms
SQL's just so much more flexible than the competing query languages, there's
not a true alternative to it. One syntax does a decent job for transactional
processing, key/value tables, heavily relational data, massive analytical
processing, data warehousing, etc. It's pretty ugly, but it's flexible enough
that you can get the job done even if you need to do something complex.

------
csours
Since a lot of people are thinking about this: Is there a good way to compose
SQL?

I see a bunch of repeated items in many SQL queries, things that would be
functions in another language.

One of my colleagues pointed out that this is indicative of poorly formed
queries. What do you all think?

~~~
combatentropy
It is very likely that repetitive SQL could be helped by views (database
views, not views in the MVC sense). Database views are just named queries:

    
    
      create view red_shirts as
      select *
      from shirts
      where color = 'red'
      ;
    

Then you can just say:

    
    
      select * from red_shirts;
    

This is a simple example. Views are normally much more intricate and useful.
Basically any select-statement could be saved as a view.

Databases also let you define functions.

------
arnon
When we approach a customer with our database, SQL compliance is super
important to them.

Some of our competitors used to be 'SQL-like', and even they swapped to using
full SQL.

I think the fact that SQL is based on solid mathematical principles really
helps it stay relevant.

------
carapace
Should mention SQL wasn't the original RM language:
[https://en.wikipedia.org/wiki/Alpha_(programming_language)](https://en.wikipedia.org/wiki/Alpha_\(programming_language\))

;-)

------
threepipeproblm
Possibly relevant: Modern SQL is a good resource, by Markus Winand, on the
newer aspects of the language. [http://modern-sql.com/](http://modern-
sql.com/)

------
gigatexal
You can get away with not using SQL if using the functionality from LINQ or
Java streams but as a DBA I feel most at home with SQL.

------
ianamartin
SQL was my entry point into software development, and I have a somewhat
emotional attachment to it. And I'm quite glad that it worked that way.

SQL, relational theory, and set theory are a great place to start
understanding how to work with data. And a great way to start understanding
software.

All software deals with data. If you don't have a good understanding of data,
you are never going to have a good understanding of software.

One of the best books I've ever read was Applied Mathematics for Database
Professionals by Lex de Haan, and Toon Koppelaars. I think that's the database
equivalent of SICP. You need to read it and understand it if you want to
seriously deal with data. And you want to if you want to write software.

I'm obviously biased because of the way I got into things, but I look at
things as a top-down vs a bottom-up point of view. I was a violinist and music
theorist before I got into technology, and the bottom-up approach has always
resonated with me.

In classical music, pretty much everything bubbles up from a baseline
foundation and a structure; the stuff at the top that you actually see is a
result of that structure. You don't start with some notes that you want to
play on an instrument and then go and try to find a structure that supports
those notes. You go bottom-up. You lay the foundation and build on that.

It was easy for me to map that idea of musical theory onto a database early in
my career. And I moved up in the stack as I needed to. I started by building
things entirely in SQL. You want complex statistical analysis? Sure, I'll do
that . . . in SQL. Because I didn't know any better.

Then I found out that there are actually other languages that can do certain
types of things much better. R, Python, C#, etc.

11 years later, I'm now very capable in a number of languages, and I don't
suck. Along the way, I've had to put a lot of effort into learning the things
I would have got from a comp sci degree program, and I'm probably not the best
at certain types of software challenges.

I use noSQL stuff for caches and data warehouses, I use some of that for
offloading traffic and keeping the reads separate from the writes. But there
isn't a project that I touch that doesn't involve SQL in some way.

SQL is incredibly useful every day. Learning it, knowing it, understanding it,
is a bare minimum for people I hire.

If you have a comp sci degree, and you don't know SQL, I'm going to probably
write you off. If you have a liberal arts degree of some kind, and you do know
SQL, I'm probably going to hire. You can learn everything else on the job.

None of that is an excuse for the totally shitty article linked here.

We use SQL today because it's good and it works. Not many languages can say
that these days.

~~~
yawaramin
Come to think of it, SQL has basically been my gateway into tech too. At one
non-tech job, I used MS Excel's built-in SQL engine, MS Query, to basically
implement an upsert. At another, I used MS Access to build a pricing
calculator application and pricing SQL scripts which reduced hours-long
processes into instants.

Now as a Scala developer, my team has actually decided to use PostgreSQL, and
I'm still in the process of trying to convince them to fully embrace SQL
instead of just using it as a dumb cache to ease memory pressure on our
backend.

------
eddd
SQL as language is one thing. The fact that RDBMS systems with some
consistence and isolation guarantees is a different story.

------
misterbowfinger
Meh. I'm not one to sing the praises of SQL so highly. I understand it's
history and its use - but it's also really, really difficult to understand and
figure out complex SQL queries.

Personally, I thought REQL was a really interesting take on query languages.
As a developer, it allowed you write much cleaner code. You barely need an
ORM. REQL kinda sucks for analysts at first, but in the long run, it makes
writing complicated queries much, much easier.

------
sebringj
It lists Redis in there for the SQL ones. Redis has SQL now?

------
wittgenstein
I'm wondering what is the revenue of SQLizer?

------
z3t4
i guess a lot of performance was sacrified and a lot of optimizations made,
witch make sql very fast in the current era.

------
crimsonalucard
When the best SQL guy is a dude that memorized a bunch of language hacks to
get the underlying algorithm to be more efficient I question the design
principles of the language. Instead wouldn't it be better for the language to
explicitly allow the user to apply algorithms or procedures to make things
more efficient rather than apply hacks?

The language is too heavy of an abstraction away from what's really going on
under the hood. In a way it suffers from the same issue as functional
programming. Not saying functional programming/sql is bad but... it has issues
like almost everything.

~~~
remotehack
SQL is about relational theory; all that matters is the data.

> Instead wouldn't it be better for the language to explicitly allow the user
> to apply algorithms or procedures to make things more efficient

That...is hacking.

~~~
crimsonalucard
No it is not hacking.

By explicit I mean BinarySearch(Table, x = name) rather than "SELECT * FROM
Table WHERE x = name"

Let me explain to you why "explicit" is better... Why should "SELECT
column_name1, column_name2 FROM table" be more efficient than "SELECT * FROM
table"? The abstraction is so leaky that in order to make a query better you
resort to a language hack that only makes sense when you understand the
instructions SQL compiles down into. This is bad. Leaky abstractions are bad.
I shouldn't have to know what the SQL query is doing to optimize....

In web development your application servers use languages like go or python
that are closer to the metal which allow us to explicitly deploy certain
algorithms without this strange layer of SQL expressions that compiles to
imperative code. This leads to faster applications that are easier to optimize
at the expense of using terse highly abstract expressions such as those found
in SQL.

Here's the strange part of web development. Everyone knows that the bottleneck
for most websites are in the database. Yet why do we deploy easily optimizable
imperative languages in the application server while putting a highly
inefficient SQL expression language over the main bottleneck (the database)?

Shouldn't it be the other way around? Shouldn't we have Web application
servers written in highly abstract functional languages while Database
languages written in easily optimizable imperative code that is closer to the
metal?

~~~
irishsultan
> By explicit I mean BinarySearch(Table, x = name) rather than "SELECT * FROM
> Table WHERE x = name"

So are you saying you want to force your DB to store rows in a particular
order? Because doing a binary search on unsorted data isn't going to work. But
forcing your data to be sorted in a particular way is either going to require
you to insert with explicit instructions to do it in the right place, or your
DB to know that it's sorted on that attribute, in which case you shouldn't
really need to tell that it should do a binary search, because it's obvious.

~~~
crimsonalucard
Dbs do that ANYWAY when you index. In this case assume both tables are indexed
by x.

It's obvious for a single SQL expression because you've studied what happens
underneath the abstraction, but when expressions get complex it's hard to
reason about what a sql expression is doing.

~~~
irishsultan
No, what DBs do when you index is have a separate data structure from the main
table, this doesn't affect what the table itself looks like. Nor is it likely
this index will support a binary search (because a binary search works on
arrays, but an index is most likely a tree structure, even when it's not it's
unlikely to be an array).

What you presumably want is a way to specify which indices to use and perhaps
to specify an order on the joins and perhaps the type of join as well.

~~~
crimsonalucard
That tree structure my friend is called a binary search tree. It is
essentially a permutation of the same kind of search.

~~~
irishsultan
Sure, it's clearly sort of the same thing, but it's not in fact a binary
search (if only because few DBs will have a tree with a branching factor of
2), and this is assuming your index is in fact a tree and not a hash index (do
you really want to specify what kind of index it is, each time you use it? for
that matter do you really want to prevent usage of an index just because you
or one of your colleagues wasn't aware of it's existence)

~~~
crimsonalucard
When did I say you should index the db as soon as you search? All I'm saying
is change the API from a expression based query language to a imperative
language.... noSQL.

------
throwit2mewillU
It works.

~~~
pc86
"It works" is a pretty poor reason to continue using something. Horses and
buggies work just fine. Automobiles are better.

I think it's important to look at _why_ SQL is still the best tool for the job
43 years later, especially in the current climate of going to production with
6-week old JS frameworks.

~~~
throwit2mewillU
It works my young Padawan.

------
plet
SQL is really good at projecting & selecting simple data. My rule of thumb has
been to use it first for any per projects and but as soon I need more than one
table, think deeper about the data and switch to NoSQL if I need to represent
complex data structures or have document storage needs.

Its still amazing how far you can go with a single table and few tweaks to a
postgres instance.

~~~
alunchbox
Haven't seen a reason to use any type of NoSQL aside from a cache layer. Most
Modern RDBMS have Json support if you still want to use a document approach
for specific cases but overall Postgres and SQL Server are able to perform
equally if not better then most recent NoSQL implementations (unless it's a
really specific one off use case for reading)

just look at the nightmare MongoDB has created in most startups a year later.

~~~
plet
For me, NoSQL works great when the structure of the data is unclear but you
have a fixed identifier that you can key off. SQL when the structure is known
and juggling multi-table transactions is not a big PITA.

I'm not sure about the mongoDB nightmare, for me its done everything as the
documentation claims it'll do.

~~~
atomical
If the structure of the data changes why wouldn't you modify the schema? Could
you give an example of what you are talking about?

