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?
Tag: mysql
Yet Another 10 Common Mistakes Java Developers Make When Writing SQL (You Won’t BELIEVE the Last One)
(Sorry for that click-bait heading. Couldn't resist ;-) ) We're on a mission. To teach you SQL. But mostly, we want to teach you how to appreciate SQL. You'll love it! Getting SQL right or wrong shouldn't be about that You're-Doing-It-Wrong™ attitude that can be encountered often when evangelists promote their object of evangelism. Getting … Continue reading Yet Another 10 Common Mistakes Java Developers Make When Writing SQL (You Won’t BELIEVE the Last One)
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
The Crystal Ball. Or, Oops, Michael Stonebraker did it Again
Michael Stonebraker's opinions and claims are always refreshing to read. He's done a lot for our industry and for how we do data processing. Some of his claims are certainly right as well. Here's an interview with him, telling us about his 5 predictions on the future of databases. Of course, him being a software … Continue reading The Crystal Ball. Or, Oops, Michael Stonebraker did it Again
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
CUBRID: A Lesser-Known Korean OSS Database Gem
While RedHat and Google have been dumping MySQL for MariaDB, there's actually a third, much lesser-known option for MySQL-oriented database folks in the RDBMS market: CUBRID. One of CUBRID's main goals is also to lure MySQL users away from Oracle by offering many equivalent syntax elements that are available in either the MySQL or Oracle … Continue reading CUBRID: A Lesser-Known Korean OSS Database Gem
jOOQ Newsletter September 17, 2013
Subscribe to this newsletter here. SQL for calculations SQL can be used for heavy calculations. This doesn't mean that it has to, of course. Many Java-oriented software architects are reluctant to allow for business logic entering their database. DBA tend to disagree and promote complex logic in database views or stored procedures. The pros and … Continue reading jOOQ Newsletter September 17, 2013
The Myth About Slow SQL JOIN Operations
In my recent SQL work for a large Swiss bank, I have maintained nested database view monsters whose unnested SQL code amounted up to 5k lines of code, joining the same table over and over again in separate subselects combined via UNION operations. This monster performed in way under 50ms, no matter how we queried … Continue reading The Myth About Slow SQL JOIN Operations
MySQL Bad Idea #573
This is MySQL's Bad Idea #573 (after #384, which I've blogged about before) I've just had a terrible experience with a bug report from the jOOQ User Group, related to escaping of backslashes in string literals in MySQL. First, I thought to myself, whatever. SQL doesn't escape backslashes. The only escape character within a string … Continue reading MySQL Bad Idea #573
SAP’s Hilarious SQL Whitepaper(s)
While looking for some authoritative information about Sybase SQL Anywhere 12's TOP .. START AT clause, I stumbled upon this hilarious white paper here, which I do not want to keep from you: http://www.sybase.com/files/White_Papers/Sybase_Top_10_Features_In_SQL_Anywhere_12.pdf I will take advantage of "fair use policy" and cite parts from section 7: Feature number 7: improved support for DaffySQL … Continue reading SAP’s Hilarious SQL Whitepaper(s)