Weird predicate syntax in queries

  • Thread starter Thread starter MikeB
  • Start date Start date
M

MikeB

Why, when I create a query in Access with the design view does Access
put so many brackets in the WHERE clause?

eg.:

SELECT Count(Games.GameNumber) AS [Rated Games]
FROM Games
WHERE (((Games.White)=[Player?]) AND ((Games.Rated)=True)) OR
(((Games.Rated)=True) AND ((Games.Black)=[Player?]));


Isn't this syntactically the same as:

SELECT Count(Games.GameNumber) AS [Rated Games]
FROM Games
WHERE (Games.Rated=True) AND (Games.White=[Player?] OR
Games.Black=[Player?]);

If I open a query in the design view, Access keeps changing the WHERE
syntax and it bothers me.

I'm having a hard time wrapping my head around the set theory of SQL
queries.
 
Yes, the two queries are the same.

Not really sure why Access insists on putting so many parentheses.
 
Why, when I create a query in Access with the design view does Access
put so many brackets in the WHERE clause?

eg.:

SELECT Count(Games.GameNumber) AS [Rated Games]
FROM Games
WHERE (((Games.White)=[Player?]) AND ((Games.Rated)=True)) OR
(((Games.Rated)=True) AND ((Games.Black)=[Player?]));


Isn't this syntactically the same as:

SELECT Count(Games.GameNumber) AS [Rated Games]
FROM Games
WHERE (Games.Rated=True) AND (Games.White=[Player?] OR
Games.Black=[Player?]);
Click to expand...

Yes. I've never understood the program's madness for extra parentheses. They
can be deleted without harm, and if you save the query from the SQL window and
avoid opening it in the query grid window, it will keep your changes
(usually).
If I open a query in the design view, Access keeps changing the WHERE
syntax and it bothers me.

I'm having a hard time wrapping my head around the set theory of SQL
queries.
Click to expand...

The way a WHERE clause works can be understood using "Boolean Algebra" -
something I was lucky enough to learn way before I got into databases, about
1962 in fact. The AND and OR operators work like + and - operators in
arithmatic, with rigid logical rules. If the WHERE clause evaluates to TRUE
then the record is retrieved; if it evaluates to FALSE (or to NULL), the
record will be excluded.

Sets and the notorious Venn diagrams (overlapping circles showing set
membership) are another way of visualizing the actions of Boolean logic.
 
MikeB said:
Why, when I create a query in Access with the design view does Access
put so many brackets in the WHERE clause?

eg.:

SELECT Count(Games.GameNumber) AS [Rated Games]
FROM Games
WHERE (((Games.White)=[Player?]) AND ((Games.Rated)=True)) OR
(((Games.Rated)=True) AND ((Games.Black)=[Player?]));


Isn't this syntactically the same as:

SELECT Count(Games.GameNumber) AS [Rated Games]
FROM Games
WHERE (Games.Rated=True) AND (Games.White=[Player?] OR
Games.Black=[Player?]);

If I open a query in the design view, Access keeps changing the WHERE
syntax and it bothers me.

I'm having a hard time wrapping my head around the set theory of SQL
queries.
Click to expand...

Being picky, [] are brackets, () are parentheses. ;-)
MikeR
 
Back
Top