By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,635 Members | 917 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,635 IT Pros & Developers. It's quick & easy.

SQL syntax for Null?

P: n/a
Is there a difference?

A) WHERE (IsNull(qry100.Cat_ID)=True))

--or--

B) WHERE qry100.Cat_ID Is Null

I'm thinking A is better because it is less ambiguous (?)

appreciate your thoughts on this... thx

Nov 12 '05 #1
Share this Question
Share on Google+
15 Replies


P: n/a
(B) should be more efficient.

The expression:
WHERE qry100.Cat_ID Is Null
is evaluated by JET, the query engine in Access, so it can take advantage of
any index on the field.

IsNull() if a VBA function. That means JET has to pass the expression to VBA
on each row and then compare the return value to True.

Avoiding VBA function where they are not needed also makes it easier to
upsize your app if you ever need to do that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"deko" <dj****@hotmail.com> wrote in message
news:JM*****************@newssvr27.news.prodigy.co m...
Is there a difference?

A) WHERE (IsNull(qry100.Cat_ID)=True))

--or--

B) WHERE qry100.Cat_ID Is Null

I'm thinking A is better because it is less ambiguous (?)

appreciate your thoughts on this... thx

Nov 12 '05 #2

P: n/a
On Sun, 19 Oct 2003 02:39:12 GMT, "Allen Browne"
<al*********@SeeSig.invalid> wrote:
(B) should be more efficient.

The expression:
WHERE qry100.Cat_ID Is Null
is evaluated by JET, the query engine in Access, so it can take advantage of
any index on the field.

IsNull() if a VBA function. That means JET has to pass the expression to VBA
on each row and then compare the return value to True.

Avoiding VBA function where they are not needed also makes it easier to
upsize your app if you ever need to do that.


Also, it is not necessary to compare the result of a function that returns
a boolean with True or False since it is already a boolean True or False.
Furthermore, in the cases where you -do- want to compare a value with True
or False (such as with a form control where not doing so is known to cause
problems), you should always compare with False, never with True. This is
because, technically, any non-zero value is True in VB or Jet SQL, but a
False is always zero. So, always say =False or <>False, never =True.
Nov 12 '05 #3

P: n/a
great tip! thanks!

just curious... do I have to re-write all my queries if I upsize to MSDE?

I have a lot of queries that use data that comes form user input (combo box,
or whatever) on a form. For example:

SELECT SUM(Amount) AS TypeTotal, TxType_ID
FROM tblTxJournal
WHERE (Year([TxDate])=Forms!frmMain!frm3.Form!cbx3Year) And
([TxType_ID]=Forms!frmMain!frm3.Form!cbx3TxType)
GROUP BY TxType_ID;

I'll be ready to go to MSDE shortly (mainly to accommodate more users), but
it will be a long time before this needs to go on SQL server...

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:8k********************************@4ax.com...
On Sun, 19 Oct 2003 02:39:12 GMT, "Allen Browne"
<al*********@SeeSig.invalid> wrote:
(B) should be more efficient.

The expression:
WHERE qry100.Cat_ID Is Null
is evaluated by JET, the query engine in Access, so it can take advantage ofany index on the field.

IsNull() if a VBA function. That means JET has to pass the expression to VBAon each row and then compare the return value to True.

Avoiding VBA function where they are not needed also makes it easier to
upsize your app if you ever need to do that.


Also, it is not necessary to compare the result of a function that returns
a boolean with True or False since it is already a boolean True or False.
Furthermore, in the cases where you -do- want to compare a value with True
or False (such as with a form control where not doing so is known to cause
problems), you should always compare with False, never with True. This is
because, technically, any non-zero value is True in VB or Jet SQL, but a
False is always zero. So, always say =False or <>False, never =True.

Nov 12 '05 #4

P: n/a
TC

"Allen Browne" <al*********@SeeSig.invalid> wrote in message
news:kP********************@news-server.bigpond.net.au...
(B) should be more efficient.

The expression:
WHERE qry100.Cat_ID Is Null
is evaluated by JET, the query engine in Access, so it can take advantage of any index on the field.


Not if nulls ain't indexed! I suspect (but am not certain) that this would
always do a full table scan.

TC

Nov 12 '05 #5

P: n/a
> great tip! thanks!

just curious... do I have to re-write all my queries if I upsize to MSDE?
You've just proven Steve's example:

Access MSDE/SQL Server
True : -1 1
False: 0 0

If you link the tables through ODBC you shouldn't have compatibility
problems, but performance is likely to be worse. You will need to optimise
the code, e.g. pass-through queries, late binding.
I'll be ready to go to MSDE shortly (mainly to accommodate more users),


