r/learnSQL • • 4d ago

What are some interesting SQL problems or query mistakes worth discussing?

I'm planning to share some SQL-related content on LinkedIn, but I want to avoid the usual beginner-level tutorials or generic interview questions.

I'm particularly interested in intermediate-level SQL concepts, window functions, CTEs, tricky query behavior, or situations where a query looks correct but produces unexpected results.

I'd love to hear from people working with SQL regularly.

What's an interesting SQL problem, subtle query mistake, or unexpected behavior you've encountered that made you think twice?

Looking for something technically meaningful that could spark a good discussion rather than another basic SQL tutorial.

5 Upvotes

12 comments sorted by

1

u/Imaginary__Bar 4d ago

It's your post, you decide! Otherwise you're just posting for the sake of posting and that is simply pointless for everyone.

1

u/you_are_wrong_tho 4d ago
drop table if exists #temp

create table #temp (id int identity (1,1) not null, textValue varchar(200))


Insert into #temp (textValue)

values
('asdf'),
('asdfsdas'),
('asdf'),
('asdfsdas'),
('asdf'),
('asdfsdas'),
('asdf'),
('asdfsdas')

select * from #temp

truncate table #temp

Insert into #temp (textValue)

values
('asdf'),
('asdfsdas'),
('asdf'),
('asdfsdas'),
('asdf'),
('asdfsdas'),
('asdf'),
('asdfsdas')


select * from #temp


delete from #temp

Insert into #temp (textValue)

values
('asdf'),
('asdfsdas'),
('asdf'),
('asdfsdas'),
('asdf'),
('asdfsdas'),
('asdf'),
('asdfsdas')


select * from #temp

This behavior screwed up an ETL process for me once. So I thought, easy, just reseed!

1

u/you_are_wrong_tho 4d ago

Reseed then threw me for a loop. there was a mix of truncates and deletes in the proc, and a single row was SOMETIMES getting dropped (always the first row of the template being imported).

1

u/plat_sil 4d ago

Is this related to high watermark thing ?

1

u/amuseboucheplease 4d ago

Can you explain what's happening /not happening correctly here?

1

u/you_are_wrong_tho 4d ago

when you truncate a table (instead of deleting/dropping then recreating) it starts the identity column where it left off. so you think, ill just reseed to 0, then you can see what happens in different scenarios in the comment below.

1

u/amuseboucheplease 4d ago

Ahh thank you. Is this typically a problem for temp tables?

1

u/you_are_wrong_tho 4d ago

Not just temp tables, any table

1

u/amuseboucheplease 4d ago

Why would ID be used in temp table though?

1

u/you_are_wrong_tho 4d ago

to have an id to batch through during an insert/upsert/delete etc

1

u/amuseboucheplease 4d ago

You wouldn't do that through a cte?

1

u/you_are_wrong_tho 4d ago

as is the answer with many things sql, it depends. but generally no I would not use a cte to do batching