At Data Geekery, we live and breathe the SQL language. Having spent many years on the jOOQ development, we’ve seen quite a few SQL features, caveats, and other interesting things.

On this blog, we’re sharing our knowledge with you.

Top SQL Posts

• 10 Cool SQL Optimisations That do not Depend on the Cost Model
• A Probably Incomplete, Comprehensive Guide to the Many Different Ways to JOIN Tables in SQL
• A Beginner’s Guide to the True Order of SQL Operations
• 10 SQL Tricks That You Didn’t Think Were Possible
• 10 Easy Steps to a Complete Understanding of SQL
• Yet Another 10 Common Mistakes Java Developers Make When Writing SQL (You Won’t BELIEVE the Last One)
• 10 More Common Mistakes Java Developers Make when Writing SQL
• 10 Common Mistakes Java Developers Make when Writing SQL

SQL Tricks

• How to Calculate a Cumulative Percentage in SQL
• How to Emulate PERCENTILE_DISC in MySQL and Other RDBMS
• Calculate Percentiles to Learn About Data Set Skew in SQL
• How to Aggregate an Archive Log’s Deltas into a Snapshot with SQL
• Writing Custom Aggregate Functions in SQL Just Like a Java 8 Stream Collector
• How to Use SQL UPDATE .. RETURNING to Run DML More Efficiently
• How to Write a Multiplication Aggregate Function in SQL
• How to Reduce Syntactic Overhead Using the SQL WINDOW Clause
• Find the Next Non-NULL Row in a Series With SQL
• How to Write Multiset Conditions With Oracle VARRAY Types
• How SQL DISTINCT and ORDER BY are Related
• PostgreSQL 11’s Support for SQL Standard GROUPS and EXCLUDE Window Function Clauses
• Using UNPIVOT to Traverse a Configuration Table’s Rows and Columns
• How to Group By “Nothing” in SQL
• Selecting all Columns Except One in PostgreSQL
• How to Run a Bulk INSERT .. RETURNING Statement With Oracle and JDBC
• Calculating Tupper’s Self-Referential Formula With SQL
• Do not GRANT ALL PRIVILEGES to your Production Users
• Creating a Microsoft Excel Style Pivot Table With Grand Totals in SQL
• How to Fetch Oracle DBMS_OUTPUT from JDBC
• Finding all Palindromes Contained in Strings with SQL
• ORMs Should Update “Changed” Values, Not Just “Modified” Ones
• How to Use SQL INTERSECT to Work Around SQL’s NULL Logic
• When to Use Bind Values, and When to Use Inline Values in SQL
• How to Calculate Multiple Aggregate Functions in a Single Query
• Creating Tables Dum and Dee in PostgreSQL
• Impress Your Coworkers With the Incredible NATURAL FULL OUTER JOIN!
• Beautiful SQL: Lateral Unnesting of Array Columns
• How to Emulate Partial Indexes in Oracle
• A Little Known SQL Feature: Use Logical Windowing to Aggregate Sliding Ranges
• How to Quickly Rename all Primary Keys in Oracle
• Why Most Programmers Get Pagination Wrong
• Quantified Comparison Predicates – Some of SQL’s Rarest Species
• Using SQL to Calculate the Popularity (on Stack Overflow) of Derby, H2, and HSQLDB
• Time for some Funky SQL: Prefix Sum Calculation
• SQL JOIN or EXISTS? Chances Are, You’re Doing it Wrong
• Impress Your Coworkers by Using SQL UNPIVOT!
• How to Fill Sparse Data With the Previous Non-Empty Value in SQL
• SQL GROUP BY and Functional Dependencies: A Very Useful Feature
• How to Find the Longest Consecutive Series of Events in SQL
• How to Find the Closest Subset Sum with SQL
• Semi Join and Anti Join Should Have Their Own Syntax in SQL
• You Probably don’t Use SQL INTERSECT or EXCEPT Often Enough
• Don’t Format Dates in SQL. Use the DATE Literal!
• INTERSECT – the Underestimated Two-Way IN Predicate
• What Exactly are SQL Views?
• Use this Neat Window Function Trick to Calculate Time Differences in a Time Series
• How SQL GROUP BY Should Have Been Designed – Like Neo4j’s Implicit GROUP BY
• It’s the Little Things: The PL/SQL NULL Statement, and why Every Language Should have One
• How to Avoid the Dreaded Dead Lock when Pessimistic Locking – And some Awesome Java 8 Usage!
• How to Extract a Date Part in SQL
• How to use SQL PIVOT to Compare Two Tables in Your Database
• 10 SQL Articles Everyone Must Read
• How to Emulate the MEDIAN() Aggregate Function Using Inverse Distribution Functions
• The Awesome PostgreSQL 9.4 / SQL:2003 FILTER Clause for Aggregate Functions
• Do You Really Understand SQL’s GROUP BY and HAVING clauses?
• Use MySQL’s Strict Mode on all new Projects!
• Have You Ever Wondered About the Difference Between NOT NULL and DEFAULT?
• Don’t Miss out on Awesome SQL Power with FIRST_VALUE(), LAST_VALUE(), LEAD(), and LAG()
• Awesome SQL Trick: Constraints on Views
• PL/SQL backtraces for debugging
• All You Ever Need to Know About Recursive SQL
• A Wonderful SQL Feature: Quantified Comparison Predicates (ANY, ALL)
• The Difference Between ROW_NUMBER(), RANK(), and DENSE_RANK()
• Are You Using SQL PIVOT Yet? You Should!
• Logon Triggers: The Oracle Database Magic Bullet
• PostgreSQL’s Table-Valued Functions
• Should I Put That Table Alias or Not?
• SQL Server ALTER TABLE SET DEFAULT
• SQL Server Trick: Circumvent Missing ORDER BY Clause
• Oracle Tip: v$sql Table or View does not Exist
• How to Implement Sort Indirection in SQL
• NoSQL? No, SQL! – How to Calculate Running Totals
• How can I do This? – With SQL of Course!
• CUME_DIST(), a Lesser-Known SQL Gem
• An MS Access SQL Transformation Odyssey
• You’re Very Likely to Have Gotten SQL Date Time Arithmetic Wrong!
• No CROSS JOIN in MS Access
• Lesser-Known SQL Features: DEFAULT VALUES
• The SQL Language’s Most Missing Feature
• A Bit of SQL History
• The Great SQL Implementation Comparison Page
• MongoDB “Lightning Fast Aggregation” Challenged with Oracle
• Counting Distinct Records in SQL
• Add LATERAL Joins or CROSS APPLY to Your SQL Tool Chain
• Auto-Creation of Indexes in RDBMS
• How to Create a Range From 1 to 10 in SQL
• Faster SQL Pagination with Keysets, Continued
• Using SQL Injection Vulnerabilities to Dump Your Database
• Probably the Coolest SQL Feature: Window Functions
• Faster SQL Pagination with jOOQ Using the Seek Method
• Recursive SQL for Data Normalisation
• SQL Trick: row_number() is to SELECT what dense_rank() is to SELECT DISTINCT
• Crazy Translations of Simple SQL Expressions to Various SQL Dialects
• Column Stores: Teaching an Old Elephant New Tricks
• Apache Derby About to Adopt the Awesome SQL:2003 MERGE Statement
• Oracle 12c Goodies: { CROSS | OUTER } APPLY
• How to Generate Date Ranges in Oracle SQL
• SQL Query Transformation Fun: Predicates with Row Value Expressions
• Emulating the SQL standard derived column list
• Row value expressions and the BETWEEN predicate
• Row value expressions and the NULL predicate
• The IS DISTINCT FROM predicate
• Serious SQL: A “convex hull” of “correlated tables”
• CROSS JOIN, a nice example for a rarely used operation
• Binary data in SQL, more trivia
• SQL and booleans, some trivia
• The truth about IN and EXISTS in SQL
• Subtle SQL differences: IDENTITY columns
• SQL incompatibilities: NOT IN and NULL values
• Subtle SQL differences: Constraint names
• Arcane magic with the SQL:2003 MERGE statement
• GROUP BY ROLLUP / CUBE
• The comprehensive SQL bitwise operations compatibility list
• What are procedures and functions after all?
• SQL Trouble with dummy tables
• Recursive queries with Oracle’s CONNECT BY clause
• Stored procedures returning multiple cursors

