sqlmysqlbrackets

The role of brackets in SQL query?

What is the role of brackets in the below query? Square brackets and backticks are used to escape the keywords in SQL queries but I could not find a proper explanation for using brackets.

SELECT (authorID) FROM AUTHOR;

In MySQL, this works but when you include two columns inside this it will give you an error saying 'the operand should contain one column'. It does not give an error when enclosing each column inside brackets and separating each with a comma like below.

SELECT (authorID),(authorName) FROM AUTHOR;
Solution

  • Brackets, or parentheses, are redundant in the context you show. There's no harm, but they aren't needed.

    Just like in algebra, where the following two expressions are functionally the same:

    x + y
(x + y)

    The extra parentheses do not change the result of this expression.

    You may use parentheses in algebra to change operator precedence. Since multiplication binds tighter than addition, the following two expressions are not the same:

    x + y * z
(x + y) * z

    Parentheses are really redundant around an expression that is so simple that it's only a single variable.

    (x)

    This is similar to your first example in SQL, where the parentheses are around each respective column in the select-list:

    SELECT (authorID),(authorName) FROM AUTHOR;

    There's no need or purpose for these parentheses in MySQL or most other implementations of SQL (I don't use Microsoft Access so that might be an exception).

    In SQL, a special use of parentheses that is not redundant is when you put a list of multiple expressions into a row value constructor. This allows comparison of tuples:

    (col1, col2) = (123, 456)
(col1, col2, col3) IN ((1, 2, 3), (4, 5, 6), (7, 8, 9))

    In some contexts like the VALUES statement, a row value constructor looks like ROW(...list...).

    But in the select-list, every expression must be a scalar expression. That is, a column, an expression, or a literal value, but not a row value.

    Likewise, in a row value constructor, each item in that list must be a scalar expression. In other words, you can't nest another row value constructor inside a row value constructor.

    In this example I'm using row values, but the expression in the select-list is the comparison, so it's just a boolean result:

    mysql> select (1, 2) = (1, 2) as `same`;
+------+
| same |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

    But I can't use another row value inside the row value:

    mysql> select (1, 2) = (1, (2,3)) as `same`;
ERROR 1241 (21000): Operand should contain 1 column(s)