Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

102
votes
11answers
85k 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
1answer
63k 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(...) = ...
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'...
22
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 ...
19
votes
3answers
592 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
449 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
991 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
631 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
75 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 ...
5
votes
2answers
1k 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
458 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
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 ...
5
votes
1answer
390 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? ...
4
votes
3answers
463 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
2k 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
306 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
185 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
102 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
57 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
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 ...
4
votes
1answer
77 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
55 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
490 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
883 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
380 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
949 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
234 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
2k 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
712 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
519 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
491 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 ...