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
- 10 SQL Articles Everyone Must Read
SQL syntax explained
- Various Meanings of SQL’s PARTITION BY Syntax
- A Rarely Seen, but Useful SQL Feature: CORRESPONDING
- The Useful BigQuery * EXCEPT Syntax
- Functional Dependencies in SQL GROUP BY
- Standard SQL/JSON – The Sobering Parts
- 5 Ways to Better Understand SQL by Adding Optional Parentheses
- SQL DISTINCT is not a function
- Using DISTINCT ON in Non-PostgreSQL Databases
- How SQL DISTINCT and ORDER BY are Related
- 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 Use SQL INTERSECT to Work Around SQL’s NULL Logic
- When to Use Bind Values, and When to Use Inline Values in SQL
- Why Most Programmers Get Pagination Wrong
- Quantified Comparison Predicates – Some of SQL’s Rarest Species
- 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
- 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
- How to Extract a Date Part in SQL
- The Awesome PostgreSQL 9.4 / SQL:2003 FILTER Clause for Aggregate Functions
- Do You Really Understand SQL’s GROUP BY and HAVING clauses?
- A Wonderful SQL Feature: Quantified Comparison Predicates (ANY, ALL)
- Are You Using SQL PIVOT Yet? You Should!
- Should I Put That Table Alias or Not?
- SQL Server Trick: Circumvent Missing ORDER BY Clause
- You’re Very Likely to Have Gotten SQL Date Time Arithmetic Wrong!
- Row value expressions and the BETWEEN predicate
- Row value expressions and the NULL predicate
- The IS DISTINCT FROM predicate
- Binary data in SQL, more trivia
- SQL and booleans, some trivia
- GROUP BY ROLLUP / CUBE
Window functions
- Using IGNORE NULLS With SQL Window Functions to Fill Gaps
- How to Reduce Syntactic Overhead Using the SQL WINDOW Clause
- PostgreSQL 11’s Support for SQL Standard GROUPS and EXCLUDE Window Function Clauses
- A Little Known SQL Feature: Use Logical Windowing to Aggregate Sliding Ranges
- Don’t Miss out on Awesome SQL Power with FIRST_VALUE(), LAST_VALUE(), LEAD(), and LAG()
- The Difference Between ROW_NUMBER(), RANK(), and DENSE_RANK()
- CUME_DIST(), a Lesser-Known SQL Gem
- Probably the Coolest SQL Feature: Window Functions
- SQL Trick: row_number() is to SELECT what dense_rank() is to SELECT DISTINCT
JOIN
- The Difference Between SQL’s JOIN .. ON Clause and the Where Clause
- Impress Your Coworkers With the Incredible NATURAL FULL OUTER JOIN!
- SQL JOIN or EXISTS? Chances Are, You’re Doing it Wrong
- Semi Join and Anti Join Should Have Their Own Syntax in SQL
- No CROSS JOIN in MS Access
- CROSS JOIN, a nice example for a rarely used operation
- The truth about IN and EXISTS in SQL
LATERAL
- LATERAL is Your Friend to Create Local Column Variables in SQL
- Beautiful SQL: Lateral Unnesting of Array Columns
- Add LATERAL Joins or CROSS APPLY to Your SQL Tool Chain
- Oracle 12c Goodies: { CROSS | OUTER } APPLY
Arrays, XML, JSON
- How to Filter a SQL Nested Collection by a Value
- Stop Mapping Stuff in Your Middleware. Use SQL’s XML or JSON Operators Instead
- How to Write Multiset Conditions With Oracle VARRAY Types
Recursive SQL
- All You Ever Need to Know About Recursive SQL
- Recursive SQL for Data Normalisation
- How to Create a Range From 1 to 10 in SQL
- Recursive queries with Oracle’s CONNECT BY clause
DML (Data Manipulation Language)
- The Many Ways to Return Data From SQL DML
- How to Use SQL UPDATE .. RETURNING to Run DML More Efficiently
- How to Run a Bulk INSERT .. RETURNING Statement With Oracle and JDBC
- The Many Flavours of the Arcane SQL MERGE Statement
- Lesser-Known SQL Features: DEFAULT VALUES
- Apache Derby About to Adopt the Awesome SQL:2003 MERGE Statement
- Arcane magic with the SQL:2003 MERGE statement
DDL (Data Definition Language)
- How to Emulate Partial Indexes in Oracle
- How to Quickly Rename all Primary Keys in Oracle
- Have You Ever Wondered About the Difference Between NOT NULL and DEFAULT?
- SQL Server ALTER TABLE SET DEFAULT
Views
Stored procedures
- Calling an Oracle Function with PL/SQL BOOLEAN Type from SQL
- It’s the Little Things: The PL/SQL NULL Statement, and why Every Language Should have One
- PL/SQL backtraces for debugging
- PostgreSQL’s Table-Valued Functions
- What are procedures and functions after all?
- Stored procedures returning multiple cursors
Transactions
Security
- Do not GRANT ALL PRIVILEGES to your Production Users
- Using SQL Injection Vulnerabilities to Dump Your Database
SQL language musings
- The SQL Language’s Most Missing Feature
- A Bit of SQL History
- How SQL GROUP BY Should Have Been Designed – Like Neo4j’s Implicit GROUP BY
- Column Stores: Teaching an Old Elephant New Tricks
Vendor specific tips
- Use MySQL’s Strict Mode on all new Projects!
- Logon Triggers: The Oracle Database Magic Bullet
- Oracle Tip: v$sql Table or View does not Exist
- MongoDB “Lightning Fast Aggregation” Challenged with Oracle
Vendor agnosticity
- Crazy Translations of Simple SQL Expressions to Various SQL Dialects
- The Great SQL Implementation Comparison Page
- An MS Access SQL Transformation Odyssey
- Emulating the SQL standard derived column list
- Subtle SQL differences: IDENTITY columns
- SQL incompatibilities: NOT IN and NULL values
- Subtle SQL differences: Constraint names
- The comprehensive SQL bitwise operations compatibility list
- SQL Trouble with dummy tables
Useful SQL tricks
- A Quick and Dirty Way to Concatenate Two Vaguely Related Tables in SQL
- How to Get an RDBMS Server Version with SQL
- Implementing a generic REDUCE aggregate function with SQL
- Having “constant” columns in foreign keys
- Use NATURAL FULL JOIN to compare two tables in SQL
- A Guide to SQL Naming Conventions
- How to Fetch All Current Identity Values in Oracle
- Calculating Weighted Averages When Joining Tables in SQL
- 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 Write a Multiplication Aggregate Function in SQL
- Find the Next Non-NULL Row in a Series With SQL
- Calculating Tupper’s Self-Referential Formula With SQL
- Creating a Microsoft Excel Style Pivot Table With Grand Totals in SQL
- How to Calculate Multiple Aggregate Functions in a Single Query
- Use this Neat Window Function Trick to Calculate Time Differences in a Time Series
- How to use SQL PIVOT to Compare Two Tables in Your Database
- How to Emulate the MEDIAN() Aggregate Function Using Inverse Distribution Functions
- How to Implement Sort Indirection in SQL
- NoSQL? No, SQL! – How to Calculate Running Totals
- Counting Distinct Records in SQL
- Faster SQL Pagination with Keysets, Continued
- How to Generate Date Ranges in Oracle SQL
- SQL Query Transformation Fun: Predicates with Row Value Expressions
Fun SQL tricks
- How to Plot an ASCII Bar Chart with SQL
- Approximating e With SQL
- Fun with PostGIS: Mandelbrot Set, Game of Life, and More
- Finding all Palindromes Contained in Strings with SQL
- Creating Tables Dum and Dee in PostgreSQL
- Using SQL to Calculate the Popularity (on Stack Overflow) of Derby, H2, and HSQLDB
- Time for some Funky SQL: Prefix Sum Calculation
- How to Find the Longest Consecutive Series of Events in SQL
- How to Find the Closest Subset Sum with SQL
- How can I do This? – With SQL of Course!
- Serious SQL: A “convex hull” of “correlated tables”
Performance
- PostgreSQL 14’s enable_memoize For Improved Performance of Nested Loop Joins
- How to Prevent Execution Plan Troubles when Querying Skewed Data, with jOOQ
- Use IN List Padding to Your JDBC Application to Avoid Cursor Cache Contention Problems
- Calculating Pagination Metadata Without Extra Roundtrips in SQL
- What’s Faster? COUNT(*) or COUNT(1)?
- The Cost of Useless Surrogate Keys in Relationship Tables
- 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
Performance: Oracle-specific
- Oracle’s BINARY_DOUBLE Can Be Much Faster Than NUMBER
- 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