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: CREATE TABLE actor ( actor_id integer NOT … Continue reading A Rarely Seen, but Useful SQL Feature: CORRESPONDING
Tag: Set operations
How to Use SQL INTERSECT to Work Around SQL’s NULL Logic
ANOTHER SQL Post this week? I got nerd-sniped: https://twitter.com/rafaelcodes/status/870574075244158980 Oooooh, challenge accepted! So, let's assume we have a table T with columns (A, B, C) like this: WITH t(a, b, c) AS ( SELECT 'a', 'b', null FROM dual UNION ALL SELECT 'a', null, 'c' FROM dual UNION ALL SELECT 'a', 'b', 'c' FROM dual … Continue reading How to Use SQL INTERSECT to Work Around SQL’s NULL Logic
Say NO to Venn Diagrams When Explaining JOINs
In recent times, there have been a couple of tremendously popular blog posts explaining JOINs using Venn Diagrams. After all, relational algebra and SQL are set oriented theories and languages, so it only makes sense to illustrate set operations like JOINs using Venn Diagrams. Right? Google seems to say so: Everyone uses Venn Diagrams to … Continue reading Say NO to Venn Diagrams When Explaining JOINs
You Probably don’t Use SQL INTERSECT or EXCEPT Often Enough
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 don’t Use SQL INTERSECT or EXCEPT Often Enough