How do I join a many-to-many where the left table can be null OR the right table can be null?

Question

Here is my schema and test data to create a minimum reproducible example:

CREATE TABLE `scpsl_user_id_bans` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `scpsl_ip_bans` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `scpsl_ban_link` (
  `user_id_ban_id` bigint(20) unsigned DEFAULT NULL,
  `ip_ban_id` bigint(20) unsigned DEFAULT NULL,
  `start_date_skew` bigint(20) DEFAULT NULL,
  `end_date_skew` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `scpsl_user_id_bans` VALUES (1, "First Ban");
INSERT INTO `scpsl_user_id_bans` VALUES (2, "Second Ban");
INSERT INTO `scpsl_user_id_bans` VALUES (3, "Third Ban");
INSERT INTO `scpsl_user_id_bans` VALUES (4, "Fourth Ban");

INSERT INTO `scpsl_ip_bans` VALUES  (3, "Third Ban");
INSERT INTO `scpsl_ip_bans` VALUES  (4, "Fourth Ban");
INSERT INTO `scpsl_ip_bans` VALUES  (5, "Fifth Ban");
INSERT INTO `scpsl_ip_bans` VALUES  (6, "Sixth Ban");

INSERT INTO `temp`.`scpsl_ban_link` VALUES ('3', '3', '0', '0');
INSERT INTO `temp`.`scpsl_ban_link` VALUES ('4', '4', '0', '0');

This is the query I want my VIEW to work with:

SELECT DISTINCT
    `idbans`.`id` AS `user_id_ban_id`,
    `ipbans`.`id` AS `ip_ban_id`,
    `idbans`.`name` AS `user_id_ban_name`,
    `ipbans`.`name` AS `ip_ban_name`,
    `idbans`.`user_id` AS `user_id`,
    `ipbans`.`user_id` AS `ip_address`
FROM `scpsl_ban_link` `ban_link`
LEFT OUTER JOIN `scpsl_user_id_bans` `idbans` ON (`idbans`.`id` = `ban_link`.`user_id_ban_id`)
RIGHT OUTER JOIN `scpsl_ip_bans` `ipbans` ON (`ban_link`.`ip_ban_id` = `ipbans`.`id`)
WHERE user_id_ban_id IS NULL or ip_ban_id IS NULL
ORDER BY user_id_ban_id DESC
;

And I expect to view this data:

+------+------------+----------------+-----------+-------+------------+
| id   | name       | user_id_ban_id | ip_ban_id | id    | name       |
+------+------------+----------------+-----------+-------+------------+
|    1 | First Ban  |              1 |         1 |  NULL | NULL       |
|    2 | Second Ban |              2 |         2 |  NULL | NULL       |
|    3 | Third Ban  |              3 |         3 |     3 | Third Ban  |
|    4 | Fourth Ban |              4 |         4 |     4 | Fourth Ban |
| NULL | NULL       |           NULL |         5 |     5 | Fifth Ban  |
| NULL | NULL       |           NULL |         6 |     6 | Sixth Ban  |
+------+------------+----------------+-----------+-------+------------+

However, MySQL creates this SQL when I try to create a view:

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `scpsl`@`localhost` 
    SQL SECURITY DEFINER
VIEW `scpsl_print_bans` AS
    SELECT DISTINCT
        `idbans`.`id` AS `user_id_ban_id`,
        `ipbans`.`id` AS `ip_ban_id`,
        `idbans`.`name` AS `user_id_ban_name`,
        `ipbans`.`name` AS `ip_ban_name`,
        `idbans`.`user_id` AS `user_id`,
        `ipbans`.`user_id` AS `ip_address`,
        FROM_DOTNETTICKS(`idbans`.`start_date`) AS `start_date`,
        FROM_DOTNETTICKS(`idbans`.`end_date`) AS `end_date`,
        `idbans`.`admin` AS `admin`,
        `idbans`.`reason` AS `reason`,
        `idbans`.`active` AS `user_id_ban_active`,
        `ipbans`.`active` AS `ip_ban_active`,
        CONCAT('UPDATE scpsl_user_id_bans SET active = 0 WHERE id = ',
                `idbans`.`id`) AS `unban_id`,
        CONCAT('UPDATE scpsl_ip_bans SET active = 0 WHERE id = ',
                `ipbans`.`id`) AS `unban_ip`
    FROM
        (`scpsl_ip_bans` `ipbans`
        LEFT JOIN (`scpsl_ban_link` `ban_link`
        LEFT JOIN `scpsl_user_id_bans` `idbans` ON ((`idbans`.`id` = `ban_link`.`user_id_ban_id`))) ON ((`ban_link`.`ip_ban_id` = `ipbans`.`id`)))
    ORDER BY `user_id_ban_id` DESC

Why is the view being mangled? How do I get my data to display like this, where a ban is always present in both (scpsl_user_id_bans AND scpsl_ban_link) OR (scpsl_ban_link OR scpsl_ip_bans)?

x left join y on c is y right join x on c except for column order. So the 2 views return the same result. Please in code questions give a [mre]--cut & paste & runnable code; example input (as initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL that includes DBMS & DDL, which includes constraints & indexes & tabular initialization. For SQL performance that includes EXPLAIN results & statistics. — philipxy, Jun 15 '22 at 06:01
When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. Otherwise you're just asking us to (re)write yet another language definition for you to misunderstand & a bespoke tutorial, with no idea what your misconceptions are or what you do or don't understand. [mre] Plus for basic problems those will be faqs you can then characterize specifically enough to (re)search & find. — philipxy, Jun 24 '22 at 09:29
[How can I do a FULL OUTER JOIN in MySQL?](https://stackoverflow.com/q/4796872/3404097) — philipxy, Jun 24 '22 at 10:07

score 0 · Accepted Answer · edited Jun 24 '22 at 10:53

The query that I wanted contained THREE queries, UNIONed together:

SELECT
    `scpsl_user_id_bans`.`id` as `scpsl_user_id_bans_id`,
    `scpsl_user_id_bans`.`name` as `scpsl_user_id_bans_name`,
    `user_id_ban_id`,
    `ip_ban_id`,
    null as `scpsl_ip_bans_id`,
    null as `scpsl_ip_bans_name`
FROM
    `scpsl_user_id_bans`
    LEFT OUTER JOIN `scpsl_ban_link` ON (`scpsl_ban_link`.`user_id_ban_id` = `scpsl_user_id_bans`.`id`)
    WHERE `ip_ban_id` IS NULL
UNION
SELECT
    `scpsl_user_id_bans`.`id` as `scpsl_user_id_bans_id`,
    `scpsl_user_id_bans`.`name` as `scpsl_user_id_bans_name`,
    `user_id_ban_id`,
    `ip_ban_id`,
    `scpsl_ip_bans`.`id` as `scpsl_ip_bans_id`,
    `scpsl_ip_bans`.`name` as `scpsl_ip_bans_name`
FROM
    `scpsl_user_id_bans`
    LEFT JOIN `scpsl_ban_link` ON (`scpsl_ban_link`.`user_id_ban_id` = `scpsl_user_id_bans`.`id`)
    LEFT JOIN `scpsl_ip_bans` ON (`scpsl_ban_link`.`ip_ban_id` = `scpsl_ip_bans`.`id`)
UNION
SELECT
    null as `scpsl_user_id_bans_id`, 
    null as `scpsl_user_id_bans_name`,
    `user_id_ban_id`,
    `ip_ban_id`,
    `scpsl_ip_bans`.`id` as `scpsl_ip_bans_id`,
    `scpsl_ip_bans`.`name` as `scpsl_ip_bans_name`
FROM
    `scpsl_ip_bans`
    LEFT OUTER JOIN `scpsl_ban_link` ON (`scpsl_ban_link`.`ip_ban_id` = `scpsl_ip_bans`.`id`)
    WHERE `user_id_ban_id` IS NULL
;

How do I join a many-to-many where the left table can be null OR the right table can be null?

1 Answers1