What is the difference between SQL and non-SQL databases?


#1

What is the difference between SQL, or relational databases, and non-SQL (non relational) databases?


#2

I think (and please correct me if I’m wrong) the difference is that non sql databases are db that stored data in plain text following some sort of Xml format instead of requiring a data access engine to access data stored in tables.


#3

Well, it’s more that SQL is a very specific thing with a very standardized language and way of storing data in tables.

Now if a database is a No-SQL database that tends to mean it has more of a specialized purpose that doesn’t need SQL or that just doesn’t use it for whatever reason. You could have something like InfluxDB which is for high speed time series data, or you can have something like MongoDB which is good at storing documents but not with linking across them. Redis would be a good example of a specialized database, and it basically is like a shared data structure server.


#4

Two major differences between NoSQL and SQL databases are:

  • NoSQL databases do not use a predefined schema like SQL databases. You don’t create “tables” with certain “columns” that become a format your data (“rows”) must adhere to. Every entry in a NoSQL database can have a different structure. Many NoSQL databases are “key-value stores” which allow you to define entries as keys (i.e. “foo”) with values that can be anything from a string to a number to a list to a JSON blob, a large nested data structure, etc.
  • You query a NoSQL database using some other language/tool instead of SQL. This will differ based on the database in question.

#5

@ltrico59 Don’t the newer, hotter SQLs like Postgres also allow different data types in different fields inside the same column?


#6

Well, I think they try but it’s tough to beat a database that doesn’t have a legacy of strict schemas.


#7

Sort of. Postgres offers both an “array” type and a “json” type. If you assign one of these types to a column, every entry will have to be an array/json blob. The array can contain values of any other supported type, and the JSON, being JSON, can be a nested object containing its own arrays, complex objects, etc.

While this allows for some behavior similar to a NoSQL store, what you still can’t do in Postgres is have a column that would allow an array/json blob OR an integer OR a string OR a timestamp, etc.