1

I have two tables, same structure (filename, filesize, permissions), and possible different or missing values between the two..

I would like to display all the records with the following fields:

filename | filesizeTable1 | permissionsTable1| filesizeTable2 | permissionsTable2 | match

and I would like:

  1. the same filename to be displayed only once
  2. if filename is missing in one of the two tables, the columns filesize and permissions for that table would be empty
  3. if filename exists on both tables, but one of the other fields is different the field "match" would display "1"
  4. if filename exists on both tables, and the other fields are the same, the field "match" would display "0"

The step 1 is achievable with a join between the two tables (select filename from table1 union select filename from table2 order by filename;) but I'm stuck on how to proceed to have empty lines when a filename is missing in one of the two tables..

Solution (thanks Lennart):

select a.filename as filename
     , a.size as filesize1, a.permissions as permissions1
     , b.size as filesize2, b.permissions as permissions2
     , CASE
        WHEN a.filename IS NULL or b.filename IS NULL THEN 1 
        WHEN a.size != b.size THEN 2
        WHEN a.permissions != b.permissions THEN 2 
        ELSE 0
       END  as Match
from scanTableHost1 as a
LEFT JOIN scanTableHost2 as b 
    ON a.filename = b.filename
UNION 
select b.filename as filename
     , a.size as filesize1, a.permissions as permissions1
     , b.size as filesize2, b.permissions as permissions2 
     , CASE
        WHEN a.filename IS NULL or b.filename IS NULL THEN 1 
        WHEN a.size != b.size THEN 2
        WHEN a.permissions != b.permissions THEN 2 
        ELSE 0
       END  as Match
from scanTableHost2 as b
LEFT JOIN scanTableHost1 as a
    ON a.filename = b.filename
  • 1
    Does SQLite have FULL joins? – ypercubeᵀᴹ Apr 7 '18 at 9:17
  • I assume filename is not null in scanTableHostx. If so you the left table can never be null, so you can simplify your first CASE to: WHEN b.filename IS NULL THEN 1 and the second one to: WHEN a.filename IS NULL IS NULL THEN 1. Also != works in many DBMS, but the standard operator for not equals in SQL is <> – Lennart Apr 7 '18 at 11:08
  • 1
    Your description for when there is a difference in values and the wanted behaviour does not match the code you produced in the end. Your description says the result should be 1, the code says 2. – ypercubeᵀᴹ Apr 7 '18 at 19:11
  • you are right, I changed the requirements, so that I have 3 values.. 0 if the rows match, 1 if it's missing, and 2 is it's different. I didn't pay attention to the actual value – clagio Apr 9 '18 at 12:05
3

I assume when you say JOIN you really mean a more general combination of the two tables. JOIN is an operator in SQL, so you should avoid using it when you are referring to a UNION. Anyhow, if I get it right you want a FULL OUTER JOIN between two tables:

select COALESCE(a.filename, b.filename) as filename
     , a.filesize as filesize1, a.permissions as permissions1
     , b.filesize as filesize1, b.permissions as permissions1
from T1 as a
FULL JOIN T2 as b
    ON a.filename = b.filename

COALESCE is a function that return the first value from left to right that is not null.

However, from what I understand SQLite only supports LEFT JOIN so you have to rewrite the query using two LEFT JOINS and a UNION as:

select a.filename as filename
     , a.filesize as filesize1, a.permissions as permissions1
     , b.filesize as filesize1, b.permissions as permissions1 
     , CASE WHEN b.filename IS NOT NULL -- a match
            THEN CASE WHEN (a.filesize, a.permissions) = (b.filesize, b.permissions)  
                      THEN 0 ELSE 1
                 END
       END     
from T1 as a
LEFT JOIN T2 as b
    ON a.filename = b.filename
UNION 
select b.filename as filename
     , a.filesize as filesize1, a.permissions as permissions1
     , b.filesize as filesize1, b.permissions as permissions1 
     , CASE WHEN a.filename IS NOT NULL -- a match
            THEN CASE WHEN (a.filesize, a.permissions) = (b.filesize, b.permissions)  
                      THEN 0 ELSE 1
                 END
       END     
from T2 as b
LEFT JOIN T1 as a
    ON a.filename = b.filename

Note that a and b changed place in the second leg of the union.

  • Thanks Lennart! indeed sqlite doesn't support outer joins, I using your query works perfectly, for point 2 Not sure why but if I use UNION ALL, instead of UNION, the query is much faster.. instead of 55ms. I suppose for point 3 and 4, I will need to use CASE ? – clagio Apr 7 '18 at 9:46
  • UNION ALL is normally faster because it does not have to bother with duplicates. If you have two identical rows in T1 and T2 both will be in the result if you use UNION ALL. Yes, you can add a CASE statement for the match, I'll give an example. What should match be if filename does not match? – Lennart Apr 7 '18 at 10:02
  • 1
    Thanks I managed to achieve all the steps, I updated my first comment with your solution :) – clagio Apr 7 '18 at 10:58
2

This is a combination of Lennart's and the OP's queries, with some simplifications / corrections:

  • UNION ALL instead of UNION. This is very likely to improve efficiency, removing the removal of duplicate rows that UNION has to do.
  • Changing one of the two parts with the addition of IS NULL check, so they don't produce duplicates. Necessary so UNION ALL works correctly.
  • The previous changes allows us to further simplify the expressions of the 2nd part.
  • Corrected the CASE expression to handle nulls in size and permissions correctly. If a.size for example is 100 and b.size is NULL, the expression will now show 2 (a difference) and not 0 (as if they were the same).

The query:

SELECT a.filename,
       a.size AS filesize1, a.permissions AS permissions1,
       b.size AS filesize2, b.permissions AS permissions2,
       CASE
         WHEN b.filename IS NULL THEN 1 
         WHEN a.size = b.size AND a.permissions = b.permissions THEN 0 
         ELSE 2
       END  AS match
FROM scanTableHost1 AS a
     LEFT JOIN scanTableHost2 AS b 
     ON a.filename = b.filename
UNION ALL 
SELECT b.filename,
       NULL, NULL, 
       b.size, b.permissions,
       1
FROM scanTableHost2 AS b
     LEFT JOIN scanTableHost1 AS a
     ON a.filename = b.filename
WHERE a.filename IS NULL ;

Another way to solve this kind of problem (i.e. write a FULL JOIN in a DBMS that doesn't have it), is to use a UNION to get all distinct join values (filenames in your case) in a derived table and then LEFT join once to each of the two tables.

This method may or may not be more efficient but is certainly cleaner, especially if you need to FULL joins more than two tables.

How to write the query:

SELECT d.filename,
       a.size AS filesize1, a.permissions AS permissions1,
       b.size AS filesize2, b.permissions AS permissions2,
       CASE
         WHEN a.filename IS NULL OR b.filename IS NULL THEN 1 
         WHEN a.size = b.size AND a.permissions = b.permissions THEN 0 
         ELSE 2
       END  AS match
FROM
    ( SELECT filename FROM table1
      UNION                         -- this means: UNION DISTINCT 
      SELECT filename FROM table2
    ) AS d
  LEFT JOIN table1 AS a
    ON  a.filename = d.filename
  LEFT JOIN table2 AS b
    ON  b.filename = d.filename ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.