sql - 如何在同一个查询中查询两个链接表？

Question

我有两张 table 。 device 可以在 blacklisted_device 上。我想获取包含特定 user_ids 的设备数量，并且在相同请求数量的 blacklisted_devices 链接中。

这里有完整的 sql 来试试:

CREATE TABLE device (
  device_id serial PRIMARY KEY,
  user_id integer,
  updated_at timestamp default current_timestamp
);

CREATE TABLE blacklisted_device (
  blacklisted_id serial PRIMARY KEY,
  device_id integer,
  updated_at timestamp default current_timestamp,
  CONSTRAINT blacklisted_device_device_id_fkey FOREIGN KEY (device_id)
      REFERENCES device (device_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);
INSERT INTO device (user_id)
VALUES (1),(2),(2),(7),(88),(99),(102),(106);

INSERT INTO blacklisted_device (device_id)
VALUES (1),(2),(3),(4);

SELECT COUNT(*) AS total_device
FROM device
WHERE user_id IN (7,88,99);

SELECT COUNT(*) AS blacklisted
FROM blacklisted_device
WHERE device_id IN (SELECT device_id FROM device WHERE user_id IN (7,88,99));

正如您在最后看到的那样，我在两次请求中得到了我想要的结果。如何在一个请求中获取它？

total_device:3，黑名单:1

请随意对所有 SQL 进行评论，我可能犯了一些错误。

谢谢

Answer 1

您需要一个LEFT JOIN:

SELECT  COUNT(*) AS total_device,
        COUNT(DISTINCT bd.device_id) AS blacklisted
FROM device d
LEFT JOIN blacklisted_device bd
    ON d.device_id = bd.device_id
WHERE d.user_id IN (7,88,99);