CODEWITHSUNDEEP
sqlms-access
ahsoe
ahsoe

Reputation: 103

Access SQL Syntax

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.

Upvotes: 0

Views: 56

Answers (2)

Serg
Serg

Reputation: 22811

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.

Upvotes: 2

Oto Shavadze
Oto Shavadze

Reputation: 42853

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

Upvotes: 2

Related Questions