MSDE is the restricted edition of SQL Server and the restrictions are:
- Max. 5 concurrent users.
- Max. 2 GB DB size.

I've seen Access-DB's performing well with more than 5 concurrent users.

If the number of users is the reason for your migration you should re-think
the target platform. As mentioned, MSDE==SQL Server.

Peter

--
No mails please.
Nov 12 '05 #6

P: n/a
TC, open a table in design view.
Open the Indexes dialog (View menu).

One of the properties in the lower pane of the dialog is, "Ignore Nulls".
Provided that setting is No, Access will be able to match the Nulls without
a table scan.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"TC" <a@b.c.d> wrote in message news:1066550022.148889@teuthos...

"Allen Browne" <al*********@SeeSig.invalid> wrote in message
news:kP********************@news-server.bigpond.net.au...
(B) should be more efficient.

The expression:
WHERE qry100.Cat_ID Is Null
is evaluated by JET, the query engine in Access, so it can take
advantage of
any index on the field.


Not if nulls ain't indexed! I suspect (but am not certain) that this would
always do a full table scan.

Nov 12 '05 #7

P: n/a
Peter Doering <no****@doering.org> wrote in news:bmtj4i$qppu8$1@ID-
204768.news.uni-berlin.de:
You've just proven Steve's example:

Access MSDE/SQL Server
True : -1 1
False: 0 0

From the help file (XP):

******** start quote ********
Boolean variables are stored as 16-bit (2-byte) numbers, but they can only
be True or False. Boolean variables display as either True or False (when
Print is used) or #TRUE# or #FALSE# (when Write # is used). Use the
keywords True and False to assign one of the two states to Boolean
variables.
When other numeric types are converted to Boolean values, 0 becomes False
and all other values become True. When Boolean values are converted to
other data types, False becomes 0 and True becomes -1.
******** end quote ********

It seems, a Boolean True has all (16) bits up, while a Boolean False has
all bits down. So, in my opinion, itís a bit of a stretch to equate -1 and
True. In arithmetic, VBA sees a two byte variable and when trying to do
something with it, seems to assume itís a two byte number, that is, an
integer.

If we start from the assumption that False is Zero then Not False or True
ups all the down bits. That is <00000000 00000000> becomes <11111111
11111111>. And what is this as an integer? -1, Right? This seems to me to
more an incidental outcome of how numbers are stored.

In SQL and JET, I believe, (but Iím not sure), Booleans refer to specific
bits of a byte; thus 8 Booleans can be stored in one byte. Of course, if
tri-state is allowed, these seem to use a second byte to keep track of
that. Since the management of which bit is which Boolean is internal, I
suspect it would be unwise to mess with these bits directly.

And, of course, Access itself can use these three technologies; I think it
seldom or never deals with Booleans itself.

If anyone wants to mess with VBA Booleans directly I offer this bit of code
as a beginning from which you might want to begin an exploration which
could waste a perfectly good Sunday.

Option Base 0
Option Explicit

Private Declare Sub CopyMemory _
Lib "kernel32" Alias "RtlMoveMemory" _
(Destination As Any, Source As Any, ByVal Length As Long)

Sub temp()
Dim a() As Byte
Dim b As Boolean
Dim i As Integer
Dim l As Long
Dim m As Long
Dim s As String
Dim z As Long

b = True
m = LenB(b)
l = m - 1

ReDim a(l)

CopyMemory a(0), b, m
For z = 0 To l
s = s & CStr(a(z))
If z <> 1 Then s = s & "."
Next z

MsgBox s

CopyMemory i, b, m

MsgBox i
End Sub

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #8

P: n/a
Lyle,
You've just proven Steve's example:

Access MSDE/SQL Server
True : -1 1
False: 0 0


From the help file (XP):
....


I'm not planning to waste my perfect Sunday ;-). What I'm referring to is
following statement from A97 help (search for "True <keyword>"):

<quote>
The True keyword has a value equal to -1.
</quote>

Peter

--
No mails please.
Nov 12 '05 #9

P: n/a
I checked my tables according to the procedure you described below. I
discovered that (in most all of my tables) some fields apparently had
duplicate indexes. For example:

Index Name -- FieldName
TxType_ID -- TxType_ID
PrimaryKey -- TxType_ID

Can I delete the TxType_ID index?
Is it okay to rename these indexes?
"Allen Browne" <al*********@SeeSig.invalid> wrote in message
news:JE********************@news-server.bigpond.net.au...
TC, open a table in design view.
Open the Indexes dialog (View menu).

One of the properties in the lower pane of the dialog is, "Ignore Nulls".
Provided that setting is No, Access will be able to match the Nulls without a table scan.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"TC" <a@b.c.d> wrote in message news:1066550022.148889@teuthos...

