MYSQL - not equal joins not working properly

Question

I'm having trouble getting a query to work properly. I feel that this should be easy but for some reason I can't get it correct.

I have two tables joined by an ID field. I'm trying to get all the records that are in t1 and don't show up in t2.

This works currently:

select * from at_templates a
left join at_vault b on a.id = b.template
where  b.at_id is null

BUT, I also want to put another condition in the query to limit the data to a subset and it is not working:

select * from at_templates a
left join at_vault b on a.id = b.template
where  b.at_id != 1

The second query comes up empty but I want the same results as the first, based upon the input of at_id.

Any ideas?

It's not clear to me what "the same results as the first, based upon the input of at_id" means. You seem to be saying that you are want a query that will return a subset of the results your first query gets. You cannot do that by adding an additional condition on b.at_id (because the current one is already as selective as can be) or by changing the current one (because then you won't get a subset). — John Bollinger, Oct 08 '14 at 21:23

score 1 · Answer 1 · edited May 23 '17 at 11:57

Your working example implies that the "first table" you want to see records from is a and the "second table" you want to use to exclude records is b. If you are excluding all records that exist in b, then you can't further limit the result set by any value like b.at_id because there are no values associated with b in your result set.

Additionally, if the condition b.at_id is null is true, the condition b.at_id != 1 will never be true because an inequality comparison with null will always return null. (The reason for this is that null is not a value; it is a placeholder indicating the absence of a value.)

If you want to get the same results from both queries, based on a comparison between some user input parameter and the field b.at_id (and noting that your second query currently returns an empty set), you might be able to use MySQL's null-safe equality operator in the following way:

SELECT
  *
FROM
  at_templates AS a
    LEFT JOIN
  at_vault AS b ON a.id = b.template
WHERE NOT b.at_id <=> 1;

This is a MySQL extension, not a standard syntax; unfortunately the ANSI SQL standard syntax, IS [NOT] DISTINCT FROM, doesn't appear to be widely supported. Some alternate ways to rewrite this condition are discussed in How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM?.

Keep in mind that if in the future you have some values of b.at_id that are not 1, this query would return those rows as well, and not just the rows returned by your first query.

MYSQL - not equal joins not working properly

1 Answers1