MySQL is a database that has been bending the SQL standard in ways that make it hard to move off MySQL. What may appear to be a clever technique for vendor lockin (or maybe just oversight of the standard) can be quite annoying in understanding the real meaning of the SQL language. One such example … Continue reading Use MySQL’s Strict Mode on all new Projects!
Tag: sql standard
Should I Put That Table Alias or Not?
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?
NoSQL? No, SQL! – How to Calculate Running Totals
If anything at all, our jOOQ talks at various JUGs and conferences have revealed mostly one thing: Java developers don't know SQL. And it isn't even necessarily our fault. We're just not exposed to SQL nowadays. But consider this: We developers (or our customers) are paying millions of dollars every year to Oracle, Microsoft, IBM, … Continue reading NoSQL? No, SQL! – How to Calculate Running Totals
How can I do This? – With SQL of Course!
Haven't we all been wondering: How can I do this? I have these data in Excel and I want to group / sort / assign / combine ... While you could probably pull up a Visual Basic script doing the work or export the data to Java or any other procedural language of choice, why … Continue reading How can I do This? – With SQL of Course!
An MS Access SQL Transformation Odyssey
Recently, we've added support for the MS Access database in jOOQ 3.3. This may well have been our hardest integration so far. The MS Access database has its ways, and many of them, too. But luckily, jOOQ's internal SQL transformation abilities are already very advanced, as we've shown previously in a blog post about the … Continue reading An MS Access SQL Transformation Odyssey
Lesser-Known SQL Features: DEFAULT VALUES
A lesser-known SQL feature is the DEFAULT keyword, which can be used in INSERT and UPDATE statements. Consider the following table, created using standard SQL syntax: CREATE TABLE timestamps ( id INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1), t TIMESTAMP DEFAULT CURRENT_TIMESTAMP, CONSTRAINT pk_values PRIMARY KEY (id) ) Now, in order to generate a … Continue reading Lesser-Known SQL Features: DEFAULT VALUES
The SQL Language’s Most Missing Feature
SQL is also awesome in many ways. We can write out the most complex truths and facts and have the database tell us the answer in no time. But the SQL language is arguably the most beautiful programming language out there. It has so many caveats that people like me get ridiculously rich selling consulting services … Continue reading The SQL Language’s Most Missing Feature
Why Did SQLJ Die?
Every now and then, SQLJ pops up somewhere, mostly in a very dusty/enterprisey or in an academic context. If you give SQLJ some thought, though, it isn't such a bad idea. It is: An ANSI and ISO standardPart of the SQL standardQuite easy to understandQuite a powerful extension to JDBC So why did it die … Continue reading Why Did SQLJ Die?
MySQL Bad Idea #666
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
Counting Distinct Records in SQL
The SQL language and its depths... Some of you readers might be aware of MySQL's capability of counting distinct records through the COUNT() aggregate function. The MySQL documentation reads: COUNT(DISTINCT expr,[expr...]) Returns a count of the number of rows with different non-NULL expr values. In other words, you can count distinct first and last names very easily: SELECT … Continue reading Counting Distinct Records in SQL