"Allen Browne" <al*********@SeeSig.invalid> wrote in message
news:kP********************@news-server.bigpond.net.au...
(B) should be more efficient.

The expression:
WHERE qry100.Cat_ID Is Null
is evaluated by JET, the query engine in Access, so it can take advantage
of
any index on the field.


Not if nulls ain't indexed! I suspect (but am not certain) that this

would always do a full table scan.


Nov 12 '05 #10

P: n/a
On Mon, 20 Oct 2003 00:11:32 GMT, "deko" <dj****@hotmail.com> wrote:

Yes.
And you may also want to clear out Tools / Options / Tables /
AutoIndex.

-Tom.
I checked my tables according to the procedure you described below. I
discovered that (in most all of my tables) some fields apparently had
duplicate indexes. For example:

Index Name -- FieldName
TxType_ID -- TxType_ID
PrimaryKey -- TxType_ID

Can I delete the TxType_ID index?
Is it okay to rename these indexes?


Nov 12 '05 #11

P: n/a
Yes. DO delete the indexes where they are duplicated.

To prevent duplicate indexes in future, go to:
Tools | Options | Tables/Queries
and delete everything in the box:
AutoIndex on Import/Create

If you leave those entries there, Access automatically adds an index to any
field that has a name ending in ID, Num, Code etc. Since you also specified
TxType_ID as your primary key, you have a spurious index.

The same thing happens with foreign keys, though it is less obvious. If you
have a foreign key with a name ending in "ID", Access automatically indexes
it. Then if you create an index on the field, you have a duplicate. Then if
you create a relationship with referential integrity (RI) between this table
and the primary one, Access creates *another* hidden index to maintain the
RI, so you now have the field triple-indexed!

In general, don't let Access auto-index, and don't create indexes on your
foreign keys: use a relationship with RI instead.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"deko" <dj****@hotmail.com> wrote in message
news:UK*************@newssvr14.news.prodigy.com...
I checked my tables according to the procedure you described below. I
discovered that (in most all of my tables) some fields apparently had
duplicate indexes. For example:

Index Name -- FieldName
TxType_ID -- TxType_ID
PrimaryKey -- TxType_ID

Can I delete the TxType_ID index?
Is it okay to rename these indexes?
"Allen Browne" <al*********@SeeSig.invalid> wrote in message
news:JE********************@news-server.bigpond.net.au...
TC, open a table in design view.
Open the Indexes dialog (View menu).

One of the properties in the lower pane of the dialog is, "Ignore Nulls".
Provided that setting is No, Access will be able to match the Nulls

without
a table scan.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"TC" <a@b.c.d> wrote in message news:1066550022.148889@teuthos...

"Allen Browne" <al*********@SeeSig.invalid> wrote in message
news:kP********************@news-server.bigpond.net.au...
> (B) should be more efficient.
>
> The expression:
> WHERE qry100.Cat_ID Is Null
> is evaluated by JET, the query engine in Access, so it can take

advantage
of
> any index on the field.

Not if nulls ain't indexed! I suspect (but am not certain) that this

would always do a full table scan.

Nov 12 '05 #12

P: n/a
super. thanks for the tip.

"Allen Browne" <al*********@SeeSig.invalid> wrote in message
news:Yb*********************@news-server.bigpond.net.au...
Yes. DO delete the indexes where they are duplicated.

To prevent duplicate indexes in future, go to:
Tools | Options | Tables/Queries
and delete everything in the box:
AutoIndex on Import/Create

If you leave those entries there, Access automatically adds an index to any field that has a name ending in ID, Num, Code etc. Since you also specified TxType_ID as your primary key, you have a spurious index.

The same thing happens with foreign keys, though it is less obvious. If you have a foreign key with a name ending in "ID", Access automatically indexes it. Then if you create an index on the field, you have a duplicate. Then if you create a relationship with referential integrity (RI) between this table and the primary one, Access creates *another* hidden index to maintain the
RI, so you now have the field triple-indexed!

In general, don't let Access auto-index, and don't create indexes on your
foreign keys: use a relationship with RI instead.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"deko" <dj****@hotmail.com> wrote in message
news:UK*************@newssvr14.news.prodigy.com...
I checked my tables according to the procedure you described below. I
discovered that (in most all of my tables) some fields apparently had
duplicate indexes. For example:

Index Name -- FieldName
TxType_ID -- TxType_ID
PrimaryKey -- TxType_ID

Can I delete the TxType_ID index?
Is it okay to rename these indexes?
"Allen Browne" <al*********@SeeSig.invalid> wrote in message
news:JE********************@news-server.bigpond.net.au...
TC, open a table in design view.
Open the Indexes dialog (View menu).

