mysql - SQL交叉查找两张表

Question

我有以下 2 个表..

mysql> describe catalog_category_reference;
+----------+---------+------+-----+---------+----------------+
| Field    | Type    | Null | Key | Default | Extra          |
+----------+---------+------+-----+---------+----------------+
| id       | int(11) | NO   | PRI | NULL    | auto_increment | 
| name     | text    | NO   |     | NULL    |                | 
| class_id | text    | NO   |     | NULL    |                | 
+----------+---------+------+-----+---------+----------------+

和

mysql> describe product_import_queue;
+-----------------------+---------+------+-----+---------+----------------+
| Field                 | Type    | Null | Key | Default | Extra          |
+-----------------------+---------+------+-----+---------+----------------+
| id                    | int(11) | NO   | PRI | NULL    | auto_increment | 
| unique_id             | text    | NO   |     | NULL    |                | 
| category_code         | text    | NO   |     | NULL    |                | 
| item_code             | text    | NO   |     | NULL    |                | 
| ffl_flag              | int(11) | NO   |     | NULL    |                | 
| name                  | text    | NO   |     | NULL    |                | 
| price                 | text    | NO   |     | NULL    |                | 
| image                 | text    | NO   |     | NULL    |                | 
| custom_options_flag   | int(11) | NO   |     | NULL    |                | 
| custom_options_string | text    | NO   |     | NULL    |                | 
| short_desc            | text    | NO   |     | NULL    |                | 
| long_desc             | text    | NO   |     | NULL    |                | 
| process_status        | int(11) | NO   |     | 0       |                | 
+-----------------------+---------+------+-----+---------+----------------+

我想搜索 product_import_queue 并找到 catalog_Category_reference 中不存在的“category_code”。请注意，category_code 存储在 class_id 下的 catalog_reference 表中。我可以在一个查询中执行此操作吗？我尝试过类似...

SELECT category_code FROM product_import_queue
LEFT JOIN catalog_category_reference ON product_import_queue.category_code = catalog_category_reference.class_id;

但这不是我要找的，而且我还没有完全理解 JOIN。

Answer 1

你几乎在那里......只需要添加一个 where 子句......

SELECT category_code 
FROM   product_import_queue 
       LEFT JOIN catalog_category_reference 
         ON product_import_queue.category_code = 
            catalog_category_reference.class_id 
WHERE  catalog_category_reference.class_id IS NULL