MySQL, in fact, all SQL products really barf at

c.id=NULL

It causes confusion. You really mean

c.id IS NULL

You need to change it to

SELECT count(*) FROM list l LEFT OUTER JOIN cardinal c ON l.sku=c.sku where c.id is null;

Give it a Try !!!

ABOUT YOUR QUESTION

Your first query

SELECT count(*) FROM list l LEFT JOIN cardinal c ON c.id=null;

resembles a natural join where nothing matches on the cardinal table, so the count 2677513 is correct.

Your second query

SELECT count(*) FROM list l LEFT OUTER JOIN cardinal c ON l.sku=c.sku where c.id=null;

is little more explicit, but the c.id=null makes it fail, thus getting 0 as a count.

A PRIMARY KEY is, by definition, UNIQUE and NOT NULL, so by adding a second UNIQUE keyword on it you are actually creating a separate index. This does not only make things less efficient but, in some cases, it will make your queries less performant.

You can check that two index were created by doing:

mysql> SHOW CREATE TABLE test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `c` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Both in MyISAM and InnoDB, it will require additional space, it will make the writes slower (because the index has to be updated synchronously at write time), and it may waste optimizer (even if microseconds) time deciding which index is the best one. MySQL would usually select the PRIMARY key in MyISAM, but if for any reason both indexes were used, you will also waste memory.

If you do not believe me, believe MySQL:

mysql> INSERT test1 SELECT * from test;
Query OK, 1691648 rows affected (6.84 sec)
Records: 1691648  Duplicates: 0  Warnings: 0

mysql> create table test2 like test1;
Query OK, 0 rows affected (0.04 sec)

mysql> ALTER TABLE test2 add unique(id);
Query OK, 0 rows affected, 1 warning (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> INSERT test2 SELECT * from test;
Query OK, 1691648 rows affected (10.94 sec)
Records: 1691648  Duplicates: 0  Warnings: 0

A run of pt-duplicate-key-checker is usually enough to detect those duplicates.

If you mean using UNIQUE NOT NULL instead of PRIMARY KEY, I would not expect a difference on MyISAM in most cases (although some tools work better with a PK, like row-based replication); it is not the case with InnoDB, in which the PK is fundamental on its structure (clustered rows around it).