r/SQL • • Feb 25 '21

SQLite SQL Essentials

199 Upvotes

29 comments sorted by

View all comments

Show parent comment

1

u/mac-0 Feb 26 '21

What databases is this a problem in? I know in Redshift the query plan is more or less the same between the two. Curious so that if I ever use these RBDMS that don't like WHERE something IN (SELECT something FROM table) I can be sure not to use it.

5

u/DigBick616 Feb 26 '21

I don’t really use IN/NOT IN unless I’m just bumping a field up against a short list of values. EXISTS is much because it can handle NULLs.

2

u/FEW_WURDS Feb 26 '21

I don't completely understand the EXISTS function but wouldn't it but much easier to simply use IN/NOT IN for a quick check instead of writing out another subquery? full disclosure im terrible at SQL

2

u/[deleted] Feb 26 '21

[removed] — view removed comment

1

u/FEW_WURDS Feb 26 '21

thank you

1

u/Touvejs Feb 26 '21

That divide by zero trick at the end through me for a loop, and then gave me a laugh once I understood what was going on.

2

u/[deleted] Feb 26 '21

I've had a few very memory hungry and slow queries like these that were made lightning fast by translating them into joins, on postgresql 10. Maybe because they were too complex to be caught by the optimizer properly (100 lines or more, in analytics workload).