I'm need to exclude some rows by id from SQL query results, but which way will be better to do this?
Using NOT IN by primary key id:
SELECT id, key1, key2 FROM my_table WHERE id NOT IN (#EXCLUDE_IDS)
Or in the post-processed loop after query in the script:
foreach ($rows as $i => $row) {
if (isset($exclude[$row['id']])) {
unset($rows[$i]);
}
}
# or
for ($i = count($rows) - 1; $i >= 0; $i--) {
if (isset($exclude[$rows[$i]['id']])) {
unset($rows[$i]);
}
}
When querying from the database, you want to only get information that you require, which why you have the option of selecting the columns in your select statement with the ability to use the conditions in the where
In this case: 1. If you wish to exclude one specific primary key then
SELECT ID, KEY1, KEY2 FROM TABLE WHERE ID != 1
2. If it based on multiple ids then
SELECT ID, KEY1, KEY2 FROM TABLE WHERE ID NOT IN (ids)
3. If you wish the exclude to be based on a separate query, then
SELECT ID, KEY1, KEY2 FROM TABLE WHERE ID NOT IN(
SELECT ID
FROM TABLE2
WHERE ID IS NOT NULL )
An alternative to the NOT IN is 'NOT EXISTS'
You should fetch the data which is needed. So doing it in the query with excluding those ids is better option. It also decrease the execution and cost.
What you need is to use next clouse:
SELECT id, key1, key2 FROM my_table WHERE id NOT REGEXP '[regexp]';
Where regexp describes list of ids you want to exclude.
not inlists can be slow. But in this case probably not slower than processing it in PHP