MySQL Query One Table/Column With Value While Another Table/Column Empty

Question

I set up a test database named ‘test’ that contains 2 tables, tbl1 and tbl2. In plain language, I’m trying to return the name of all entries in the package2 column of tbl2 that do not exist in the package1 column of tbl1.

I’ve tried all different sorts of queries with no success as shown below. How can I accomplish this?

Returns nothing:

SELECT * 
FROM tbl1,tbl2 
WHERE NOT tbl1.package1 != tbl2.package2 
OR (tbl1.package1 IS NULL AND tbl1.package1 IS NOT NULL)

Returns nothing:

SELECT * 
FROM tbl1,tbl2 
WHERE (tbl1.package1 IS NULL AND tbl2.package2 IS NOT NULL)

Database setup

CREATE TABLE IF NOT EXISTS `tbl1` (
  `host` varchar(10) NOT NULL,
  `package1` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `tbl2` (
  `packageid` tinyint(10) NOT NULL,
  `package2` varchar(10) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

INSERT INTO `tbl2` (`packageid`, `package2`) VALUES
(1, 'a'),
(2, 'b'),
(3, 'c'),
(4, 'd');

-- Indexes for table `tbl1`
ALTER TABLE `tbl1`
  ADD PRIMARY KEY (`host`,`package1`) USING BTREE;

-- Indexes for table `tbl2`
ALTER TABLE `tbl2`
  ADD PRIMARY KEY (`packageid`);

ALTER TABLE `tbl2`
  MODIFY `packageid` tinyint(10) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=5;

Answer 1

What you need is a LEFT JOIN

SELECT tbl2.* FROM
tbl2 LEFT JOIN tbl1
ON tbl2.package2 = tbl1.package1
WHERE tbl1.package1 IS NULL;

Note how I specify tbl1.package1 IS NULL checking the condition of the JOIN, not the data.