Access SQL Syntax

Question

I have enherited an Access-database with some queries. It is an account-database with information on the different accounts. Specifically IBAN-numbers for each account.

One of the queries is this, where we compare IBAN-numbers from the database with IBAN-numbers from the imported Id-table:

SELECT CAMTaccounts.IBAN, CAMTaccounts.Comment
FROM CAMTaccounts LEFT JOIN Id ON CAMTaccounts.[IBAN] = Id.[IBAN]
WHERE (((Id.IBAN) Is Null));

I thought I understood the SQL-language to some degree coming from SQL Server, but this statement, I cannot understand.

To me, this is equivalent to writing:

Select CAMTaccounts.*
From CAMTaccounts Left Outer Join Id On CAMTaccounts.IBAN = Id.IBAN
Where Id.IBAN Is Null

and this join, to me, does not make any sense.

But clearly, I am not understanding this correctly. I was hoping, that some of you could explain my flawed logic to me.

Thanks.

Because we join on = Id.IBAN and afterwards specify that Id.IBAN should be Null, which it never is and therefore the join is redundant. We only get data from the left table CAMTaccounts, but no data from the right Id-table. — ahsoe
– ahsoe, Commented Apr 6, 2017 at 6:49

Serg · Accepted Answer · 2017-04-06 06:50:34Z

2

This query will return all rows from CAMTaccounts which have no matching Id rows. This is known as Anti-join query. Excessive parenthesis really make no sense, most probably generated with a tool.

answered Apr 6, 2017 at 6:50

Serg

22.8k5 gold badges23 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ahsoe Over a year ago

Yes, I think it is generated with Query Designer in Access.

Oto Shavadze · Accepted Answer · 2017-04-06 06:49:49Z

2

Suppose that CAMTaccounts.IBAN have values

A
B

And Id.IBAN have values

B
C

Then this query WITHOUT WHERE clause outputs something like this

`CAMTaccounts.IBAN` | `Id.IBAN`
 ---------------------------------
 A                  | NULL
 B                  | B

You can easily see what will result from this, if add Where Id.IBAN Is Null clause.

So, this query will give you result from CAMTaccounts table, when IBAN column doesn't contains value from Id.IBAN column

answered Apr 6, 2017 at 6:49

Oto Shavadze

43.3k56 gold badges168 silver badges248 bronze badges

1 Comment

ahsoe Over a year ago

Thanks, both Oto and Serg for the answers.

Collectives™ on Stack Overflow

Access SQL Syntax

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related