I recently stumbled upon a standard SQL feature that was implemented, to my surprise, in HSQLDB. The keyword is CORRESPONDING, and it can be used with all set operations, including UNION, INTERSECT, and EXCEPT. Let’s look at the sakila database. It has 3 tables with people in it: Similar, but not the same. What if … Continue reading A Rarely…
#except
5 posts
14 Jan 2022
7 Jan 2022
One of the coolest things about using and making jOOQ is that we get to discover the best extensions to the standard SQL language by vendors, and add support for those clauses in jOOQ via emulations. One of these syntaxes is BigQuery’s * EXCEPT syntax. Everyone who ever wrote ad-hoc SQL queries would have liked … Continue reading The Useful…
5 Aug 2020
There are a few ways to compare two similar tables in SQL. Assuming PostgreSQL syntax, we might have this schema: It is now possible to use UNION and EXCEPT as suggested by Chris Saxon: In PostgreSQL, we can write: Notice how TABLE x is just standard SQL, and PostgreSQL, syntax sugar for SELECT * FROM … Continue reading Use NATURAL…
15 Aug 2018
Oracle is one of the few databases that implements the SQL standard ORDBMS extensions, which essentially allow for nested collections. Other databases that have these features to some extent are CUBRID, Informix, PostgreSQL. Oracle has two types of nested collections: The main difference at first is that a nested table can be of arbitrary size, … Continue reading How to…
6 Oct 2015
When people talk about SQL JOIN, they often use Venn Diagrams to illustrate inclusion and exclusion of the two joined sets: While these Venn diagrams are certainly useful to understand (and remember) SQL JOIN syntax, they’re not entirely accurate, because SQL JOIN is a special type of a cartesian product, the CROSS JOIN. In a … Continue reading You Probably…