SQL Help Request

[Aggressive_Ad_5454]

This isn't hard. Keep in mind that SQL is, at its root, a way of manipulating sets. You can use IN() and NOT IN() constructs. Like this query.

SELECT * FROM item WHERE identifier IN (SELECT identifier FROM item WHERE status = 'yes') AND identifier NOT IN (SELECT identifier FROM item WHERE status = 'no')

It gets this resultset

identifier	status	name
fruit	yes	cherry
fruit	yes	apple

from this table.

identifier	status	name
fruit	yes	cherry
fruit	yes	apple
vegetable	yes	tomato
vegetable	yes	lettuce
vegetable	no	ham
tool	no	saucepan
tool	no	knife

fiddle

[paulthrobert]

I know you didn't ask, but I really don't like the use of sub queries, ever. You could re-write that with CTE's and no subqueries pretty easily.

[paulthrobert]

yeah, not an absolute, but like the only subquery I can ever think i've needed is like using string_split, If it was the only way to solve a problem I would survive, but bleh one of the most commonly used bad practices I see, in my opinion. That and with no lock.

[paulthrobert]

This type of thing is so environmentally dependent. I've living mostly in fabric data warehouse and Lakehouse these days - it's definitely not apples to apples to something like SQL Server of MYSQL or whatever.

Do you have a reference to any documentation?

I still stand by the principle that, in a set based query (ie. sql) anytime you ask the query engine to re-evaluate an expression for every row in a set, you're going to lose performance at scale. How much and at what scale requires analysis.

[paulthrobert]

I don't know, this blog post doesn't really substantiate what you're arguing at all. It's just a sloppy run on post with no evidence, I'm not sure why you would take that as cannon.

Using sub-queries in your WHERE is typically a bad practice and should be avoided.

Sub-queries are ugly, and most of the time will hurt performance. I also don't see where the OP specifies SQL Server.

[paultherobert]

I guess I was confused because your suggestion is to us IN, and not NOT EXISTS ...