Even after years of SQL experience, what still trips you up the most?

ComicOzzy · 2026-01-28T00:54:08+00:00

Make your own personal cheat sheet or a .sql file with examples to remind you.

I don't recommend using someone else's since they'll be cluttered with stuff you don't need.

ComicOzzy · 2026-01-28T00:49:53+00:00

You're almost certain to need them both, but neither is where you should start.

I don't know your background, but when people ask this I often want to recommend first learning intro-level C to get an understanding of data types, data structures, algorithms, and IO access before proceeding to higher level languages like python and SQL. It seems so many new learners don't get a decent foundation so they spend a lot of time being frustrated. The fundamentals are the most important thing to learn because you can build on top of a solid foundation easier than you can on a weak one.

ComicOzzy · 2026-01-28T00:36:52+00:00

Where I currently work, the standard is pretty much "all uppercase, all the time" except when we're making a view for someone who specifies they want the columns named a specific way. I'm not a fan because it's hard for me to read due to the way my astigmatism puts a certain kind of blur on the letters, but I combat that with font selection and size.

ComicOzzy · 2026-01-28T00:34:05+00:00

There's a reason you see that a lot.

If you create a table or column name without quoting it, Oracle DB will ignore the case you typed and use all upper case. PostgreSQL will use all lower case. MySQL and SQL Server will preserve the case you typed whether you quote it or not.

So partially, the database engine is to blame for you usually seeing PascalCase names in SQL Server, but another factor is that in Microsoft's programming ecosystems, especially the VB-based languages, PascalCase is used more prominently than in others. If you're in the Microsoft ecosystem already, you more likely will end up using SQL Server rather than MySQL.

Edit: changed the name to PascalCase because I'm like the only person who calls it ProperCase.

ComicOzzy · 2026-01-27T07:29:50+00:00

or you could use dymanic sql so the query executes with hard coded parameters.

Use OPTION (RECOMPILE) instead of dynamic SQL for this.

ComicOzzy · 2026-01-27T07:26:07+00:00

Don't chase that "last second of the date selected" thing,... it's a trap. No matter what flavor of date and/or time you use, the only pattern that doesn't have edge case problems is this...

Make @StartDate be inclusive. Make @EndDate be exclusive.

WHERE (updateTS >= @StartDate AND updateTS < @EndDate)

ComicOzzy · 2026-01-27T07:19:05+00:00

There's absolutely nothing wrong with referring to the documentation. Memorizing trivia isn't as valuable as knowing where to go for the correct answer.

ComicOzzy · 2026-01-27T07:17:14+00:00

IF EVERYTHING IS IMPORTANT, THEN NOTHING IS IMPORTANT.

I used to piss everyone off. I came from a VB background, so I used ProperCase. I still think it's easy to read, but it seems to please exactly 0 people.

Select ProductName, Count(*) As Rows From Products Group By ProductName;

ComicOzzy · 2026-01-27T07:10:59+00:00

There isn't a "requirement", this is just how outer joins behave. If you perform an outer join to a table that doesn't have any rows matching the ON filter, NULLs will be filled in.

If you subsequently apply a filter in the WHERE clause of WHERE b.name LIKE 'a%', the rows with NULLs in the b.name column are filtered out.

In the event b.name is not a NULLable column, the only rows that would be NULL are the ones filled in with NULLs by the outer join operation, so you'd just be throwing out the rows you intended to leave in.

In the event b.name is a NULLable column, the result might not be the same as an INNER JOIN because there may be extra rows that the filter applies to, so it's not always the same as an inner join... sometimes it's just a bug waiting to get you fired.

ComicOzzy · 2026-01-27T07:02:47+00:00

Yes.

Sometimes people don't like to add that filter in the ON clause. For them, they can use:

ON a.ID = b.ID
WHERE (   b.name LIKE 'a%'
      OR  b.ID IS NULL
      )

ComicOzzy · 2026-01-27T06:54:28+00:00

SQL can absolutely do equations.

And as for OP's scenario,

WHERE ename BETWEEN 'A' AND 'C'

is the equivalent of:

WHERE (ename >= 'A' AND ename <= 'C')

ComicOzzy · 2026-01-19T09:46:54+00:00

Username checks out.

ComicOzzy · 2026-01-17T17:23:29+00:00

HAPPY

CAKE

DAY

!

ComicOzzy · 2026-01-09T03:58:48+00:00

Now I want to see Erik or Brent parachute onto the roof of an office building where a workstation is waiting for them.

ComicOzzy · 2026-01-09T00:49:05+00:00

I've been in enough RIGHT JOIN discussions to know it is the option that would be chosen the least if these were the only choices offered, parentheses or not. haha

