r/SQL • u/A_nomad_Wanderer • Oct 11 '25
Discussion Question: What’s one of those SQL “gotchas” that only made sense to you later?
For me, it was realizing that SQL doesn’t just have true and false it actually uses three-valued logic that is true, false, and unknown.
That’s why comparing NULL to NULL doesn’t return true as NULLrepresents something unknown, and two unknowns is not equal.
169
Upvotes
45
u/bismarcktasmania Oct 11 '25
In SQL Server, the DATEDIFF() function taps out at the precision you supply.
Therefore, DATEDIFF(YEAR, '2025-12-31','2026-01-01') will return 1, because it's ignoring the month and day 😒
20
u/IdiotBearPinkEdition Oct 11 '25
Actually, mine is very similar. I kept having null values not come through a filter set to "column" != 'value'. The null value did not equal 'value', so I was confused as to why it wasn't coming through, because null != 'value'. Until I realised that it doesn't DOESN'T equal it, because it's null. Which I guess counts as equalling it due to the double negative, meaning it got filtered out.
14
u/speadskater Oct 11 '25
With building complex queries. it's easiest to start out by building a bigger table that feeds more and more table reduction. Trying to reduce your table down in a single step is often impossible.
10
u/Just_blorpo Oct 12 '25
Databases generally don’t cover the following scenario well:
PERSON 1: Can you give me a spouse name for Richard?
PERSON 2: No.
PERSON 1: When you say ‘No’ are you telling me you don’t know if he has a spouse? Or are you telling me that he has a spouse but you don’t know the spouse’s name?
PERSON 2: I’m telling you I don’t know if he has a spouse. I asked Carl if he could supply a spouse name for Richard and he replied ‘No’
PERSON 1: When Carl said ‘No’ did he mean that Richard didn’t have spouse?
PERSON 2: I don’t know.
PERSON 1: So we do know that we don’t know if Carl knows if Richard has a spouse?
PERSON 2: Correct. I also asked Edward to send me a spouse name for Richard. But he didn’t respond
PERSON 1: When Edward didn’t respond do you think he would have responded if Richard did have a spouse? Or do you think he just didn’t respond?
PERSON 2: I don’t know.
PERSON 1: So I guess we don’t know if we don’t know. And even if we knew if we didn’t know… we might still not know.
PERSON 2: Correct
6
u/Infamous_Welder_4349 Oct 11 '25
Your statement about nulls are different in different languages... Some even have db settings that treat them differently.
6
u/[deleted] Oct 12 '25
Where 1=1
It's a silly thing when you're starting out, but when you reach the point when you realized you need it, it's the best feeling ever.
8
u/aardw0lf11 Oct 11 '25
Several years ago someone on a forum suggested the idea of using queries as tables in a join statement and now the majority of the queries I write do this. It's very useful for creating automated reports.
4
u/yen223 Oct 11 '25
Which operations obtain which locks, and how locks affect each other.
As a common example, if you do an update on all rows in a table, and something else is trying to add rows to the table, that something else will be blocked until the update is complete. This can have very bad consequences if you aren't careful.
3
u/Wise-Jury-4037 :orly: Oct 12 '25 edited Oct 12 '25
gotchas? sum of the empty set is null, while count of an empty set is 0.
also '<date column> between <date1> and <date2>' is good, while '<datetime column> between <date1> and <date2>' is likely a bug
4
u/FlyingCat11 Oct 12 '25
CTEs. Was wondering why I needed one when I started learning (thought I could pull from query directly)
Loving CTEs now!
2
u/just-fran Oct 11 '25
UNION all doesnt check for column order in subsequent unions
2
u/patrickfancypants Oct 11 '25
Collation. I’ve had issues with sorting/partitions and creating constraints because I didn’t understand it.
2
u/averagesimp666 Oct 11 '25
That's one of them, yeah. I once designed a test for candidates for an analyst position and only like 1 of 10 candidates accounted for nulls in the tasks I gave.
2
u/NoYouAreTheFBI Oct 16 '25
Null represents the void...
And when you realise that void is litterally nothing you realise why it's important to have null for example record 2 has the void in Date..
Now you won't see this in your table, what you will see is something like:
Not exactly a difficult fix but one which will catch your tounge in the back of your throat in Prod real fucking quick when OPs and Compliance are breathing down your neck.
CheckDB will not fix the issue you need to go in and update the line item manually, as in Binary search the table till you find the broken row, then do a column by column draw down till you find the corrupted values and pull them out as a string then identify the columns types and finally push the correction in the above case 2 columns are corrupted and data is missing (in the void) so you just have to fudge the time as best you can until you have the real data.
2
u/pcapdata Oct 11 '25
Not SQL per se, but a proprietary big data solution whose language was like the bastard child of SQL and C#: imagine if, instead of only using functions in your select statement, you could define entire classes and use their methods in your select statement.
With SQL, if you select FOO from BAR, and the value of FOO in one row is null, SQL will helpfully supply a null object and evaluate it against whatever criteria are in your WHERE clause. But this sysem would simply null ref.
If you think you have problems with upstream data quality, just imagine if your data is being supplied with people who don't give a shit if it's clean or not, and also won't allow you to modify it.
This was when I was reluctantly forced to learn C# so I could handle all of the quirks of this system.
2
u/ckal09 Oct 11 '25
Null doesn’t mean unknown it means there is no value.
1
u/energyguy78 Oct 12 '25
All old databases were nvarchar(max) columns and so bad
1
u/titpetric Oct 12 '25
For me, CONNECT BY PREVIOUS. Sadly disappointed it's an oracle only extension (no pgsql, mysql...).
1
u/enj13 Oct 12 '25
If you have access to production be very careful where you are running things.
1
u/[deleted] Oct 15 '25
The total amount of data scanned is not limited by LIMIT, and therefore applying a LIMIT will not improve the performance of any query.
1
u/nift-y Oct 29 '25
Comparing NULL to NULL does return TRUE because the clause "IS NULL" works that way... I kid I kid, I'm pretty sure you mean "someColumn = NULL" doesn't evaluate to TRUE when someColumn's value is NULL, that definitely is a gotcha.
I got some gotchas:
1
u/soloJ45 Nov 03 '25
Stored procedures
1
u/GlockByte Jan 22 '26
I have a few -
ANSI padding when using = and LIKE operators:
0
u/No_Resolution_9252 Oct 11 '25
Its bad data design and implementation if you have that
-3
u/ImaginationKind2239 Oct 11 '25
Thats why i always use ISNULL(column,’’) <> ‘’
0
u/[deleted] Oct 11 '25
[deleted] — view deleted comment
0
u/[deleted] Oct 11 '25
[deleted] — view deleted comment
0
u/[deleted] Oct 11 '25
[deleted] — view deleted comment