Reputation: 791
I have the following example table.
1 2 3
A| |B -- Row 1
| |A -- Row 2
|B| -- Row 3
| | -- Row 4
A|A|A -- Row 5
C| |C -- Row 6
My aim is to systematically filter 'out' rows (presumably using != WHERE clauses). I know how to filter 'in' rows using = WHERE clauses but am unsure if it is possible to filter 'out' rows. Please see the following three examples:
1) Filter out A (i.e. leave any rows containing B,C or blank):
--> This should return everything apart from row 5 because every other row contains at least 1 B, C or blank cell
2) Filter out A AND Blanks (i.e. leave any rows containing B or C):
--> This should return rows 1, 3, 6 because each of these rows contains a B or a C
3) Filter out blanks (i.e. leave any rows containing A,B or C):
--> Return rows 1, 2, 3, 5, 6 leaving out Row 4 because this does not include a A, B or a C
A function to generate a query using an = clause would be:
void filterInRows(String colFilter)
{
List uniqueCols = ['A', 'B', 'C', ''];
String query = 'SELECT * FROM Test WHERE ';
int filterCounter = 0;
uniqueCols.forEach((String col)
{
if(col != colFilter)
{
query += (filterCounter != 0) ? ' AND ' : '';
query += "One = '$col' OR Two = '$col' OR Three = '$col'";
filterCounter++;
}
});
query += ';';
}
For example, if we call filterInRows('A') then we get back:
SELECT * FROM Test WHERE One = 'B' OR Two = 'B' OR Three = 'B' AND One = 'C' OR Two = 'C' OR Three = 'C' AND One = '' OR Two = '' OR Three = '';
Which solves the first example above. However, I am stuck in writing a function/query that would allow me to achieve the same thing but filtering rows out rather than in. Queries such as the following fail to achieve this for obvious reasons:
SELECT * FROM Test WHERE One != 'A' OR Two != 'A' OR Three != 'A';
SELECT * FROM Test WHERE One != 'A' AND Two != 'A' AND Three != 'A';
I am using SQLite. The code above is written in Dart but is just an example - happy for counter examples in any other language. My aim is to filter stuff out rather than filter stuff in. I believe you need to do this using != but I am open to another method using different keywords if they exist. Please let me know if I have not explained this problem clearly. Thanks in advance.
Upvotes: 1
Views: 2073
Reputation: 1271241
Use is not
:
SELECT *
FROM Test
WHERE One IS NOT 'A' AND Two IS NOT 'A' AND Three IS NOT 'A';
As the documentation explains, this is the NULL
-safe operator in SQLite:
The
IS
andIS NOT
operators work like=
and!=
except when one or both of the operands areNULL
. In this case, if both operands areNULL
, then theIS
operator evaluates to 1 (true) and theIS NOT
operator evaluates to 0 (false). If one operand is NULL and the other is not, then theIS
operator evaluates to 0 (false) and theIS NOT
operator is 1 (true).
In ANSI SQL, IS
is IS NOT DISTINCT FROM
. Some databases use <=>
.
Upvotes: 2