Are brackets in the WHERE clause standard sql
Yes. You can use parenthesis to bind components of where clauses. This isn't necessary in your example, but if you had multiple and
and or
components, you might need parenthesis to either ensure correct order of operations or simply to self-document the query.
Example 1:
select *
from foo
where
(class='A' and subclass='B')
or (class='C' and subclass='D')
In example 1, the parens aren't strictly required because and
binds more tightly than or
, but if you had multiple or
conditions tied by and
you would need it to get correct results, as in example 2 below.
Example 2:
select *
from foo
where
(class='A' or class='B')
and (subclass='C' or subclass='D')
I use them in either case, because I don't like having to parse the sql in my head the same way the query optimizer does -- I'd rather be explicit about it and more quickly understand what the intent is.
MySQL where and clauses in brackets
Yes, they are the same. The brackets here play the same role as all brackets do in conditionals.
When used with AND
then they don't have any impact on the condition, as long as it only has AND
within it. Same applies to statements that only has OR
within. When used with both AND
and OR
then they do have an impact as long as they are used on sub-operations and not on the condition as a whole:
A AND (B OR C) != (A AND B) OR C
In SQL, brackets are also used for IN
clause, EXISTS
, sub-queries and other fundamentals
Parentheses Placement in Where Statements
if you wanted a variable @x to be not equal to 1 or 2, then you could write
@X <> 1 AND @X <> 2
but if you wrote
@X <> 1 OR @X <> 2
then it will be true for any number, so if @X was 1, then the second half is true, making the whole thing true
WHERE TRACK_GROUP = 'ED Tracking Group' AND PT_ACUITY='3 - Two+ Resources' AND CHECKIN_DATE_TIME > @StartDate and CHECKIN_DATE_TIME < @EndDate
AND
(REASON_FOR_VISIT Not Like '%asthma%' --it needs to be NOT like any of these, so all the conditions have to be true
AND REASON_FOR_VISIT Not Like '%psych%') --that is to say, each of the conditions is NOT like the value in quotes
AND REASON_FOR_VISIT Not Like '%suicide%'
AND REASON_FOR_VISIT Not Like '%suicidal%'
AND REASON_FOR_VISIT Not Like '%homicide%'
AND REASON_FOR_VISIT Not Like '%homicidal%'
AND REASON_FOR_VISIT Not Like '%FD-12%'
AND REASON_FOR_VISIT Not Like '%behavioral%'
AND REASON_FOR_VISIT Not Like '%overdose%'
AND REASON_FOR_VISIT Not Like '%aggression%' )
What is the use of the square brackets [] in sql statements?
The brackets are required if you use keywords or special chars in the column names or identifiers. You could name a column [First Name]
(with a space) – but then you'd need to use brackets every time you referred to that column.
The newer tools add them everywhere just in case or for consistency.
What does the SQL standard say about parentheses in SQL UNION/EXCEPT/INTERSECT statements?
There's no need for brackets/parenthesis in a UNION statement.
MySQL is the only one I'm aware of at this moment, which allows you to define ORDER BY
and LIMIT
clauses specific to each query as long as the query is enclosed in brackets -- standard SQL only allows a ORDER BY
for the final result. The GROUP BY
and HAVING
clauses are specific to each query that makes up the UNION'd statement.
MySQL supports:
(SELECT a.column
FROM A_TABLE a
ORDER BY a.column DESC)
UNION
SELECT b.column
FROM B_TABLE b
...which will cause no end of grief if you want/need to port to other databases.
Standard SQL only allows:
SELECT a.column
FROM A_TABLE a
UNION
SELECT b.column
FROM B_TABLE b
ORDER BY column DESC
[] brackets in sql statements
The [] marks the delimitation of a identifier, so if you have a column whose name contains spaces like Order Qty you need to enclose it with [] like:
select [Order qty] from [Client sales]
They are also to escape reserved keywords used as identifiers
How is where clause in SQL evaluated?
SELECT * FROM mytable
WHERE name='Anton' AND lastname='Gildebrand' OR age > 18 AND country='Sweden'
Is equal to:
SELECT * FROM mytable
WHERE (name='Anton' AND lastname='Gildebrand') OR (age > 18 AND country='Sweden')
Sources: TSQL, MySQL, Oracle, PostgreSQL
Meaning of square brackets [] in MS-SQL table designer?
The square brackets []
are used to delimit identifiers. This is necessary if the column name is a reserved keyword or contains special characters such as a space or hyphen.
Some users also like to use square brackets even when they are not necessary.
From MSDN:
Delimited identifiers
Are enclosed in double quotation marks (") or brackets ([ ]). Identifiers that comply with the rules for the format of identifiers may or may not be delimited.
SELECT *
FROM [TableX] --Delimiter is optional.
WHERE [KeyCol] = 124 --Delimiter is optional.
Identifiers that do not comply with all of the rules for identifiers must be delimited in a Transact-SQL statement.
SELECT *
FROM [My Table] --Identifier contains a space and uses a reserved keyword.
WHERE [order] = 10 --Identifier is a reserved keyword.
Related Topics
Update X Set Y = Null Takes a Long Time
Sql Server Database Change Workflow Best Practices
Query to Check Overlapping Ranges in SQL Server
Index Spanning Multiple Tables in Postgresql
Displaying Columns as Rows in SQL Server 2005
Why Sum(Null) Is Not 0 in Oracle
Standard SQL Alternative to Oracle Decode
What Is The Most Efficient Way to Count Rows in a Table in Sqlite
Sql 2008 Vs 2012 Error: Incorrect Syntax Near The Keyword 'Compute'
Ora-01779: Cannot Modify a Column Which Maps to a Non Key-Preserved Table
Unique Date Range Fields in SQL Server 2008
Postgresql: Defining a Primary Key on a Large Database
Sql - Query to Insert a Column Value If It Does Not Exist in That Column
Is It Faster to Check If Length = 0 Than to Compare It to an Empty String
Join Table Twice - on Two Different Columns of The Same Table
How to Get String After Character Oracle
T-Sql: Comparing Two Tables - Records That Don't Exist in Second Table