Skip to main content
The 2024 Developer Survey results are live! See the results
42 votes
Accepted

Using a CREATE TABLE AS SELECT how do I specify a WITH condition (CTE)?

It may be awkward, but you have to move the WITH clause from the top into the query. It's a part of the statement to generate the table, and that statement comes after the CREATE TABLE, so you would ...
Evan Carroll's user avatar
  • 63.9k
31 votes

SQL: SELECT All columns except some

It actually is somewhat possible with PostgreSQL starting with 9.4 where JSONB was introduced. I was pondering about similar question on how to show all available attributes in Google Map (via GeoJSON)...
mlt's user avatar
  • 431
21 votes

Where can I find the first standardization of SQL, SQL-86?

HTML SQL-86 Google Groups I found some references about an HTML version of SQL-86. The new URL for the HTML translation of SQL-86 is: http://speckle.ncsl.nist.gov/~sql/ The reason for ...
McNets's user avatar
  • 23.8k
18 votes

Are there standard SQL functions with side effects?

You have a few different questions in here. Q: What are ANSI standard SQL functions? ANSI standard functions are things like AVG, COUNT, MIN, MAX. They're covered in the 1992 ANSI standard, but that'...
Brent Ozar's user avatar
17 votes
Accepted

Where can I find the first standardization of SQL, SQL-86?

The Standard The standard is available as FIPS 127 and hosted on the Internet Archive (archive.org) This link was given to me by a member of the Houston Linux Users Group.
Evan Carroll's user avatar
  • 63.9k
16 votes
Accepted

In PostgreSQL, what is the difference between a "Stored Procedure" and other types of functions?

Since Postgres functions (CREATE FUNCTION) only run in a (single) transaction context, several important commands cannot be executed inside a function body. Like CREATE DATABASE or CREATE INDEX ...
Erwin Brandstetter's user avatar
16 votes

Best practices to maximize portability in SQL Server 2016

User Aaron Bertrand made some comments that align well with my thoughts on your question. This is more of a frame challenge than an answer to your specific question, but I think it's valuable to ...
Josh Darnell's user avatar
  • 29.4k
15 votes

Is the keyword "ALIAS" actually used?

PostgreSQL maintains a list of reserved and non-reserved terms in the appendix. ALIAS is absent from that list. You can verify PostgreSQL does not use ALIAS by checking out the YACC grammar. Even as ...
Evan Carroll's user avatar
  • 63.9k
14 votes

Where can I find the first standardization of SQL, SQL-86?

I reached out to them Hello Evan, Thanks for reaching out. Unfortunately, we do not sell or offer the 1986 version of the SQL standard (ANSI X3.135-1986) for historical reasons. In general, we offer ...
Evan Carroll's user avatar
  • 63.9k
14 votes

What does `<>` mean?

WHERE posn_id <> rid Will return all rows where both posn_id and rid are not NULL and where they are different. It's the ANSI SQL-Compliant not equals operator in a simple comparison predicate ...
BradC's user avatar
  • 10k
13 votes
Accepted

Does MariaDB or MySQL implement the VALUES (expression) table value constructor?

MariaDB 10.3+ Yes, speaking of MariaDB 10.3.3 and later it seems to support table value constructors. SELECT 1, t.7 FROM (VALUES (7),(42)) AS t; 1 | 7 -------- 1 | 7 1 | 42 You'll notice that ...
Vladislav Vaintroub's user avatar
13 votes
Accepted

Why does information_schema have "YES" and "NO" character strings rather than booleans?

INFORMATION_SCHEMA Using psql you can see the schema with \d information_schema.columns, or you can look it up in the docs. View "information_schema.columns" ...
Evan Carroll's user avatar
  • 63.9k
13 votes
Accepted

Is Microsoft SQL Server 2016 fully ANSI SQL-92 compliant?

I don't believe that any version of any database platform on earth is fully compliant with any version of the standard. This is probably why you're not finding any claims of 100% compliance... You ...
12 votes

How SELECT column does not list in GROUP BY clause

It is not clear to me what you are asking but I believe that GROUP BY is one of the most misunderstood concepts in SQL, so I'll add this answer anyhow. It may or may not help with the understanding of ...
Lennart - Slava Ukraini's user avatar
12 votes

Does the spec permit column-count mismatch on INSERT?

