mysql - 如何使用 JOIN 代替 IN 子句

Question

我有以下查询，工作正常，但它使用了 IN 子句。有没有办法用 JOIN 替换 IN 子句？

SELECT p.*
FROM products as p
LEFT JOIN `product_categories` AS `pc` ON (`pc`.`product_id` = `p`.`product_id`)
WHERE pc.category_id IN (SELECT node.category_id 
FROM categories AS node, categories AS parent
WHERE node.left_node 
BETWEEN parent.left_node AND parent.right_node
AND parent.category_id = 6 ORDER BY node.left_node)

这里是我必须使用的表结构，它与 IN 子句一起工作正常，但我不知道，使用 JOIN 而不是 IN 子句获得相同的结果

--
-- Table structure for table `categories`
--

CREATE TABLE IF NOT EXISTS `categories` (
  `category_id` int(11) NOT NULL,
  `name` varchar(20) NOT NULL,
  `left_node` int(11) NOT NULL,
  `right_node` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `categories`
--

INSERT INTO `categories` (`category_id`, `name`, `left_node`, `right_node`) VALUES
(1, 'electronics', 1, 20),
(2, 'televisions', 2, 9),
(3, 'tube', 3, 4),
(4, 'lcd', 5, 6),
(5, 'plasma', 7, 8),
(6, 'portable electronics', 10, 19),
(7, 'mp3 players', 11, 14),
(8, 'flash', 12, 13),
(9, 'cd players', 15, 16),
(10, '2 way radios', 17, 18);

-- --------------------------------------------------------

--
-- Table structure for table `products`
--

CREATE TABLE IF NOT EXISTS `products` (
  `product_id` int(11) NOT NULL,
  `name` varchar(40) DEFAULT NULL,
  `cat_id` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `products`
--

INSERT INTO `products` (`product_id`, `name`, `cat_id`) VALUES
(1, '20" TV', 3),
(2, '36" TV', 3),
(3, 'Super-LCD 42"', 4),
(4, 'Ultra-Plasma 62"', 5),
(5, 'Value Plasma 38"', 5),
(6, 'Power-MP3 5gb', 7),
(7, 'Ipod 4gb', 8),
(8, 'Porta CD', 9),
(9, 'Walkman', 9),
(10, 'Family Talk 360', 10);

-- --------------------------------------------------------

--
-- Table structure for table `product_categories`
--

CREATE TABLE IF NOT EXISTS `product_categories` (
  `product_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `product_categories`
--

INSERT INTO `product_categories` (`product_id`, `category_id`) VALUES
(1, 3),
(2, 3),
(3, 4),
(4, 5),
(5, 5),
(6, 7),
(7, 8),
(8, 9),
(9, 9),
(10, 10);

Answer 1

您需要在 node.category_id 上 JOIN 进行转换

SELECT p.*
FROM products as p
LEFT JOIN `product_categories` AS `pc` ON `pc`.`product_id` = `p`.`product_id`
JOIN categories AS node ON pc.category_id = node.category_id
JOIN categories AS parent ON node.left_node BETWEEN parent.left_node AND parent.right_node
WHERE parent.category_id = 6
ORDER BY node.left_node;