我正在尝试在列地址（用户表）中查找与地址（地址\效果表）匹配的任何项。我正在用xampp（使用mariadb）在本地系统上测试这个

用户表

+------------------+-----------------+------------------+--------------------------+
|        ID        |    firstname    |  lastname        |    address               |
|                  |                 |                  |                          |
+----------------------------------------------------------------------------------+
|     1            |    john         |    doe           |james street, idaho, usa  |                    
|                  |                 |                  |                          |
+----------------------------------------------------------------------------------+
|     2            |    cindy        |   smith          |rollingwood av,lyn, canada|
|                  |                 |                  |                          |
+----------------------------------------------------------------------------------+
|     3            |    rita         |   chatsworth     |arajo ct, fremont, cali   |
|                  |                 |                  |                          |
+------------------+-----------------+---------------------+-----------------------+
|     4            |    randy        |   plies          |smith spring, lima, peru  |                       
|                  |                 |                  |                          |
+----------------------------------------------------------------------------------+
|     5            |    Matt         |   gwalio         |park lane, atlanta, usa   |
|                  |                 |                  |                          |
+------------------+-----------------+------------------+--------------------------+

地址影响表

+---------+----------------+
|idaho    |potato, tater   |
+--------------------------+
|canada   |cold, tundra    |
+--------------------------+
|fremont  | crowded        |
+--------------------------+
|peru     |alpaca          |
+--------------------------+
|atlanta  |peach, cnn      |
+--------------------------+
|usa      |big, hard       |
+--------+-----------------+

我试过使用内部连接来查找匹配的字符串。
如果使用此查询，则找不到任何项：

SELECT users.firstname, users.lastname, users.address
FROM users
INNER JOIN db_name.address_effect
ON
(address_effect.Address LIKE '%' + users.address + '%'
OR users.address LIKE '%' || address_effect.Address || '%')

然后我尝试了下面的查询，它列出了user表中的所有项，而不是只列出那些地址匹配的项

SELECT DISTINCT users.firstname, users.lastname, users.address
FROM users
INNER JOIN db_name.address_effect
ON
(address_effect.Address LIKE '%' || users.address || '%'
OR users.address LIKE '%' || address_effect.Address || '%')

我错过了什么？
谢谢。