14 ms·
Easy Steps to a Complete Understanding of SQL
- harrytuttle 13y agoNot a bad article but it needs a step 11: 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...
- lukaseder 13y agoWell, to beginners it's always good to express things in a positive way. The "DBA rules" is part of the 2nd year of SQL experience tutorial :-)
- ams6110 13y agoThis is why you have a test instance for developers (or more likely now, every developer runs his own engine locally or in a VM).
- harrytuttle 13y agoThat's irrelevant as it usually only goes bang in production under production load and data volumes.
- Shivetya 13y agohow many platforms do not permit setting limits on processing of queries?
- rhizome31 13y agoAnother thing to understand is how NULLs are handled in different contexts, using either the familiar two-valued logic or the more exotic three-valued logic. It's kind of messy but really worth knowing if you're working with SQL. The wikipedia page actually gives a pretty good account of the issue: https://en.wikipedia.org/wiki/Null_%28SQL%29 https://en.wikipedia.org/wiki/Null_%28SQL%29
- zamalek 13y agoThe way I see SQL in my mind is programming Venn Diagrams ( http://en.wikipedia.org/wiki/Venn_diagram http://en.wikipedia.org/wiki/Venn_diagram ) followed by creating a projection that uses one or more of the areas within that diagram (contrasting strongly with imperative languages, i.e. logic programming). Unfortunately it's not straight-forward to do that because SQL is a superset of Venn Diagrams, but that line of thinking is where you need to be.
- ams6110 13y agoYes, exactly. To use SQL well you need to think in terms of sets and set operations. Venn Diagrams are a good idea to help visualize this. If you find yourself writing a lot loops over cursors there's a good chance you're doing it wrong.
- ams6110 13y agoSQL JOIN tables should be used rather than comma-separated tables 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.
- chris_wot 13y agoThey stop Cartesian products from accidentally occurring. That alone should force the use of ANSI joins. cross joining two million row tables is not a pleasant experience!
- einhverfr 13y agoI think the key is that join condition problems are easier to spot. There are several types query issues which are classic join condition issues. If you use cross join and where, these are harder to spot when troubleshooting. I think this is a bigger issue actually than accidental cross-joins. The latter sometimes happens occasionally. The former happens much more frequently and so wins there in terms of debugging time are quite important.
- batbomb 13y agoLimiting syntax to use only ANSI joins also means that you end up with one and only one table as the root table (or view) to select from. I think this makes code much clearer. 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.
- lysium 13y agoGreat article! I wish all SQL libraries would adhere to this structure instead of forcing devs to write glorified (string) SQL. Ie., let me from(join(table1, table2)) .where(table1.col1 > 10) .groupBy(col1) .project(col1, avg(col2)) and only in that order.
- chris_wot 13y agoHave you tried LINQ?
- deleted 13y ago[deleted]
- lukaseder 13y agoCheck out http://www.jooq.org http://www.jooq.org, by the same author as the article (me)
- lysium 13y agoLooks great! Do you happen to know whether jooq runs with DB2 v10? I see it supports DB2 v9.7.
- adad95 13y agohttp://www.linqpad.net/WhyLINQBeatsSQL.aspx http://www.linqpad.net/WhyLINQBeatsSQL.aspx
- batbomb 13y agoI wrote my own library in Java recently for something similar. It was inspired by LINQ and SQL grammar. 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));
- andrewcooke 13y ago
- chris_wot 13y agoBe careful with point 6. If you use a subquery through an IN clause and it returns a NULL as one of the rows, nothing will return.
- hobs 13y agoI was just about to say that! Truthfully this is a good introduction but is definitely not everything you need to know about SQL.(Obviously) Handling nulls is just one of many sticklers people using SQL frequently screw up.
- lukaseder 13y agoThat's only true for NOT IN predicates (which is mentioned in the article) Example: http://sqlfiddle.com/#!4/d41d8/16785 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)
- chris_wot 13y agoDoh! Of course. :-) 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!
- lukaseder 13y ago> which ultimately causes he Boolean expression to be false 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-and-null-values/ 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-the-null-predicate/ 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) > I hope that redeems my reputation a little :-) You're forgiven ;-) Thanks!
- jfb 13y agoI thought this was a very good article, unusually so for a blog post about databases. I do take issue, however, with the idea that there's a beauty to SQL. There's a beauty in the conceptual model, but you have to squint sideways to see it through the deeply hostile syntax.
- chris_wot 13y agoThe conceptual model is great, but IMHO a little impractical. Anyone who has ever had to trick the optimiser into choosing a better plan by changing the semi join from WHERE EXISTS to NOT IN (or vice versa) will appreciate what I'm saying :-)
- jfb 13y agoLovely conceptual models always founder on the shoals of terrible implementation.
- chris_wot 13y agoNot necessarily. It's almost impossible for a CBO to get the best query plan for every query under every load under all data distributions. But the relational model is still beautiful, I find its limitations are minor in the grand scheme of things.
- jeffdavis 13y ago"It's almost impossible for a CBO to get the best query plan for every query under every load under all data distributions." 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.
- chris_wot 13y agoI'm not disagreeing :-) Although the poor DBA on pager may not agree with you.
- jcampbell1 13y agoI think the author has the order wrong. Consider: SELECT WEEKDAY(created_at) wkday, COUNT(1) FROM orders GROUP BY wkday The above query works fine, thus GROUP BY, ORDER BY, and HAVING are all aware of SELECT.
- lukaseder 13y agoTrue, the explanation was maybe a bit simplistic. Some expressions are allowed to appear even if they're not declared in the GROUP BY clause...
- chris_wot 13y agoOnly scalar expressions. You still need to retrieve the rows.
- chris_wot 13y agoThose are expressions. They only work once you have picked the table(s) - FROM - and determined the rows you want returned - WHERE. The query then groups and orders the results and calculates the scalar value of COUNT(1) which it repeats on every row, then it determines the weekday for every row for the column created_at and uses that value for output.
- jcampbell1 13y agoMy point is that a better explanation of the execution ordering is: 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.
- lukaseder 13y ago(I had to correct myself) 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 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!
- ExpiredLink 13y ago> If a has 3 columns and b has 5 columns, then the "output table" will have 8 (3 + 5) columns. The records contained in this combined table reference are those of the cross product / cartesian product of a x b. This is where the easy steps become hard for real noobs.
- pbhjpbhj 13y agoA graphical example of a Cartesian join - http://web.utk.edu/sas/OnlineTutor/1.2/en/60477/m71/m71_3.htm http://web.utk.edu/sas/OnlineTutor/1.2/en/60477/m71/m71_3.ht....
- julochrobak 13y agohere are some other graphical illustrations of the operators from relational algebra - http://bandilab.org/bandicoot-algebra.pdf http://bandilab.org/bandicoot-algebra.pdf It's focusing on bandicoot language, but concepts are taken from the relational algebra. It should match the logic of SQL.
- saosebastiao 13y agoFantastic article. I clicked on it, hoping to find a way to help some of my less SQL-savvy co-workers to understand SQL a little better. I instead came away with a better understanding of my own.
- triplesec 13y agoStuff in here they nevr taught me all those years ago either! And a smart geek is never be too proud for a refresher.
- collyw 13y agoWhere do you go to keep up with things like this. 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.
- shire 13y agoThanks for this article very beneficial, I'm learning about Databases at the moment so this definitely comes in handy while learning SQL.
- bsaul 13y agoThe most beneficial thing you can do when you start with SQL is to NEVER EVER put join clauses in the WHERE section. ALWAYS put the join clauses as close to the table you're joining as possible. 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.
- mtdewcmu 13y agoI like SQL. The relational model is often underestimated. All of the complex structure that goes beyond individual rows is orthogonal and is itself first-class data. Contrast with document databases where hierarchies are part of the low-level storage mechanism. By making the structure first-class data, the relational model keeps you from painting yourself into a corner.