Easy Steps to a Complete Understanding of SQL
tech.pro
tech.pro
Having used SQL since long before the ANSI JOIN syntax was well supported (first Sybase, then MS SQL and then Oracle) I resisted it for a long time out of habit, and also because at first the syntax was buggy when used in Oracle.
But I have come around to being in favor of it. The bugs have been fixed, and the main advantages are that: inner and outer joins are more clearly stated than by using '*=', or '(+)' suffixes on one side of a predicate; and the join criteria are clearly separated from the WHERE clause. It makes it much easier to see how the tables are being joined vs. how the results are being limited.
I tend to writ my SQL like lisp, very compositional with lots of embedded views, in order to be very explicit on how I want tables and views joined. If the optimizer is good, it will know what parts of your statement it can optimize, and if it's not, it usually follows my code, and I don't have to resort to Optimizer hints in order for Oracle to get its black magic done.
It's also impossible for a human to do it. At least the optimizer will be awake at 3 in the morning and able to respond to data changes; the human will be asleep.
Although the poor DBA on pager may not agree with you.
They were invented for a reason: data changes. Data volume changes and data distribution changes. If your algorithms don't change to suit the inputs, performance will still fall off a cliff. That's a fundamental problem and not unique to SQL.
And this really only matters for somewhat "interesting" queries anyway. It's not like the database will all of a sudden change an "UPDATE foo SET x = x + 1 WHERE id = 13" into a 14-table join.
In practice, you have many queries adapting nicely to changing data, and then a couple where they get some estimates wrong and drive you crazy. But just because you are able to fix the plans in some of those instances doesn't make it a valid comparison. You have to compare it against hand crafting all of the plans (or writing equivalent algorithms) and the results of doing that. I suspect that in most cases, more plans will drive you crazy if you choose the plans rather than letting an optimizer do it.
Remember, the data is going to change once you get in production.
Hopefully 95% or more of queries don't need to be nudged :-)
What modern relational databases have... is a very intuitive abstract storage model. That's awesome. We've been working on an alternative "navigational model" (inspired from CODASYL and ORMs) based on this storage model. Our experimental query language is at http://htsql.org
Our critique of SQL is at http://htsql.org/doc/overview.html#why-not-sql
http://blog.jooq.org/2013/08/24/mit-prof-michael-stonebraker...
htsql seems very interesting. I should soon blog about your product and critique. Have you been publishing elsewhere?
Things like Amazon Redshift are still relational but make radically different decisions on most of those points.
Edit: that talk is well worth reading, btw. I wonder why he completely ignores flash though?
BTW, the person asking the last couple of questions is Ed Bugnion, one of the co-founders of VMWare. He is a faculty now at EPFL.
He's arguing against multithreaded systems in favour of a partitioned OLTP platform like H-Store (VoltDB). I didn't see anything about the relational model being "all wrong". It works well for lots of scenarios, and turning it into a KV store is also straightforward.
As far as SQL, I think people agree it's not the best query language. QUEL may have won, but Stonebraker says[1]: "The only reason SQL won in the marketplace was because IBM released DB2 in 1984 without changing the System R query language."
1: http://iggyfernandez.wordpress.com/2011/12/23/nocoug-journal...
What is intuitive to users is the idea of related files/tables, and this really doesn't have much to do with the relational algebra. While we have implemented HTSQL using SQL, this is an implementation choice -- our experiment is syntax/semantics, we didn't want to be in the business of data storage and query optimization, and, we use (the incredibly awesome) PostgreSQL. HTSQL's logical query model depends upon is a tabular based abstract storage.
For what it's worth, a join is indeed part of relational algebra - it's a composition of binary relations.
how does htsql do with this?
/student{, /fork(year(dob),month(dob))}
http://demo.htsql.org/student%7B*,%20/fork%28year%28dob%29,m...
You could wrap this up in a definition to make it a bit easier to understand:
/student .define(students_same_month := fork(year(dob),month(dob))) .select(, /students_same_month )
http://demo.htsql.org/student%0A.define%28students_same_mont...
With the more general case, let's say you're interested in listing per semester, which students started during that semester. In this case, you start with ``/semester`` and then define the correlated set, ``starting_students`` which uses the ``@`` attachment operator to link the two sets. Then, you'd select columns from semester and the list of correlated starting students.
/semester .define(starting_students:= (student.start_date>=begin_date& student.start_date<=end_date)@student) {, /starting_students }
http://demo.htsql.org/semester.define%28starting_students:=%...
Typically, you'd include the new link definition, ``starting_students`` in your configuration file so that you don't have to define it again... then the query is quite intuitive:
/semester{, /starting_students }
While this all may seem complex, it's not an easy problem. More importantly, HTSQL has the notion of "navigation". You're not filtering a cross product of semesters and students, instead, you're defining a named linkage from each semester to a set of students. The rows returned in the top level of query are semesters and the rows returned in the nested level are students.
I made some queries that are analogous to my temporal query needs
Here I'm looking for every student, other students with DOB within 2 weeks of the given student: http://demo.htsql.org/student.define(similar_students:=%20(s...
Here for every semester with at least one student, I find the oldest and the youngest students enrolled: http://demo.htsql.org/semester%20.define(starting_students:=...
No being a computer scientist I have to admit I do not appreciate the intricacies of the 'problems with SQL' blog entries. But working with htsql I gotta say it seems a lot more intuitive than SQL. It feels like the logic correspond much better to my mental model. And that there is much less of the jumping up and down the code to nest my SQL code logic that I find myself doing all the time.
Is there a way to install this on a PostgresQL instance on Win8?
You don't see the beauty in SQL until you try to get as much bang for the buck out of specific queries. Then the idea that you can think in terms of math and sets really shines. You can do a lot in SQL which would require a lot more app code to accomplish, and the SQL will be more elegant and easier to maintain.
Please elaborate, I can only think of one.
Your second, and any other 'meanings' are a misuse of NULL, which is your fault, not SQLs
Both of the uses I stated are data values that do not exist in the database.
> Your second, and any other 'meanings' are a misuse of NULL
No, they aren't. They are different, and substantively different, reasons why data does not exist in a database. One is the value does not exist in the domain of which the DB is model, and one is that the value exists in that domain but it is not known.
(Others have pointed out the additional case of "we do not know if it exists", which is ambiguity between those two cases.)
These two substantially different meanings of NULL were recognized fairly early in the history of the relational model by the creator of the model (E.F. Codd), who proposed refining the model by having separate markers for each of those two cases.
1. Value is unknown
2. Value does not exist (for example, outer join results).
These have ery different implications. For example:
somestring || does_not_exist you would think would equal somestring, but with unknown we dont know.
Does this mean SQL is pretty? Not really, but it gets the job done. Though I did have to spend a lot of time optimizing SQL to force certain query optimizations.
Does this mean relational should be used everywhere? No, but it has a distinct place. I think beer is beautiful too, but I won't drink it for breakfast with the in-laws.
eg : never do SELECT * FROM A JOIN B WHERE A.ID = B.a_id and B.id > 10 but always JOIN B ON A.ID = B.a_id AND B.id > 10
the second way of doing "scale" much much better when you add more tables, and start mixing left and right joins.
from(join(table1, table2))
.where(table1.col1 > 10)
.groupBy(col1)
.project(col1, avg(col2))
Do you happen to know whether jooq runs with DB2 v10? I see it supports DB2 v9.7.
Select statement = table1.select()
.join(table2,[join condition])
.where(table1.col1.gt(10))
.groupBy(table1.col1)
.selection(col1, Fn.avg(table1.col2))
Select s = new Select()
.from(table1)
.join(table2, table2.t1_col1.eq(table1.col1))
.where(table1.col1.gt(10))
.groupBy(table1.col1)
.selection(col1, Fn.avg(table1.col2));Example: http://sqlfiddle.com/#!4/d41d8/16785
with a(x) as (
select 1 from dual union all
select 2 from dual union all
select null from dual
)
select 1 from dual where 1 in (select x from a)The reason being is that IN uses an disjunction and NOT IN uses a conjunction. When you compare the value to NULL it returns UNKNOWN, which ultimately causes the Boolean expression to be false.
I hope that redeems my reputation a little :-)
Btw, great article!
Strictly speaking, the boolean expression is also UNKNOWN ;-) I've tried to explain this here: http://blog.jooq.org/2012/01/27/sql-incompatibilities-not-in...
... but to this date, I'm still not 100% sure if I correctly understood SQL NULL. A quite worrying thing is documented here: http://blog.jooq.org/2012/12/24/row-value-expressions-and-th...
It's about row value expressions and NULL predicates. The following are not the same!
(A, B) IS NOT NULL
NOT((A, B) IS NULL)
You're forgiven ;-) Thanks!
Those expressions can be worked out via first order predicate logic. Damned tricky, I can definitely see how that would trip up pretty much anyone at first glance. If you weren't aware of logical equivalences then you might be scratching your head for some time!
Another great article, btw.
I was taught at a reputable university to put joins in the where clause. Now it seems that is frowned upon, and I only find out 13 years later.
This is where the easy steps become hard for real noobs.
It's focusing on bandicoot language, but concepts are taken from the relational algebra. It should match the logic of SQL.
SELECT WEEKDAY(created_at) wkday, COUNT(1)
FROM orders
GROUP BY wkday
FROM, JOIN, WHERE, SELECT, GROUP BY, HAVING, DISTINCT, UNION, ORDER BY, LIMIT.
The author has SELECT after the GROUP BY and HAVING, which doesn't make sense because GROUP BY is aware of expressions in the SELECT statement. Grouping and filtering happens after selection.
SELECT is evaluated after GROUP BY, at least in most databases that implement SQL correctly. SQL Server's documentation nicely explains this:
http://technet.microsoft.com/en-us/library/ms189499.aspx
Skip to "Logical Processing Order of the SELECT statement".
MySQL, PostgreSQL, and SQLite behave differently, though!
WITH orders(created_at) AS (
SELECT date '2012-01-01' UNION ALL
SELECT date '2012-01-02' UNION ALL
SELECT date '2012-01-03' UNION ALL
SELECT date '2012-01-04' UNION ALL
SELECT date '2012-01-05' UNION ALL
SELECT date '2012-01-06' UNION ALL
SELECT date '2012-01-07' UNION ALL
SELECT date '2012-01-08'
)
SELECT EXTRACT(DOW FROM created_at) wkday, COUNT(1)
FROM orders
GROUP BY wkday
Edit: it seems more complicated that I initially assumed. MySQL doesn't allow it. SQLite does.
Rule number 2, for instance, does not apply exactly in the above way to MySQL, PostgreSQL, and SQLite.
Know thy database engine for if you do not, your local DBA will be mightily pissed off when you do a cross join across 15 tables and watch his/her IOPS go through the roof...