Menu
If I have a list of items, say
apples
pairs
pomegranites
and I want to identify any that don't exist in the 'fruit' column in an SQL DB table.
I can think of a few ways to do it, thought I'd throw it out there and see what you folks think.
456
To test whether a row exists in a MySQL table or not, use exists condition. The exists condition can be used with subquery. It returns true when row exists in the table, otherwise false is returned. True is represented in the form of 1 and false is represented as 0.
To check whether a particular value exists in the database, you simply have to run just a regular SELECT query, fetch a row and see whether anything has been fetched. Here we are selecting a row matching our criteria, then fetching it and then checking whether anything has been selected or not.
Since the list of fruits you are selecting from can be arbitrarily long, I would suggest the following:
create table FruitList (FruitName char(30))
insert into FruitList values ('apples'), ('pears'), ('oranges')
select * from FruitList left outer join AllFruits on AllFruits.fruit = FruitList.FruitName
where AllFruits.fruit is null
A left outer join should be much faster than "not in" or other kinds of queries.
174
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