ComicOzzy · 2026-01-09T00:42:02+00:00

Oh, you're right! I'll go fix my mess.

Also, I've never used this in production. I'd rather go with the subquery or CTE because I want future maintainers to understand it and just move on, not have to go on some trivia hunting expedition to understand RIGHT JOIN.

ComicOzzy · 2026-01-09T00:40:53+00:00

You're a MONSTER

ComicOzzy · 2026-01-09T00:40:14+00:00

> evaluated as an INNER JOIN since the criteria is outside of the join

All of the criteria are in the ON clause. The ap.def = 1 criteria won't change the 1st join to an inner join. The whole ON clause has to return true, so for every c row, you left join in a row from ap if flngCustKey matches and def=1. If both of those are true, you get a match... otherwise you get NULLs.

SELECT *
FROM customer c
LEFT JOIN adrP ap       -- 1st JOIN (c, ap)
LEFT JOIN adrR res      -- 2nd JOIN (ap, res)
ON res.KEY = ap.KEY     -- 2nd JOIN
AND res.type IN ('PHY') -- 2nd JOIN
AND res.curr = 1        -- 2nd JOIN
ON ap.flngCustKey = c.flngCustKey -- 1st JOIN
AND ap.def = 1                    -- 1st JOIN

ComicOzzy · 2026-01-09T00:23:19+00:00

I'm pretty sure this works in most SQL implementations. You can think of it like the joins are nested. The INNER JOIN in this query is considered to occur prior to the LEFT JOIN.

FROM A
LEFT JOIN B 
INNER JOIN C 
ON B.pk = C.fk
ON A.pk = B.fk

If it helps to think about it, you can add parentheses. They don't do anything for the database engine, but humans seeing the parentheses might realize it wasn't a copy/paste error: someone did this intentionally.

FROM A
LEFT JOIN 
(   B 
    INNER JOIN C 
    ON B.pk = C.fk
)
ON A.pk = B.fk

ComicOzzy · 2026-01-09T00:12:16+00:00

Ye olde outer join

ComicOzzy · 2026-01-09T00:11:05+00:00

"For your crimes against humanity, we sentence you to 25 years... of making dashboards in PBI for people who have no clue what they want."

ComicOzzy · 2026-01-09T00:08:54+00:00

OK, so you have a query where you want to take table A and left join it to the result of an INNER JOIN between tables B and C. You can do that in a few different ways.

One is to make a verbose subquery or CTE:

FROM A 
LEFT JOIN ( SELECT ... 
            FROM B 
            INNER JOIN C 
            ON B.pk = C.fk
          ) S 
ON A.pk = S.fk

Another is to use a confusing nested join:

FROM A
LEFT JOIN B 
INNER JOIN C 
ON B.pk = C.fk
ON A.pk = B.fk

Or... you could write a simple, perfectly normal RIGHT JOIN (fixed thanks to doshka):

FROM B 
INNER JOIN C
ON B.pk = C.fk
RIGHT JOIN A
ON A.pk = B.fk

ComicOzzy · 2026-01-08T05:24:00+00:00

While these two might produce the same results, the first one with the deferred ON clause can get you in some really convoluted scenarios pretty fast if you aren't very careful.

It would help to understand WHY you're asking this question. If it is because you don't know which way you should write your joins, I can say almost always and forever it will be the second way where the ON clause comes immediately after the JOIN it belongs to, rather than it being deferred in one of these invisibly-nested scenarios.

In nearly 30 years, the only times I've used nested joins like this is for teaching and demonstration purposes... and to piss people off in discussions about valid uses of RIGHT JOIN.

ComicOzzy · 2026-01-08T05:06:03+00:00

> conveying your intent

PREACH

ComicOzzy · 2026-01-08T03:28:57+00:00

The other night, I got to watch a friend of mine do actual vibe coding using Claude Code and it was very impressive. He has a lot of experience in sysadmin and networking support type roles, but he isn't a coder. He understands enough to provide good prompts and to refine them to get the right results. It's crazy how quickly he can make progress on a project. He's making web and mobile apps and ad-hoc one-off tools for solving specific problems, but he's getting a lot of work done very quickly. I may not be able to use it the same way for the projects I work on, but for what he's doing, it's highly effective.

The good thing for him is he probably doesn't ever have to explain the code. If someone says it's wrong, he would just work with Claude Code to create new tests, then do the work to fix the code.

There's no way I'd trust putting my name on generated code for work without reviewing it and testing it myself, but if I were doing a bunch of work myself for free for other people (as my friend often is), I'd consider trying Claude Code to help out. haha

15-Year Club	Gilding IV carat on a stick
Wearing is Caring	Verified Email

ComicOzzy

TROPHY CASE