SQL Tuning

• The Performance Difference Between SQL Row-by-row Updating, Batch Updating, and Bulk Updating
• When Using Bind Variables is not Enough: Dynamic IN Lists
• The Cost of JDBC Server Roundtrips
• Why SQL Bind Variables are Important for Performance
• How to Avoid Excessive Sorts in Window Functions
• 10 Cool SQL Optimisations That do not Depend on the Cost Model
• How to Write Efficient TOP N Queries in SQL
• JOIN Elimination: An Essential Optimiser Feature for Advanced SQL Usage
• How to Find Redundant Indexes in SQL
• Don’t Use the String Concatenation “Trick” in SQL Predicates
• SQL IN Predicate: With IN List or With Array? Which is Faster?
• How to Benchmark Alternative SQL Queries to Find the Fastest Query
• Faster SQL Through Occasionally Choosing Natural Keys Over Surrogate Keys
• Many SQL Performance Problems Stem from “Unnecessary, Mandatory Work”
• Why You Should (Sometimes) Avoid Expressions in SQL Predicates
• Don’t Even use COUNT(*) For Primary Key Existence Checks
• Why You Should Design Your Database to Optimise for Statistics
• Avoid Using COUNT() in SQL When You Could Use EXISTS()
• Say NO to Excessive Use of Surrogate Keys if Performance Really Matters to You
• How to Know if a Given Index Can be Dropped
• How Adding a UNIQUE Constraint on a OneToOne Relationship Helps Performance
• Correlated Subqueries are Evil and Slow. Or are They?
• Do Not Think That One Second is Fast for Query Execution
• Join the No OFFSET Movement!
• SQL Tip of the Day: Be Wary of SELECT COUNT(*)
• The Index You’ve Added is Useless. Why?
• ID Lists Aren’t the Best Solution for the N+1 Problem
• The Myth About Slow SQL JOIN Operations

SQL Tuning: Oracle-specific

• How to Work Around ORA-38104: Columns referenced in the ON Clause cannot be updated
• Beware of Hidden PL/SQL to SQL Context Switches
• Oracle’s OFFSET .. FETCH Can be Slower than Classic ROWNUM Filtering
• The Difficulty of Tuning Queries Over a Database Link – Or How I Learned to Stop Worrying and Love the DUAL@LINK Table
• How to Fetch Multiple Oracle Execution Plans in One Nice Query
• Be Careful When Emulating Parameterised Views with SYS_CONTEXT in Oracle
• Does Your Database Really Use Your Index?
• How to Quickly Enumerate Indexes in Oracle 11gR2
• Don’t Forget to Set the SEQUENCE CACHE Size
• How to get Oracle execution plans with Starts, E-Rows, A-Rows and A-Time columns
• Oracle scalar subquery caching