Problem with joining to an empty table
- by Imran Omar Bukhsh
I use the following query:
select * from A LEFT JOIN B on ( A.t_id != B.t_id)
to get all the records in A that are not in B.
The results are fine except when table B is completely empty, but then I do not get any records, even from table A.
Later
It wont work yet!
CREATE TABLE IF NOT EXISTS T1 (
id int(11) unsigned NOT NULL AUTO_INCREMENT,
title varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
t_id int(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
--
-- Dumping data for table T1
INSERT INTO T1 (id, title, t_id) VALUES
(1, 'apple', 1),
(2, 'orange', 2);
--
-- Table structure for table T2
CREATE TABLE IF NOT EXISTS T2 (
id int(11) NOT NULL AUTO_INCREMENT,
title varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
t_id int(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
--
-- Dumping data for table T2
INSERT INTO T2 (id, title, t_id) VALUES
(1, 'dad', 2);
Now I want to get all records in T1 that do not have a corresponding records in T2
I try
SELECT *
FROM T1
LEFT OUTER JOIN T2 ON T1.t_id != T2.t_id
and it won't work
