MySQL NOT IN from another column in the same table

Question

I want to run a mysql query to select all rows from a table films where the value of the title column does not exist anywhere in all the values of another column (collection).

Here is a simplified version of my table with content:

mysql> select * from films; +----+--------------+--------------+ | id | title | collection | +----+--------------+--------------+ | 1 | Collection 1 | NULL | | 2 | Film 1 | NULL | | 3 | Film 2 | Collection 1 | +----+--------------+--------------+

Here is my query:

mysql> SELECT * FROM films WHERE title NOT IN (SELECT collection FROM films); Empty set (0.00 sec)

In this example, I would want to select the rows with titles Film 1 and Film 2, but my query is returning no rows.

Here is the table structure:

CREATE TABLE `films` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(200) NOT NULL DEFAULT '', `collection` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM;

The reason you're not retuning any rows is because NULL isn't being considered in the result set. I have a sneaking suspicion if you replace NULL with an empty string, this may fix your problem. — Joshua Burns
– Joshua Burns, Commented Jan 9, 2013 at 20:19
@JoshuaBurns I tested this and it doesn't seem to be the issue. — Kermit
– Kermit, Commented Jan 9, 2013 at 20:20

user330315 · Accepted Answer · 2013-01-09 20:27:53Z

17

SELECT * FROM films WHERE title NOT IN (SELECT collection FROM films where collection is not null);

SQLFiddle: http://sqlfiddle.com/#!2/76278/1

answered Jan 9, 2013 at 20:27

user330315

Sign up to request clarification or add additional context in comments.

3 Comments

Taryn Over a year ago

+1 I think you posted this while I was editing mine with the IN version as well

jessica Over a year ago

Thanks, I'm accepting yours because it's the first correct answer (and the one I'm going to use). Could you explain why the is not null makes it work?

thang Over a year ago

is not null is needed because the semantic specification of not in requires that the set can't contain any null element. See documentation at dev.mysql.com/doc/refman/5.0/en/comparison-operators.html where it says To comply with the SQL standard, IN returns NULL not only if the expression on the left hand side is NULL, but also if no match is found in the list and one of the expressions in the list is NULL.

Community · Accepted Answer · 2017-05-23 11:54:16Z

Have you tried using NOT EXISTS:

SELECT * FROM films f1 WHERE NOT EXISTS (SELECT collection FROM films f2 WHERE f1.title = f2.collection);

See SQL Fiddle with Demo

If you want to use IN then you will want to look for values that are NOT NULL:

SELECT * FROM films WHERE title NOT IN (SELECT collection FROM films WHERE collection is not null);

See SQL Fiddle with Demo

The result for both is:

| ID | TITLE | COLLECTION | ------------------------------ | 2 | Film 1 | (null) | | 3 | Film 2 | Collection 1 |

The problem with your current query is that -- stealing from @Quassnoi's answer here:

Both IN and NOT IN return NULL which is not an acceptable condition for WHERE clause.

Since the null value is being returned by your subquery you want to specifically exclude it.

@jessica you requested additional details on why you need to filter out null values from the subquery -- see this link or this one. Or even this question answered by the same person who wrote those articles.

EvilBob22 · Accepted Answer · 2013-01-09 20:33:54Z

Another option using an outer join

SELECT f.* FROM films f LEFT OUTER JOIN films ff ON f.title = ff.collection WHERE ff.collection IS NULL

bonCodigo · Accepted Answer · 2013-01-09 20:58:05Z

Try this please:

SQLFIDDLE DEMO

Query:

select a.id, a.planid from one a left join one b on a.planid <> b.iid where not (b.iid is null) group by b.id ;

Results: based on the sample table I used.

ID PLANID t15 1 j18 2

EDIT TO ADD : HERE WITH OP SCHEMA

select b.id, b.title from opschema b inner join opschema a on b.title <> a.collection or b.collection <> a.title group by b.id ;

OP SHCEMA SQLFIDDLE DEMO

ID TITLE 2 Film 1 3 Film 2

@njk my logic is correct though :) I have also added OP's schema.
@Jessica please give a try on this one as well and comment. :)

Lucas Zamboulis · Accepted Answer · 2014-08-28 12:59:27Z

CREATE TABLE IF NOT EXISTS `reservation_tables` ( `res_table_id` int(10) NOT NULL AUTO_INCREMENT, `res_table_name` int(10) NOT NULL, `date_time` varchar(20) NOT NULL, `partyhall_id` int(10) NOT NULL, `flag` enum('0','1') NOT NULL DEFAULT '0', PRIMARY KEY (`res_table_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ; INSERT INTO `reservation_tables` (`res_table_id`, `res_table_name`, `date_time`, `partyhall_id`, `flag`) VALUES (1, 1, '2014-08-17 12:00 am', 7, '1'), (2, 2, '2014-08-17 12:00 am', 7, '1'), (3, 3, '2014-08-18 12:00 am', 8, '1'), (4, 4, '2014-08-18 12:00 am', 8, '1'), (5, 1, '2014-08-25 12:00 am', 12, '1'), (6, 2, '2014-08-25 12:00 am', 12, '1'), (7, 3, '2014-08-20 12:00 am', 23, '1'), (8, 4, '2014-08-20 12:00 am', 23, '1');

Ι had to select available table name for matching date_time

Example select available table_name where date_time = 2014-08-18 12:00 am.

solution query is: im sure this works well

SELECT distinct res_table_name FROM reservation_tables WHERE `res_table_name` NOT IN (SELECT `res_table_name` FROM reservation_tables where `date_time` = '2014-08-17 12:00 am')

Collectives™ on Stack Overflow

MySQL NOT IN from another column in the same table

5 Answers 5

3 Comments

1 Comment

Comments

EDIT TO ADD : HERE WITH OP SCHEMA

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

1 Comment

Comments

EDIT TO ADD : HERE WITH OP SCHEMA

3 Comments

Comments

Linked

Related