Draft documents can be found at: http://www.wiscorp.com/sql20nn.zip The part that is of interest for this question is: 7IWD2-02-Foundation-2011-12.pdf At first I could not find any support for ...
Lennart - Slava Ukraini's user avatar
12 votes

Best practices to maximize portability in SQL Server 2016

Do not enforce STD SQL. Decide first which DBMS you will use according to the needs of your project, and take advantage of it.
McNets's user avatar
  • 23.8k
11 votes
Accepted

Does the SQL Spec require a GROUP BY in EXISTS ()

I found it in the SQL 2011 spec... If the <select list> “*” is simply contained in a <table subquery> that is immediately contained in an <exists predicate>, then the <select ...
Evan Carroll's user avatar
  • 63.9k
11 votes
Accepted

How SELECT column does not list in GROUP BY clause

You can not select aggregates across a field if you don't include the field in the group by list. If you want the totals per year you should write SELECT year,sum(mark),sum(maxmark) FROM table1 ...
Tom V's user avatar
  • 15.7k
11 votes
Accepted

Is there a function which the opposite of NULLIF?

NULLIF(expr1, NULLIF(expr1, expr2)) does what you want.
Balazs Papp's user avatar
  • 40.8k
11 votes

Is MySQL breaking the standard by allowing selecting columns that are not part of the group by clause?

Standard SQL would reject your query because you can not SELECT non-aggregate fields that are not part of the GROUP BY clause in an aggregate query This is correct, up to 1992. But it is plainly ...
10 votes
Accepted

Difference between "<> ANY(…)" and "NOT = ANY(…)"?

NOT sid = ANY (SELECT ...) is equivalent to: sid <> ALL (SELECT ...) So, your two conditions are not the same. ALL and ANY operators are hard for me (and many people) to use. I think that'...
ypercubeᵀᴹ's user avatar
10 votes
Accepted

Does SQL Server support custom DOMAINs?

SQLCLR presents a surface area for creating fully custom data types. In fact, that is how SQL Server supports geometric and hierarchy data types. Since SQLCLR is based on the Microsoft.Net common-...
Hannah Vernon's user avatar
  • 70.2k
10 votes
Accepted

What actually is a SQL clause?

In general terms, a clause is just a logical chunk of a SQL statement - and, usually, is a chunk that is (theoretically) optional. I'm a SQL Server guy, so I'll be thinking in those terms. SELECT ...
RDFozz's user avatar
  • 11.6k
10 votes

Best practices to maximize portability in SQL Server 2016

Not really. There is SET FIPS_FLAGGER 'FULL'. This prints out a warning for non standard SQL - but some caveats are I am unsure what specific standard this uses (and suspect it may be SQL 92) From ...
Martin Smith's user avatar
  • 85.4k
10 votes
Accepted

Why does FROM multiple tables default to cartesian product?

The syntax of comma-style joins (SQL-89) versus using the JOIN keyword (SQL-92) is not the point. You should use the more modern syntax, but it doesn't address the question of why the default would be ...
Bill Karwin's user avatar
  • 15.2k
9 votes
Accepted

What does "ISO standard equivalent" actually mean?

It just means that operator SOME is not some MS proprietary extension. It is part of the (ISO/ANSI) SQL standard.
ypercubeᵀᴹ's user avatar
9 votes
Accepted

SQL Text data type deprecated?

In the ANSI SQL Standard, there is no TEXT type. There are various string types defined, like CHAR and VARCHAR and many more but no mention of a TEXT type. There is a CHARACTER LARGE OBJECT and a ...
ypercubeᵀᴹ's user avatar
9 votes
Accepted

What is the order when SELECT-ing unordered set from an ordered SELECT?

Can I trust that the inner order is respected by the outer projection as well ...? No, you cannot. ... or do I need to state the ORDER BY in the outer statement explicitly, to be sure? Yes, you ...
ypercubeᵀᴹ's user avatar
9 votes
Accepted

How do I do a JOIN in ANSI SQL 89 syntax?

You don't want to use SQL-89 syntax. SQL-89 is just an implicit CROSS JOIN (Cartesian product). SELECT * -- NEVER FROM foo, bar; -- NEVER EVER DO THIS Is the same as the more modern ...
Evan Carroll's user avatar
  • 63.9k

Only top scored, non community-wiki answers of a minimum length are eligible