Questions tagged [sql-standard]
For questions concerning the SQL Standard itself (also known as the ANSI or ISO SQL Standard) including writing queries that conform to that standard. This tag is NOT to be used for questions about SQL Server Standard Edition.
96
questions
107
votes
11answers
89k views
SQL: SELECT All columns except some
Is there a way to SELECT all columns in a table, except specific ones? IT would be very convenient for selecting all the non-blob or non-geometric columns from a table.
Something like:
SELECT * -...
29
votes
2answers
64k views
CREATE INDEX vs ALTER TABLE ADD INDEX - MySQLism, or SQL Standard?
Just came across a weird issue, whereby depending on how I create an index, an index name is required.
http://dev.mysql.com/doc/refman/5.5/en/create-index.html
http://dev.mysql.com/doc/refman/5.5/en/...
29
votes
3answers
6k views
Why does ANSI SQL define SUM(no rows) as NULL?
The ANSI SQL standard defines (chapter 6.5, set function specification) the following behaviour for aggregate functions on empty result sets:
COUNT(...) = 0
AVG(...) = NULL
MIN(...) = NULL
MAX(...) = ...
23
votes
1answer
2k views
Is the optimisation fence behaviour of a CTE (WITH query) specified in the SQL:2008 standard? If so, where?
I see frequent references to WITH queries (common table expressions, or CTEs) acting as an optimisation fence, where the server isn't permitted to push filters down into the CTE queries, pull common ...
22
votes
2answers
1k views
Where can I find the first standardization of SQL, SQL-86?
This question is different but similar to this request looking for SQL-89.
The first draft of SQL is labeled SQL-86. Numerous references are made to it. Is this available to download? Wikipedia doesn'...
19
votes
3answers
607 views
What is the correct result for this query?
I came across this puzzle in the comments here
CREATE TABLE r (b INT);
SELECT 1 FROM r HAVING 1=1;
SQL Server and PostgreSQL return 1 row.
MySQL and Oracle return zero rows.
Which is correct? ...
13
votes
2answers
1k views
Where can I find the SQL standard document?
Where can I find a legal copy of the ISO SQL 2008 standard?
11
votes
1answer
3k views
ANSI/ISO plans for LIMIT standardization?
Are there currently plans to standardize one best way of limiting the number of results returned by a query?
The stack overflow question at Is there an ANSI SQL alternative to the MYSQL LIMIT ...
11
votes
2answers
2k views
Are there standard SQL functions with side effects?
Do the SQL standards define functions with side-effects?
For example, do they have functions for writing into files* or to update values in certain columns of a table when you do something like
...
11
votes
1answer
1k views
Does the SQL Spec require a GROUP BY in EXISTS ()
Microsoft currently permits this syntax.
SELECT *
FROM ( VALUES (1) ) AS g(x)
WHERE EXISTS (
SELECT *
FROM ( VALUES (1),(1) )
AS t(x)
WHERE g.x = t.x
HAVING count(*) > 1
);
Notice ...
9
votes
3answers
2k views
Is the keyword “ALIAS” actually used?
According to PostgreSQL 7.1 through 9.1 (now unsupported), ALIAS is listed as a reserved word, at least for SQL-99. Later versions do not show it - suggesting that it has been dropped as a reserved ...
8
votes
4answers
1k views
Best practices to maximize portability in SQL Server 2016
When it comes to developing the prototype of a solution, often the technologies has not been decided yet and might not be the same that will be used in the finished product.
In this scenarios I tend ...
8
votes
4answers
453 views
Which SQL standard is most broadly and completely supported?
I want to concentrate on one of the SQL language specs for learning and reference purposes. Which version of the SQL standard is most broadly supported across DBMS'es so that I can focus my time and ...
7
votes
1answer
1k views
Which DBMS are more standard compliant? [closed]
I find it frustrating that the same queries don't work in different DBMS.
Example, some DBMS (such as Microsoft SQL Server and MySQL) seems to support the information_schema table, while others (such ...
7
votes
1answer
1k views
Why does information_schema have “YES” and “NO” character strings rather than booleans?
I was taken by surprise to find that the is_nullable column for this query is typed as character varying (3) rather than boolean:
select
column_name,
data_type,
is_nullable
from
...
7
votes
2answers
673 views
Does SQL Server support custom DOMAINs?
PostgreSQL supports the DOMAIN specification, from SQL 2011 working draft spec,
A domain is a named user-defined object that can be specified as an alternative to a data type in certain places ...
7
votes
2answers
2k views
Single data type for imprecise date values, as allowed by ISO 8601
How can I store date and time values with reduced precision in a PostgreSQL type, and have them behave as date and/or time values?
ISO 8601 allows date values with reduced precision. ‘1964’, ‘1964-05’...
7
votes
1answer
76 views
the <set clause>'s <multiple column assignment>
I just know a little sql basics and need to write a plain sql syntax highlighter/checker
So I'm fighting through the standard...
I stumbled over somthing I'm not sure I understand it right, as I never ...
6
votes
5answers
4k views
How different are the top DB vendor's SQL (DML-) dialects from each other?
Does someone who has worked extensibly SQL-wise with at least two top DB products (such as Oracle, SQL Server, Informix, Sybase, DB2, Teradata) know how different the DB vendor's SQL dialects are from ...
6
votes
1answer
160 views
Are there any fully declarative implementations of SQL
I was recently bitten by a bug ( in my code ) where these two queries had drastically different runtimes:
select * from smalltable st
inner join bigtable bt on st.btid = bt.btid
select * from ...
6
votes
1answer
451 views
Where can I find the SQL-89 standard?
This question is different but similar to this request looking for SQL-86.
SQL 1989 is almost 30 years old. Is this spec freely available anywhere. The standard does not have a Wikipage or the like?
...
5
votes
2answers
2k views
Is Microsoft SQL Server 2016 fully ANSI SQL-92 compliant?
I am trying to find a compliance confirmation for MS SQL 2016 - if it is fully compliant with ANSI SQL-92 starndard.
I found this article on Microsoft Docs which states it is not, but it refers to ...
5
votes
2answers
505 views
Does the spec permit column-count mismatch on INSERT?
I'm just wondering if the spec permits excluding the column-list clause in an INSERT statement if you're inserting in fewer columns than the table provides. I see PostgreSQL does, and MySQL and SQL ...
5
votes
2answers
4k views
SET ANSI_NULLS at the database level versus setting it at the table level
I have generated schema-only script for my sql server 2008 R2, here is part of it:-
USE [master]
GO
/****** Object: Database [****] Script Date: 03/25/2015 12:17:09 ******/
CREATE DATABASE [****] ...
5
votes
1answer
2k views
Using a CREATE TABLE AS SELECT how do I specify a WITH condition (CTE)?
There is an old and deprecated command in PostgreSQL that predates CREATE TABLE AS SELECT (CTAS) called SELECT ... INTO .... FROM, it supports WITH clauses / Common Table Expressions (CTE). So, for ...
5
votes
1answer
282 views
Are view references in a query properly called “derived tables” as such?
While answering a question on stackoverflow, I presented a definition of derived tables:
A derived table is a complete query, inside of parentheses, that is used as if it were a real table.
But a ...
4
votes
3answers
468 views
Obtaining gratis copy of the SQL specification?
There used to be a public link to the JTC 1/SC 32 working drafts of 9075:2003, but the link I had has not worked since they started on the post-2003 revisions. Given that there are publicly available ...
4
votes
2answers
3k views
In PostgreSQL, what is the difference between a “Stored Procedure” and other types of functions?
I understand the distinction between:
Scalar functions
Set-Returning-Functions (SRF)s
Internal functions
Window functions
Aggregate functions of all sorts
User-Implemented functions (which in ...
4
votes
2answers
4k views
Difference between “<> ANY(…)” and “NOT = ANY(…)”?
Could you please explain the difference between these two operators?
sID != smth and not sID = smth.
At a first glance they seem perfectly equal. But they give different results.
4
votes
3answers
384 views
What actually is a SQL clause?
This may sound like an overly simple question but I'm not finding it easy to locate a proper answer.
To the question "What are sql clauses?" most of the resources on internet simply provide a list ...
4
votes
1answer
187 views
SQL “Standard” guide?
I am currently learning TSQL and am learning that were possible, I should use "standard" SQL dialect. Where can I find out which commands are "standard" and which are TSQL?
4
votes
2answers
103 views
How do i crack this non-ansi code blob
I've just started a new job about a month ago working for a software company that is really understaffed. I've been tasked with modernizing some of this old non-ansi SQL code and I'm having a ...
4
votes
2answers
5k views
Converting Tables to Ansi Padding On
I am currently working with a SQL Server 2008 R2 Server and a database which uses a lot of tables that are a mixed bag of ANSI_PADDED Settings. 90% of the tables have Columns with Ansi Padding on AND ...
4
votes
1answer
153 views
According to the SQL92 spec, can I store the value 1 in a field where the precision = scale?
Is it SQL92 compliant to store the number 1 in a field that is defined as NUMERIC 3,3?
What about DECIMAL 3,3?
Does this mean that the precision is a floating decimal place or static that must ...
4
votes
1answer
64 views
When is the AS keyword required?
In the following statement (PostgreSQL 11):
=> SELECT c cost FROM tt;
ERROR: syntax error at or near "cost"
LINE 1: SELECT c cost FROM tt;
I get an error. Adding parenthesis around the field ...
4
votes
1answer
81 views
How locks in Postgres behave when quitting the JVM abnormally
I have a table 'car', as follows
Table "public.car"
Column | Type | Collation | Nullable | Default
----------+--------+-----------+----------+---------
car_no | bigint | ...
4
votes
1answer
61 views
Standard Behaviour for mix of 'ON-DELETE-CASCADE' and 'ON-DELETE-RESTRICT' constraints in Mysql
In mysql 5.6, Consider these 2 examples creating relationships between A, B, C and D.
Example 1
CREATE TABLE `a` (
id INT UNSIGNED NOT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB;
CREATE TABLE ...
3
votes
1answer
578 views
Order of Execution of Set clause in Update Query (Postgres)
I recently came across this strange behaviour in Postgres. I had a table like the following one:
sasdb=# \d emp_manager_rel
Table "db3004db.emp_manager_rel"
Column | Type | ...
3
votes
1answer
988 views
Ansi SQL: Auto Numbered Column
Is there an ANSI standard for auto-numbered columns.
Currently, we have a choice of SERIAL, AUTOINCREMENT, AUTO_INCREMENT, IDENTITY() and good old NEXTVAL() among others.
I read somewhere that there ...
3
votes
1answer
2k views
Converting multiple SQL implicit joins into explicit joins
I am trying to convert several queries that use implicit joins into explicit joins, as per the standard ANSI/ISO Entry SQL-92 [1].
But after a lot of googling I could not find any examples or guides ...
3
votes
1answer
427 views
Tool to test RDBMS SQL dialect for ANSI SQL standard compliance
We are developing an interface to our internal data storage and manipulation tool that would allow people to use SQL dialect.
We decided that it would be perfect to make our SQL dialect as close to ...
2
votes
4answers
979 views
What computations can NOT be performed in standard SQL? [closed]
I know loops cannot be expressed in standard SQL. What other forms of processing can it not perform?
2
votes
1answer
287 views
What is the order when SELECT-ing unordered set from an ordered SELECT?
What does the SQL standard and its mainstream DB implementations (SQL Server, Oracle, DB2, PostgreSQL, Firebird, MySQL, etc.) say about the final order in the following general case:
SELECT * FROM (...
2
votes
1answer
1k views
What does “ISO standard equivalent” actually mean?
I'm reading about comparison operators. In the documentation, Comparison Operators Modified by ANY, SOME, or ALL it says (emphasis added):
Comparison operators that introduce a subquery can be ...
2
votes
2answers
6k views
MySQL case sensitivity for table names
Why is it that different platforms (linux vs windows) does not support the same naming conventions? I'm stunned by the fact that the documentation uses file names as an excuse for this.
Yes, windows ...
2
votes
2answers
1k views
Equality in JOIN or WHERE clause? Efficiency and style
Of the two queries below, which is the most efficient for postgresql?
And which has better style in terms of readability, etc.
The difference is the placement of the statement doctor.type != 'surgeon'...
2
votes
2answers
3k views
Databases: are there universal datatypes?
While designing database tables (using some tool like Enterprise Architect), how do I use only 'universal' datatypes that will work with all databases when I need to switch to a different database in ...
2
votes
1answer
957 views
Does MariaDB or MySQL implement the VALUES (expression) table value constructor?
I am just wondering if MariaDB or MySQL implements the <table value constructor> in the SQL Spec. In SQL Server and PostgreSQL this is done with standardized VALUES (expression)?
SELECT *
FROM (...
2
votes
1answer
658 views
How do I do a JOIN in ANSI SQL 89 syntax?
In Oracle, I use
SELECT * FROM table1...JOIN...
where the dots represent either the type of join or the condition on which to be joined.
Is this ANSI 89 syntax? If not, then how would I perform a ...
2
votes
1answer
505 views
SQL ISO Standard for group concatenation
Is there an operator in the latest standard SQL:2011 (or earlier) that will handle group concatenation the likes of which we currently have to resort to these tricks for?
GROUP_CONCAT in MySQL
FOR ...