One of the properties in the lower pane of the dialog is, "Ignore Nulls". Provided that setting is No, Access will be able to match the Nulls

without
a table scan.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"TC" <a@b.c.d> wrote in message news:1066550022.148889@teuthos...
>
> "Allen Browne" <al*********@SeeSig.invalid> wrote in message
> news:kP********************@news-server.bigpond.net.au...
> > (B) should be more efficient.
> >
> > The expression:
> > WHERE qry100.Cat_ID Is Null
> > is evaluated by JET, the query engine in Access, so it can take
advantage
> of
> > any index on the field.
>
> Not if nulls ain't indexed! I suspect (but am not certain) that this

would
> always do a full table scan.


Nov 12 '05 #13

P: n/a
TC

"Lyle Fairfield" <Mi************@Invalid.Com> wrote in message
news:Xn*******************@130.133.1.4...

(snip)
In ... JET, I believe, (but I'm not sure), Booleans refer to specific
bits of a byte; thus 8 Booleans can be stored in one byte. Of course, if
tri-state is allowed, these seem to use a second byte to keep track of
that.


Jet allocates a null/notnull bit for each field - seperate from the bits
that store the actual value.

But for booleans, they made the (ahem) brain-dead decision to use the
null/notnull bit to store the actual boolean value!

So in Jet, boolean fields can never be Null :-(

TC

Nov 12 '05 #14

P: n/a
TC
Ok, forgot that!

TC
"Allen Browne" <al*********@SeeSig.invalid> wrote in message
news:JE********************@news-server.bigpond.net.au...
TC, open a table in design view.
Open the Indexes dialog (View menu).

One of the properties in the lower pane of the dialog is, "Ignore Nulls".
Provided that setting is No, Access will be able to match the Nulls without a table scan.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"TC" <a@b.c.d> wrote in message news:1066550022.148889@teuthos...

"Allen Browne" <al*********@SeeSig.invalid> wrote in message
news:kP********************@news-server.bigpond.net.au...
(B) should be more efficient.

The expression:
WHERE qry100.Cat_ID Is Null
is evaluated by JET, the query engine in Access, so it can take advantage
of
any index on the field.


Not if nulls ain't indexed! I suspect (but am not certain) that this

would always do a full table scan.


Nov 12 '05 #15

P: n/a
TC
But, the duplicates don't take any space!

See google groups message ID:

<39*************************@posting.google.com>

:-)

TC
"Allen Browne" <al*********@SeeSig.invalid> wrote in message
news:Yb*********************@news-server.bigpond.net.au...
Yes. DO delete the indexes where they are duplicated.

To prevent duplicate indexes in future, go to:
Tools | Options | Tables/Queries
and delete everything in the box:
AutoIndex on Import/Create

If you leave those entries there, Access automatically adds an index to any field that has a name ending in ID, Num, Code etc. Since you also specified TxType_ID as your primary key, you have a spurious index.

The same thing happens with foreign keys, though it is less obvious. If you have a foreign key with a name ending in "ID", Access automatically indexes it. Then if you create an index on the field, you have a duplicate. Then if you create a relationship with referential integrity (RI) between this table and the primary one, Access creates *another* hidden index to maintain the
RI, so you now have the field triple-indexed!

In general, don't let Access auto-index, and don't create indexes on your
foreign keys: use a relationship with RI instead.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"deko" <dj****@hotmail.com> wrote in message
news:UK*************@newssvr14.news.prodigy.com...
I checked my tables according to the procedure you described below. I
discovered that (in most all of my tables) some fields apparently had
duplicate indexes. For example:

Index Name -- FieldName
TxType_ID -- TxType_ID
PrimaryKey -- TxType_ID

Can I delete the TxType_ID index?
Is it okay to rename these indexes?
"Allen Browne" <al*********@SeeSig.invalid> wrote in message
news:JE********************@news-server.bigpond.net.au...
TC, open a table in design view.
Open the Indexes dialog (View menu).

One of the properties in the lower pane of the dialog is, "Ignore Nulls". Provided that setting is No, Access will be able to match the Nulls

without
a table scan.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"TC" <a@b.c.d> wrote in message news:1066550022.148889@teuthos...
>
> "Allen Browne" <al*********@SeeSig.invalid> wrote in message
> news:kP********************@news-server.bigpond.net.au...
> > (B) should be more efficient.
> >
> > The expression:
> > WHERE qry100.Cat_ID Is Null
> > is evaluated by JET, the query engine in Access, so it can take
advantage
> of
> > any index on the field.
>
> Not if nulls ain't indexed! I suspect (but am not certain) that this

would
> always do a full table scan.


Nov 12 '05 #16

This discussion thread is closed

Replies have been disabled for this discussion.