a fork of sqlite that is “open to contribution”

Subqueries and performance in PostgreSQL

Relational (inner) joins are really common in the world of databases, and one weird thing about them is that it seems like everyone has a different idea of what they are. In this post I’ve aggregated a bunch of different definitions, ways of thinking about them, and ways of implementing them that will hopefully be interesting. They’re not without redundancy, some of them are arguably the same, but I think they’re all interesting perspectives nonetheless.

hq hammond rock|metal !jazz year:1970-1980 rating:desc

becomes

SELECT * FROM music
WHERE search LIKE '%hammond%'
AND (search LIKE '%rock%' OR search LIKE '%metal%')
AND search NOT LIKE '%jazz%'
AND year BETWEEN 1970 AND 1980
ORDER BY rating DESC

The (composite_value).* expansion to columns was new to me. And useful in a recent query I had to write.

Free multi-platform database tool for developers, SQL programmers, database administrators and analysts. Supports all popular databases: MySQL, PostgreSQL, MariaDB, SQLite, Oracle, DB2, SQL Server, Sybase, MS Access, Teradata, Firebird, Derby, etc.

Awesome post on how to write SQL:

UPDATE table_name SET array_col = ARRAY(SELECT DISTINCT UNNEST(array_col) ORDER BY 1) where array_length(array_col, 1) > 1;