Postgresql 'Not In' and Subquery
I'm Trying to Execute This Query: Select Mac, Creation_Date from Logs Where Logs_Type_Id=11 and Mac Not in (Select Consols. Mac from Consols) but I Get No...
I'm trying to execute this query:
SELECT mac, creation_date
FROM logs
WHERE logs_type_id=11
AND mac NOT IN (select consols.mac from consols)
But I get no results. I tested it, and I know that there is something wrong with the syntax. In MySQL such a query works perfectly. I've added a row to be sure that there is one mac which does not exist in the consols table, but still it isn't giving any results.
3 Answers
When using NOT IN you should ensure that none of the values are NULL:
SELECT mac, creation_date
FROM logs
WHERE logs_type_id=11
AND mac NOT IN (
SELECT mac
FROM consols
WHERE mac IS NOT NULL -- add this
)
When using NOT IN, you should also consider NOT EXISTS, which handles the null cases silently. See also PostgreSQL Wiki
SELECT mac, creation_date
FROM logs lo
WHERE logs_type_id=11
AND NOT EXISTS (
SELECT *
FROM consols nx
WHERE nx.mac = lo.mac
);
You could also use a LEFT JOIN and IS NULL condition:
SELECT
mac,
creation_date
FROM
logs
LEFT JOIN consols ON logs.mac = consols.mac
WHERE
logs_type_id=11
AND
consols.mac IS NULL;
An index on the "mac" columns might improve performance.
Elena Rostova holds a Master's degree in Public Health Journalism. She covers groundbreaking medical research, holistic wellness trends, mental health awareness, and nutritional science.
