SQL Server Join – Show Missing Data from 2 Tables with JOIN

sql servert-sql

I have 3 DB tables, a Computer table, a Patch table, and a ComputerPatched table. Below is a table mock up to assist my explanation….

DECLARE @Computer AS TABLE (
    [ComputerID] int
    , [ComputerName] nvarchar(50)
);

INSERT INTO @Computer ([ComputerID], [ComputerName])
VALUES (1, 'Computer A'), (2, 'Computer B'), (3, 'Computer C'), (4, 'Computer D');

DECLARE @Patch AS TABLE (
    [PatchID] int
    , [PatchName] nvarchar(50)
);

INSERT INTO @Patch ([PatchID], [PatchName])
VALUES (1, 'Security A'), (2, 'Security B'), (3, 'Security C'), (4, 'Security D');

DECLARE @ComputerPatched AS TABLE (
    [ComputerID] int
    , [PatchID] int
    , [InstallDate] datetime
);

INSERT INTO @ComputerPatched ([ComputerID], [PatchID], [InstallDate])
VALUES 
    (1, 1, '20200708'), (1, 3, '20200115')
    , (2, 1, '20200615'), (2, 3, '20200615'), (2, 4, '20200215')
    , (3, 4, '20200427')
;

What I am trying to achieve is to JOIN all results from the Computer table AND Patch table to find out what patches have been installed on what computers and what patches have not been installed. The result I would be expecting would look like something as below…

+------------+--------------+---------+------------+-----------+
| ComputerID | ComputerName | PatchID | PatchName  | Installed |
+------------+--------------+---------+------------+-----------+
|          1 | Computer A   |       1 | Security A | 20200708  |
|          1 | Computer A   |       2 | Security B | NULL      |
|          1 | Computer A   |       3 | Security C | 20200115  |
|          1 | Computer A   |       4 | Security D | NULL      |
|          2 | Computer B   |       1 | Security A | 20200615  |
|          2 | Computer B   |       2 | Security B | NULL      |
|          2 | Computer B   |       3 | Security C | 20200615  |
|          2 | Computer B   |       4 | Security D | 20200215  |
|          3 | Computer C   |       1 | Security A | NULL      |
|          3 | Computer C   |       2 | Security B | NULL      |
|          3 | Computer C   |       3 | Security C | 20200427  |
|          3 | Computer C   |       4 | Security D | NULL      |
|          4 | Computer D   |       1 | Security A | NULL      |
|          4 | Computer D   |       2 | Security B | NULL      |
|          4 | Computer D   |       3 | Security C | NULL      |
|          4 | Computer D   |       4 | Security D | NULL      |
+------------+--------------+---------+------------+-----------+

I am having trouble envisioning how to write this query I believe I would need some form of OUTER JOIN but everything I have tried has failed. Any help would be much appreciated!

Best Answer

You will need a CARTESIAN PRODUCT between computer and patch. You can then left join to get an eventual installdate:

SELECT c.[ComputerID], c.[ComputerName], p.[PatchID], p.[PatchName], cp.[InstallDate]
FROM @Computer c
CROSS JOIN @Patch p
LEFT JOIN @ComputerPatched cp
   ON c.[ComputerID] = cp.[ComputerID]
   AND p.[PatchID] = cp.[PatchID];

Fiddle

I would advise not to quote identifiers unless necessary.

Related Solutions

SQL Server HashBytes Function – Choosing the Right Algorithm

The HASHBYTES function only takes up to 8000 bytes as input. Because your inputs are potentially larger than that, duplicates in the range of the field that gets hashed will cause collisions, regardless of the algorithm chosen. Carefully consider the range of data you plan to hash -- using the first 4000 characters is the obvious choice, but may not be the best choice for your data.

In any event, because of what a hash function is, even if the inputs are 8000 bytes or less, the only way to ensure 100% correctness in the results is to compare the base values at some point (read: not necessarily first). Period.

The business will dictate whether or not 100% accuracy is required. This will tell you that either (a) comparing the base values is required, or (b) you should consider not comparing the base values -- how much accuracy should be traded off for performance.

While hash collisions are possible in a unique input set, they are infinitesimally rare, regardless of the algorithm chosen. The whole idea of using a hash value in this scenario is to efficiently narrow down the join results to a more manageable set, not to necessarily arrive at the final set of results immediately. Again, for 100% accuracy, this cannot be the final step in the process. This scenario isn't using hashing for the purpose of cryptography, so an algorithm such as MD5 will work fine.

It would be extremely hard for me to justify moving up to a SHA-x algorithm for "accuracy" purposes because if the business is going to freak out about the miniscule collision possibilities of MD5, chances are they're also going to freak out that the SHA-x algorithms aren't perfect either. They either have to come to terms with the slight inaccuracy, or mandate that the query be 100% accurate and live with the associated technical implications. I suppose if the CEO sleeps better at night knowing you used SHA-x instead of MD5, well, fine; it still doesn't mean much from a technical point of view in this case.

Speaking of performance, if the tables are read-mostly and the join result is needed frequently, consider implementing an indexed view to eliminate the need to compute the entire join every time it's requested. Of course you trade off storage for that, but it may be well worth it for the performance improvement, particularly if 100% accuracy is required.

For further reading on indexing long string values, I published an article that walks through an example of how to do this for a single table, and presents things to consider when attempting the full scenario in this question.

Sql-server – Updating a local table with a per-row count(*) which is an aggregate of inner joins on remote server

Depending on your permissions, the linked server could be trying to stream all the data over locally and then doing filtering. References

You might be able to skip that pain by computing the total aggregate count first into a table on the local server and then beat against that.

CREATE TABLE #LOCAL
(
    package_uuid nvarchar(255) NOT NULL PRIMARY KEY CLUSTERED
,   [count] bigint
);

INSERT INTO
    #LOCAL
SELECT 
    p.package_uuid
,   count(d.external_identification) AS [count]
FROM 
    ServerB.DATABASE.dbo.package p
    INNER JOIN 
        ServerB.DATABASE.dbo.doc2 d
        ON p.package_id = d.package_id
GROUP BY 
    p.package_uuid;

Try running that query locally on ServerB first to get an understanding of the theoretical throughput without factoring in your network. You can then do some quick and dirty estimates based on data sizes (500 + 8 per row in temporary table) and then it depends on your network. Hopefully this is all local network.

If the time is significantly different between the run on ServerB and pulling it back over, then you might need to use the OPENQUERY syntax to force the join on the remote server. Code approximately

CREATE TABLE #LOCAL
(
    package_uuid nvarchar(255) NOT NULL PRIMARY KEY CLUSTERED
,   [count] bigint
);

INSERT INTO
    #LOCAL
SELECT
    OQ.package_uuid
,   OQ.[count]
FROM
    OPENQUERY(ServerB,
    N'
    SELECT 
        p.package_uuid
    ,   count(d.external_identification) AS [count]
    FROM 
        DATABASE.dbo.package p
        INNER JOIN 
            DATABASE.dbo.doc2 d
            ON p.package_id = d.package_id
    GROUP BY 
        p.package_uuid
    ) AS OQ;

Best Answer

Related Solutions

SQL Server HashBytes Function – Choosing the Right Algorithm

Sql-server – Updating a local table with a per-row count(*) which is an aggregate of inner joins on remote server

Related Question