There are a few ways to compare two similar tables in SQL. Assuming PostgreSQL syntax, we might have this schema: CREATE TABLE t1 (a INT, b INT, c INT); CREATE TABLE t2 (a INT, b INT, c INT); INSERT INTO t1 VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9); INSERT INTO t2 VALUES … Continue reading Use NATURAL FULL JOIN to compare two tables in SQL
Tag: null
5 Things You May Not Have Known About jOOQ
jOOQ has been around for a while now (since 2009!) and by now we can say we've seen quite a bit of things about the SQL and Java languages. Some of our design decisions are particular in the way jOOQ thinks about programming with SQL. These include: Nullability (let's stop fighting it) Value types (let's … Continue reading 5 Things You May Not Have Known About jOOQ
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
The Java Ecosystem’s Obsession with NonNull Annotations
I'm not well known for my love of annotations. While I do recognise that they can serve a very limited purpose in some areas (e.g. hinting stuff to the compiler or extending the language where we don't want new keywords), I certainly don't think they were ever meant to be used for API design. "unfortunately" … Continue reading The Java Ecosystem’s Obsession with NonNull Annotations
Ceylon Might Just be the Only (JVM) Language that Got Nulls Right
Here we go again. THAT TOPIC. But hang on. The approach discussed here (and in the Ceylon language) is not something you see every day. At the same time, it is very cunning. Nulls are baked into the language ... or so it may seem. Indeed, in Ceylon, like in Kotlin (and possibly many other … Continue reading Ceylon Might Just be the Only (JVM) Language that Got Nulls Right
Divided we Stand: Optional
Our recent article "NULL is Not The Billion Dollar Mistake. A Counter-Rant" got us a lot of reads, controversial comments, and a 50/50 upvote / downvote ratio pretty much everywhere a blog post can be posted and voted on. This was expected. Objectively, NULL is just a "special" value that has been implemented in a … Continue reading Divided we Stand: Optional
NULL is Not The Billion Dollar Mistake. A Counter-Rant
A short while ago, I gave this answer on Quora. The question was "What is the significance of NULL in SQL?" and most of the existing answers went on about citing C.J. Date or Tony Hoare and unanimously declared NULL as "evil". So, everyone rants about NULL all the time. Let me counter-rant. Academics Of … Continue reading NULL is Not The Billion Dollar Mistake. A Counter-Rant
Yet Another 10 Common Mistakes Java Developers Make When Writing SQL (You Won’t BELIEVE the Last One)
(Sorry for that click-bait heading. Couldn't resist ;-) ) We're on a mission. To teach you SQL. But mostly, we want to teach you how to appreciate SQL. You'll love it! Getting SQL right or wrong shouldn't be about that You're-Doing-It-Wrong™ attitude that can be encountered often when evangelists promote their object of evangelism. Getting … Continue reading Yet Another 10 Common Mistakes Java Developers Make When Writing SQL (You Won’t BELIEVE the Last One)
The Index You’ve Added is Useless. Why?
Recently, at the office: Bob: I've looked into that slow query you've told me about yesterday, Alice. I've added the indexes you wanted. Everything should be fine now Alice: Thanks Bob. I'll quickly check ... Nope Bob, still slow, it didn't seem to work Bob: You're right Alice! It looks like Oracle isn't picking up … Continue reading The Index You’ve Added is Useless. Why?
Three-State Booleans in Java
Every now and then, I miss SQL's three-valued BOOLEAN semantics in Java. In SQL, we have: TRUE FALSE UNKNOWN (also known as NULL) Every now and then, I find myself in a situation where I wish I could also express this UNKNOWN or UNINITIALISED semantics in Java, when plain true and false aren't enough. Implementing … Continue reading Three-State Booleans in Java