Member Avatar for gunnarflax

Let's say that I want to retrieve rows from just one table and exclude rows with certain ID's.
Hypothetical table:

+----+-------+------+
| id | value | type |
+----+-------+------+
| 1  |  foo  | cool |
+----+-------+------+
| 2  |  bar  | cool |
+----+-------+------+
| 3  |  lor  | cool |
+----+-------+------+
| 4  |  ips  | cool |
+----+-------+------+
| 5  |  asd  | cool |
+----+-------+------+
| 6  |  qwe  | warm |
+----+-------+------+

The rows I want is the rows that are of the type "cool" and id not equal to 2, 3 and 5. How would I write this?
My guess so far has been something like this, but it doesn't work:

SELECT * FROM table WHERE type='cool' AND id != 2,3,5

How can I achieve what I want in the most efficient way?
All replies are greatly appreciated!

Thank you!

  • 2 Contributors
  • 2 Replies
  • 112 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by gunnarflax

Dani AI

Generated

Short answer: was right — you need a membership/anti‑membership test instead of trying to hand a comma list to an inequality operator. The original failed because an operator like != or <> accepts a single value, not a comma-separated list. For a tiny, fixed set of IDs the simplest options are fine; for dynamic or large exclusion sets use an anti-join pattern.

A couple of practical approaches:

Use explicit inequality checks if the exclusion list is small and fixed (also avoid SELECT * in production):

SELECT id, value, type
FROM mytable
WHERE type = 'cool'
  AND id <> 2 AND id <> 3 AND id <> 5;

If the IDs you want to exclude are kept in another table (recommended for maintainability when the list can grow), prefer an anti-join. Two common patterns:

-- correlated NOT EXISTS
SELECT t.id, t.value, t.type
FROM mytable t
WHERE t.type = 'cool'
  AND NOT EXISTS (
    SELECT 1 FROM excluded_ids e WHERE e.id = t.id
  );
-- LEFT JOIN / IS NULL anti-join
SELECT t.id, t.value
FROM mytable t
LEFT JOIN excluded_ids e ON e.id = t.id
WHERE t.type = 'cool' AND e.id IS NULL;

Performance and gotchas:

  • If you use a subquery with membership operators, NULLs in the subquery can change results; NOT EXISTS avoids that pitfall.
  • Make sure relevant columns are indexed. If id is primary key it’s already indexed; adding an index on type or a composite index can help:
    CREATE INDEX idx_type_id ON mytable(type, id);
  • Use EXPLAIN to confirm the query uses indexes and to compare plans.

Summary: for the small literal list the simple membership/inequality solution is fine (what suggested). For anything dynamic or large, store excluded IDs in a table and use NOT EXISTS or a LEFT JOIN anti-join, plus proper indexes and EXPLAIN-driven tuning.

Recommended Answers

All 2 Replies

Member Avatar for hielo

try:

SELECT * FROM table WHERE type='cool' AND (id NOT IN(2,3,5) )
Member Avatar for gunnarflax

Thank you! I think that solved it, I'll let you know if it doesn't work :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.