Opposite sql of innerj join (1 Viewer)

jaryszek · Jan 15, 2019

Hi,

i am using query to have common rows like here with inner join:

Code:

SELECT DISTINCTROW temp.DisplayName, temp.Topology, temp.Tier, temp.Platform, temp.OSDistro, temp.OSVersion, temp.Origin, temp.PlatformRepository, temp.ImageID, temp.Publisher, temp.Offer, temp.SKU, temp.Version, temp.Urn, temp.OS, temp.SystemNameFull, temp.Customer
FROM Temp_Images AS temp inner join tblImages AS t1 ON (temp.Customer = t1.Customer or (temp.Customer is Null AND t1.Customer is Null)) 
AND (temp.SystemNameFull = t1.SystemNameFull or (temp.SystemNameFull is Null AND t1.SystemNameFull is Null)) 
AND (temp.OS = t1.OS or (temp.OS is Null AND t1.OS is Null)) 
AND (temp.Urn = t1.Urn or (temp.Urn is Null AND t1.Urn is Null)) 
AND (temp.Version = t1.Version or (temp.Version is Null AND t1.Version is Null)) 
AND (temp.SKU = t1.SKU or (temp.SKU is Null AND t1.SKU is Null)) 
AND (temp.Offer = t1.Offer or (temp.Offer is Null AND t1.Offer is Null)) 
AND (temp.Publisher = t1.Publisher or (temp.Publisher is Null AND t1.Publisher is Null)) 
AND (temp.ImageID = t1.ImageID or (temp.ImageID is Null AND t1.ImageID is Null)) 
AND (temp.PlatformRepository = t1.PlatformRepository or (temp.PlatformRepository is Null AND t1.PlatformRepository is Null)) 
AND (temp.Origin = t1.Origin or (temp.Origin is Null AND t1.Origin is Null)) 
AND (temp.OSVersion = t1.OSVersion or (temp.OSVersion is Null AND t1.OSVersion is Null)) 
AND (temp.OSDistro = t1.OSDistro or (temp.OSDistro is Null AND t1.OSDistro is Null)) 
AND (temp.Platform = t1.Platform or (temp.Platform is Null AND t1.Platform is Null)) 
AND (temp.Tier = t1.Tier or (temp.Tier is Null AND t1.Tier is Null)) 
AND (temp.Topology = t1.Topology or (temp.Topology is Null AND t1.Topology is Null)) 
AND (temp.DisplayName = t1.DisplayName or (temp.DisplayName is Null AND t1.DisplayName is Null))
AND (temp.Topology = t1.Topology or (temp.Topology is Null AND t1.Topology is Null));

what ill be the opposite syntax of it to get not common values? outer join in access doesnt exist?
How can i change the query ?

Best,
Jacek

jaryszek · Jan 15, 2019

Hi, i am trying with :

Code:

SELECT * from Temp_Images as temp
LEFT JOIN tblImages as t1 on (temp.Customer = t1.Customer or (temp.Customer is Null AND t1.Customer is Null)) 
WHERE t1.Customer is Null

what do you think? How to handle nulls here?

Best,
Jacek

June7 · Jan 15, 2019

I have never seen an OR in JOIN clause. And joining on Null really makes no sense.

Show sample raw data and desired output.

jaryszek · Jan 15, 2019

Hi June7,

this is working very well.

Code:

SELECT * from Temp_Images as temp
LEFT JOIN tblImages as t1 on (temp.Customer = t1.Customer or (temp.Customer is Null AND t1.Customer is Null)) 
WHERE t1.Customer is Null

if i have a lot of fields some of them can be empty so join would not handle them and would not see the rows. In Or in join you can get all rows which are the same even they have nulls. So this example working like a charm and not which fields are exists in one table and in second not even with nulls.

Best,
Jacek

plog · Jan 15, 2019

what ill be the opposite syntax of it to get not common values?

So, did you get your initial issue resolved? Because it makes little sense as stated.

For example,

Table1
F1, F2
Steve, 8
Larry, 9

Table2
F1, F2
Steve, 8
Larry, 9

If you ran a query to return rows that didn't match you would get this result:

Query1
F1, F2
Steve, 8
Larry, 9

That's because Larry<>Steve and 8<>9. Each row in both tables don't match a row in the other table.

If you haven't resolved your issue, please demonstrate it with data. Provide 2 sets:

A. Starting data from your table(s). Include table and field names and enough data to cover all cases.

B. Expected data from your query. Show what data you hope to end up with when you feed in the data from A.

jaryszek · Jan 15, 2019

Hi plog, thank you.

These rows are the same and working like a charm with :

SELECT * from Table1 as t1
LEFT JOIN Table2 as t2 on (t1.F1 = t2.F1 or (t1.F1 is Null AND t2.F1 is Null))
AND (t1.F2 = t2.F2 or (t1.F2 is Null AND t2.F2 is Null))
WHERE t2.F1 is Null AND t2.F2 is Null

query resilt is Null and this is correct.
If i changed 8 to 11in Table 1 i will get:

This is ok, what it is not necessarry here it is that i no need t2 fields in query and i do not know to avoid them.

Best,
Jacek

plog · Jan 15, 2019

Only put the fields you want to show up in the SELECT

SELECT t1.F1, t1.FS FROM...

Frothingslosh · Jan 15, 2019

June7 said:
I have never seen an OR in JOIN clause. And joining on Null really makes no sense.

Show sample raw data and desired output.

OR in JOIN clauses are perfectly valid, although it's far more common to see in a WHERE clause.

It's most commonly done when you want to restrict the records being pulled in to the temporary table being created behind the scenes, as opposed to after the table is created. In theory, OR in the JOIN clauses are applied before records are added, while OR conditions in WHERE clauses occur after the temporary table is built. In execution, it's a bit more complex.

I don't know about the specifics in Access, but in SQL server, it USUALLY doesn't make a difference, as the query optimizer is pretty good about determining the most efficient time to restrict records based on join conditions and WHERE clauses. I've actually run a bunch of tests on this, and the execution plans were the same, although that's partially because the plan is created and saved the first time a query is run. According to what I saw over on Stack Overflow, though, there are apparently times when it DOES make a perceptible difference. Not sure where, though, as the case I was researching it for was one where there was no difference found after testing, even after nuking the execution plans for each test.

Same deal applies to putting a null check in the join.

jaryszek · Jan 16, 2019

thank you Guys Frothingslosh and plog for help!

Best,
Jacek

Opposite sql of innerj join (1 Viewer)

jaryszek

Registered User.

jaryszek

Registered User.

June7

AWF VIP

jaryszek

Registered User.

plog

Banishment Pending

jaryszek

Registered User.

Attachments

plog

Banishment Pending

Frothingslosh

Premier Pale Stale Ale

jaryszek

Registered User.

Users who are viewing this thread