In MySQL 5.7, in the two association tables, the query exists in one table but does not exist in the other table
1. The field table2_id in the table table1 is associated with the primary key field ID of the table table2. Now you need to query data that exists in table table1 but not in table table2 . The final implementation of SQL is as follows. as shown in Figure 1
Figure 1
SELECT
*
FROM
`db1_20230810`.`table1`
WHERE
NOT EXISTS ( SELECT * FROM `db2_20230810`.`table2` WHERE ( `table2`.`id` = `table1`.`table2_id` ) AND ( `table2`.`deleted_at` IS NULL ) )
AND ( `table1`.`role` != 'development' )
AND (
`table1`.`deleted_at` IS NULL
)
