How do i get a list of records from two tables that have different values based on a matching PK? Lets say i have the following two tables:

Table1:

|IP    |Name  | Gateway | Version |
+------+------+---------+---------+
|1     |A     | 1.0.0   | v1      |
+------+------+---------+---------+
|2     |B     | 2.0.0   | v2      |
+------+------+---------+---------+
|3     |C     | 3.0.0   | v3      |   
+------+------+---------+---------+
|4     |D     | 4.0.0   | v4      |
+------+------+---------+---------+


Table2:

|IP    |Name  | Gateway | Version |
+------+------+---------+---------+
|1     |A     | 1.0.0   | v1      |
+------+------+---------+---------+
|2     |F     | 2.0.0   | v2      |
+------+------+---------+---------+
|3     |C     | 3.0.1   | v3      |   
+------+------+---------+---------+
|4     |D     | 4.0.0   | v10      |
+------+------+---------+---------+


How would i write a query to return a list of records from both tables that have certain different values based on the primary key (Lets use IP in this example)?

For example, if i chose to compare only Version and Name, the result would be:

|IP    |Name  | Gateway | Version |
+------+------+---------+---------+
|2     |B     | 2.0.0   | v2      |
+------+------+---------+---------+
|2     |F     | 2.0.0   | v2      |
+------+------+---------+---------+
|4     |D     | 4.0.0   | v4      |
+------+------+---------+---------+
|4     |D     | 4.0.0   | v10     |


Thank you!
Note MySQL 8 is has been released for more then a year now
   which supports COUNT(DISTINCT ...) OVER(PARTITION BY ...) syntax..


There are multiple solutions possible,

Yes the solutions are hard, one of the problems is that MySQL does not (jet) support 

COUNT(DISTINCT ...) OVER(PARTITION BY ...)

otherwise the solution would have been much more easy. 

one of the solutions is.

Query

SELECT 
 *
FROM (

  SELECT 
   table1.*
  FROM 
   table1 

  UNION ALL 

  SELECT 
   table2.*
  FROM 
   table2 
) AS merged

INNER JOIN (

  SELECT 
     total_data.IP 
   , COUNT(*) = COUNT(DISTINCT total_data.Name) AS has_name_changes
   , COUNT(*) = COUNT(DISTINCT total_data.Gateway) AS has_gateway_changes
   , COUNT(*) = COUNT(DISTINCT total_data.Version) AS has_version_changes
  FROM (
    SELECT 
     table1.*
    FROM 
     table1 

    UNION ALL 

    SELECT 
     table2.*
    FROM 
     table2 
  ) AS total_data
  GROUP BY 
     total_data.IP  
) AS search_filter
ON
   merged.IP = search_filter.IP
WHERE
   search_filter.has_name_changes = 1
 OR
   search_filter.has_version_changes = 1


Result

| IP  | Name | Gateway | Version | IP  | has_name_changes | has_gateway_changes | has_version_changes |
| --- | ---- | ------- | ------- | --- | ---------------- | ------------------- | ------------------- |
| 2   | B    | 2.0.0   | v2      | 2   | 1                | 0                   | 0                   |
| 2   | F    | 2.0.0   | v2      | 2   | 1                | 0                   | 0                   |
| 4   | D    | 4.0.0   | v4      | 4   | 0                | 0                   | 1                   |
| 4   | D    | 4.0.0   | v10     | 4   | 0                | 0                   | 1                   |


see demo

The other solution which is even harder and only works when you have two records, one in each table.

Query

SET SESSION group_concat_max_len = @@max_allowed_packet;

SELECT 
   merged.IP 
 , SUBSTRING_INDEX(
     SUBSTRING_INDEX(
       SUBSTRING_INDEX(
         SUBSTRING_INDEX(
           GROUP_CONCAT(
             CONCAT_WS(
                ','
              , merged.Name
              , merged.Gateway
              , merged.Version
             )
             SEPARATOR ';'
           )
            , ';'
            , sql_number_generator.number
         )
         , ';'
         , -1
      )
      , ','
      , 1
    )
    , ','
    , -1
  ) AS Name
 , SUBSTRING_INDEX(
     SUBSTRING_INDEX(
       SUBSTRING_INDEX(
         SUBSTRING_INDEX(
           GROUP_CONCAT(
             CONCAT_WS(
                ','
              , merged.Name
              , merged.Gateway
              , merged.Version
             )
             SEPARATOR ';'
           )
            , ';'
            , sql_number_generator.number
         )
         , ';'
         , -1
      )
      , ','
      , 2
    )
    , ','
    , -1
  ) AS Gateway
 , SUBSTRING_INDEX(
     SUBSTRING_INDEX(
       SUBSTRING_INDEX(
         SUBSTRING_INDEX(
           GROUP_CONCAT(
             CONCAT_WS(
                ','
              , merged.Name
              , merged.Gateway
              , merged.Version
             )
             SEPARATOR ';'
           )
            , ';'
            , sql_number_generator.number
         )
         , ';'
         , -1
      )
      , ','
      , 3
    )
    , ','
    , -1
  ) AS Version


,  SUBSTRING_INDEX(
         SUBSTRING_INDEX(
           GROUP_CONCAT(
             CONCAT_WS(
                ','
              , merged.Name
              , merged.Gateway
              , merged.Version
             )
             SEPARATOR ';'
           )
            , ';'
            , sql_number_generator.number
         )
         , ';'
         , -1
      ) AS merged_record
FROM (
  SELECT 
   *
  FROM 
   table1 
  UNION ALL
  SELECT 
   *
  FROM 
    table2 
) AS merged
CROSS JOIN (
  SELECT 
   1 AS number 
  UNION
  SELECT
   2 AS number  
) AS sql_number_generator 
GROUP BY 
   merged.IP 
 , sql_number_generator.number
HAVING 
   MIN(merged.Name) &lt;&gt; MAX(merged.Name)
 OR
   MIN(merged.Version) &lt;&gt; MAX(merged.Version)


Result

| IP  | Name | Gateway | Version | merged_record |
| --- | ---- | ------- | ------- | ------------- |
| 2   | B    | 2.0.0   | v2      | B,2.0.0,v2    |
| 2   | F    | 2.0.0   | v2      | F,2.0.0,v2    |
| 4   | D    | 4.0.0   | v4      | D,4.0.0,v4    |
| 4   | D    | 4.0.0   | v10     | D,4.0.0,v10   |


see demo