Every now and then, I see folks lament the SQL syntax's peculiar disconnect between the lexical order of operations (SELECT .. FROM) the logical order of operations (FROM .. SELECT) Most recently here in a Youtube comment reply to a recent jOOQ/kotlin talk. Let's look at why jOOQ didn't fall into this trap of trying … Continue reading Changing SELECT .. FROM Into FROM .. SELECT Does Not “Fix” SQL
It appears that our recent beginner SQL articles explaining SQL syntax were quite popular. These include: A Beginner’s Guide to the True Order of SQL Operations A Probably Incomplete, Comprehensive Guide to the Many Different Ways to JOIN Tables in SQL 10 Easy Steps to a Complete Understanding of SQL How SQL DISTINCT and ORDER … Continue reading 5 Ways to Better Understand SQL by Adding Optional Parentheses
One of the things that confuse SQL users all the time is how DISTINCT and ORDER BY are related in a SQL query. The Basics Running some queries against the Sakila database, most people quickly understand: SELECT DISTINCT length FROM film This returns results in an arbitrary order, because the database can (and might apply … Continue reading How SQL DISTINCT and ORDER BY are Related
Infrequent SQL developers often get confused about when to put parentheses and/or aliases on derived tables. There has been this recent Reddit discussion about the subject, where user Elmhurstlol was wondering why they needed to provide an alias to the derived table (the subselect with the UNION) in the following query: SELECT AVG(price) AS AVG_PRICE … Continue reading Should I Put That Table Alias or Not?
MySQL... We've blogged about MySQL before. Many times. We've shown bad ideas implemented in MySQL here: MySQL Bad Idea #384MySQL Bad Idea #573 But this beats everything. Check out this Stack Overflow question. It reads: "Why Oracle does not support 'group by 1,2,3'?". At first, I thought this user might have been confused because SQL … Continue reading MySQL Bad Idea #666
A great database has just gotten better. PostgreSQL 9.3 has been released today. While improved reliability and availability is certainly quite a thrilling addition, from the jOOQ perspective, the most interesting features are new SQL syntax elements. These include: Better support for JSON LATERAL JOIN (or as SQL:1999 calls it: lateral derived table) Materialised views … Continue reading PostgreSQL 9.3 Released!