A lot of developers get the distinction between JOIN and SEMI-JOIN wrong. Let me explain... What are JOIN and SEMI-JOIN A little bit of relational algebra first. What is an (INNER) JOIN? An JOIN is nothing but a filtered cartesian product. And what is a cartesian product? Wikipedia explains this very nicely: for sets A … Continue reading SQL JOIN or EXISTS? Chances Are, You’re Doing it Wrong
Tag: cartesian product
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
CROSS JOIN, a nice example for a rarely used operation
In 95% of the cases, cartesian products originate from accidental cross join operations and cause unnecessary high load on a database. Maybe the results aren't even wrong, as someone may have applied a UNION or a DISTINCT keyword, to remove unwanted duplicates. But there are those 5% of SQL queries, where the cartesian product is … Continue reading CROSS JOIN, a nice example for a rarely used operation