Home ›
Select rows in a MySQL table that have no corresponding rows in another table using IS NULLSelect rows in a MySQL table that have no corresponding rows in another table using IS NULL
Submitted by Benjamin Melançon on December 19, 2007 - 6:21am
If there is no matching row for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find rows in a table that have no counterpart in another table:
SELECT left_tbl.*
FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id
WHERE right_tbl.id IS NULL;
This example finds all rows in left_tbl with an id value that is not present in right_tbl (that is, all rows in left_tbl with no corresponding row in right_tbl). This assumes that right_tbl.id is declared NOT NULL.
http://dev.mysql.com/doc/refman/5.0/en/join.html
Hmm. Not working.
Portuguese, Brazil translation of WSF2008
Copyright (c) 2007 submin support@agaricdesign.com
#
msgid ""
msgstr ""
"Project-Id-Version: PROJECT VERSION\n"
"POT-Creation-Date: 2007-12-17 10:16-0500\n"
"PO-Revision-Date: 2007-12-17 10:16-0500\n"
"Last-Translator: submin support@agaricdesign.com\n"
"Language-Team: Portuguese, Brazil support@agaricdesign.com\n"
"MIME-Version: 1.0\n"
"Content-Type: text/plain; charset=utf-8\n"
"Content-Transfer-Encoding: 8bit\n"
"Plural-Forms: nplurals=2; plural=(n>1);\n"
user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near
't.locale = 'pt-br' ORDER BY t.plid, t.plural' at line 1 query: SELECT s.lid, s.source, s.location, t.translation, t.plid, t.plural FROM locales_source s INNER JOIN locales_target t ON s.lid = t.lid LEFT JOIN locale_subset ls ON s.lid = ls.lid WHERE ls.lid = NULL t.locale = 'pt-br' ORDER BY t.plid, t.plural in /var/www/wsf2008/includes/database.mysql.inc on line 172.
missing AND in SQL. Fixed that:
Still not working.
SELECT s.lid, s.source, s.location, t.translation, t.plid, t.plural FROM locales_source s INNER JOIN locales_target t LEFT JOIN locale_subset ls ON s.lid = t.lid AND s.lid = ls.lid WHERE ls.lid = NULL AND t.locale = 'pt-br' ORDER BY t.plid, t.plural
Nothing. Zero rows like the others.
SELECT s.lid, s.source, s.location, t.translation, t.plid, t.plural FROM locales_source s LEFT JOIN locale_subset ls ON s.lid = ls.lid INNER JOIN locales_target t ON s.lid = t.lid WHERE ls.lid = NULL AND t.locale = 'pt-br' ORDER BY t.plid, t.plural
It works without the LEFT JOIN locale_subset, but not combined. Hmm... it doesn't work with my locale_subset join at all!
SELECT s.lid, s.source, s.location FROM locales_source s LEFT JOIN locale_subset ls ON s.lid = ls.lid WHERE ls.lid IS NULL
Arrghh!
IS NULL IS NULL IS NULL IS NULL IS NULL IS NULL
IS IS IS IS IS IS IS IS IS IS IS IS IS IS IS IS
NOT 'equals' or '=' but IS
IS NULL
Dang me.
Basically, the original works:
SELECT s.lid, s.source, s.location, t.translation, t.plid, t.plural FROM locales_source s INNER JOIN locales_target t ON s.lid = t.lid LEFT JOIN locale_subset ls ON s.lid = ls.lid WHERE ls.lid IS NULL AND t.locale = 'pt-br' ORDER BY t.plid, t.plural
or in Drupal:
$result = db_query("SELECT s.lid, s.source, s.location, t.translation, t.plid, t.plural FROM {locales_source} s INNER JOIN {locales_target} t ON s.lid = t.lid LEFT JOIN {locale_subset} ls ON s.lid = ls.lid WHERE ls.lid IS NULL AND t.locale = '%s' ORDER BY t.plid, t.plural", $language);
Resolution
Searched words:
MySQL JOIN on null missing
JOIN on null exclude
JOIN WHERE null MySQL
Comments
Select rows in a MySQL table that have no corresponding rows in
select * from table_a aleft join table_b b on a.id = a.id -- or whatever fields you need to use, must use left join
where b.id is null -- here you use the table field that should not have records
I haven't tried, but you should be able to use join tables using the field a.id (table_a)
good luck!
fxarte
Yup! Thanks fxarte
If my unclear notes confused anyone else, yes, the first example from the MySQL docs and the last example in pure SQL and Drupal (6 and earlier) SQL are correct and do work. (So should, of course, fxarte's example.) Everything in the middle is me forgetting that NULL must be used with the IS or IS NOT keywords, not = or != ...may I remember next time.
I was looking for something
I was looking for something similar, but found that this:
SELECT table1.id FROM table1
WHERE NOT EXISTS (
SELECT item_id
FROM table2
WHERE table2.item_id = table1.id
);
does the same...
Post new comment