[This article](https://sqlserverfast.com/blog/hugo/2008/03/lets-deprecate-update-from/) fascinated me it claims that FROM clauses in UPDATE are not part of the ANSI standard. Unfortunately, it is very old and I don't care to buy a copy of the latest ANSI standard for SQL. Is it still the case that FROM clauses in UPDATE are not part of the ANSI standard?

A software vendor refuses to support MariaDB on the grounds that they need JSON support, but that "JSON is not inherently included in the basic SQL standard, but it is part of the SQL:2016 standard (ISO/IEC 9075:2016), which MariaDB does not support." (quote from their support). I need to point the vendor's non-technical management to a document that explicitly confirms MariaDB’s compliance with the JSON portion of SQL:2016 standard. I have searched both mariadb.org and mariadb.com and their git repos for the strings "ISO", "9075", "SQL:2016" but I wasn't able to find such a statement, only occasional references.

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 * -the_geom FROM segments; * I once heard that this functionality was deliberately excluded from the SQL standard because changing adding columns to the table will alter the query results. Is this true? Is the argument valid? * Is there a workaround, especially in PostgreSQL?

--- ### Please read carefully. This question is _not_ about workarounds or alternative ways to achieve LIMIT in IN subqueries! --- It’s a well-known and well-documented that there are [a number of restrictions on subqueries](https://dev.mysql.com/doc/mysql-reslimits-excerpt/8.0/en/subquery-restrictions.html) in MySQL (and MariaDB). Of the restrictions listed in the documentation, the reasoning behind most is either quite logical (e.g., you cannot modify and select from the same table in the same query) or explained on the page. The only restriction that _doesn’t_ provide a reasoning in the documentation is also the most common one to come up against, namely that you cannot LIMIT a subquery with certain operators, most commonly IN. This is invalid:

SELECT * FROM a WHERE b_id IN (SELECT id FROM b ORDER BY c LIMIT 5)

There are any number of questions strewn across the Internet (including on this site) looking for alternative ways to achieve the same effect, but that’s not what I’m asking for here – I know you can just wrap it in another subquery and it works. What I’m puzzled by is the fact that I cannot find any explanation as to **why** this restriction exists to begin with. I’m guessing it’s not part of the SQL standard, since limiting subqueries in this way is perfectly valid in other engines, like SQL Server and (as far as I can tell) PostgreSQL. So why did the developers of MySQL (and MariaDB) decide that using LIMIT in subqueries with operators like IN should be disallowed? Has anyone from the developer team ever mentioned or explained the choice?

wondering if the ANSI SQL standard has a portable way to create a sequence object and get values from it? I can't find a portable way, but search engines seem to confuse ANSI sql with MS SQL server, so maybe I'm just missing it...

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 [****] ON PRIMARY ( NAME = N'TMS', FILENAME = N'D:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\****.mdf' , SIZE = 11264KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'TMS_log', FILENAME = N'L:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\****.ldf' , SIZE = 4672KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) GO ALTER DATABASE [****] SET COMPATIBILITY_LEVEL = 100 GO IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) begin EXEC [****].[dbo].[sp_fulltext_database] @action = 'enable' end GO ALTER DATABASE [****] SET ANSI_NULL_DEFAULT OFF GO ALTER DATABASE [****] SET ANSI_NULLS OFF GO ALTER DATABASE [****] SET ANSI_PADDING OFF GO ALTER DATABASE [****] SET ANSI_WARNINGS OFF GO ALTER DATABASE [****] SET ARITHABORT OFF GO ALTER DATABASE [****] SET AUTO_CLOSE OFF GO ALTER DATABASE [****] SET AUTO_CREATE_STATISTICS ON GO ALTER DATABASE [****] SET AUTO_SHRINK OFF GO ALTER DATABASE [****] SET AUTO_UPDATE_STATISTICS ON GO ALTER DATABASE [****] SET CURSOR_CLOSE_ON_COMMIT OFF GO ALTER DATABASE [****] SET CURSOR_DEFAULT GLOBAL GO ALTER DATABASE [****] SET CONCAT_NULL_YIELDS_NULL OFF GO ALTER DATABASE [****] SET NUMERIC_ROUNDABORT OFF GO ALTER DATABASE [****] SET QUOTED_IDENTIFIER OFF GO ALTER DATABASE [****] SET RECURSIVE_TRIGGERS OFF GO ALTER DATABASE [****] SET DISABLE_BROKER GO ALTER DATABASE [****] SET AUTO_UPDATE_STATISTICS_ASYNC OFF GO ALTER DATABASE [****] SET DATE_CORRELATION_OPTIMIZATION OFF GO ALTER DATABASE [****] SET TRUSTWORTHY OFF GO ALTER DATABASE [****] SET ALLOW_SNAPSHOT_ISOLATION OFF GO ALTER DATABASE [****] SET PARAMETERIZATION SIMPLE GO ALTER DATABASE [****] SET READ_COMMITTED_SNAPSHOT OFF GO ALTER DATABASE [****] SET HONOR_BROKER_PRIORITY OFF GO ALTER DATABASE [****] SET READ_WRITE GO ALTER DATABASE [****] SET RECOVERY FULL GO ALTER DATABASE [****] SET MULTI_USER GO ALTER DATABASE [****] SET PAGE_VERIFY CHECKSUM GO ALTER DATABASE [****] SET DB_CHAINING OFF GO USE [****] GO /****** Object: User [AD-ITSERVICES\TMSDB.user] Script Date: 03/25/2015 12:17:09 ******/ CREATE USER [AD-ITSERVICES\test.user] FOR LOGIN [****\test.user] WITH DEFAULT_SCHEMA=[dbo] GO /****** Object: Table [dbo].[TechnologyTypes] Script Date: 03/25/2015 12:17:12 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[TechnologyTypes]( [AssetTypeID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](50) NOT NULL, [IncludedInSearch] [bit] NULL, CONSTRAINT [PK_AssetType] PRIMARY KEY CLUSTERED ( [AssetTypeID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_AssetType] UNIQUE NONCLUSTERED ( [Name] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[TechnologyStatus] Script Date: 03/25/2015 12:17:12 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON now i am confused on why i go this sentence :- ALTER DATABASE [****] SET ANSI_NULL_DEFAULT OFF GO ALTER DATABASE [****] SET ANSI_NULLS OFF but when creating any sql table i will get :- SET ANSI_NULLS ON so what is the final result; is ANSI_NULLS will be equal to off or on ? from my understanding the "SET ANSI_NULLS" will be OFF at the database level, unless i set it ON at the table level ? is this correct ? any why i got the ANSI_NULLs set to off at the database level ?

Backstory: I'm having to do some _computer archeology_ for a data integration project, involving getting data out a multiple-decades-old old Progress, now _OpenEdge_, database application for AS/400. While reading through their documentation for their implementation of SQL-92 from 30 years ago, I saw they supported the exact same kind of RDMBS constraints that we still use today: PRIMARY KEY, FOREIGN KEY, UNIQUE, NULL/NOT NULL and CHECK. As of 2024, this is the same set of constraints that MySQL , MSSQL , Oracle , and Db2 support. Postgres is the only exception, as it supports all of the above _and_ EXCLUDE constraints - but nothing else. I coughed-up $200 for a legit copy of the current (2023) ISO/IEC 9075-2 specification confirmed that no other kinds of constraints are defined. ...and I find this surprising: I'd have thought that over the past 30 years we'd have other kinds of constraints by now because having to compromise a normalized DB design is still a major pain-point when designing a DB today, and it's easy to come up with ideas for other kinds of constraints - for example, I would really benefit from an "EXISTS" constraint which would be just like a FOREIGN KEY constraint today, but lets you reference a non-unique column in the target table - or a GLOBALLY UNIQUE constraint to ensure that GUID values _really are_ globally unique over all tables. -------- So far I'm only aware of Postgres' EXCLUDE constraints. What other kinds of constraints exist in other, lesser-known, RDBMS? Have their vendors attempted to have them standardised?

I'm just wondering if either the spec of the implementation (PostgreSQL) provides for generating identity columns from UUIDs or the like. Is there an alternative to, CREATE TABLE f ( id uuid DEFAULT gen_random_uuid() ); Especially one that can protect the column under GENERATED ALWAYS

Where can I find a legal copy of the ISO SQL 2008 standard?

I am just wondering if MariaDB or MySQL implements the ` in the SQL Spec. In [SQL Server](https://learn.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql?view=sql-server-2017) and [PostgreSQL this is done with standardized VALUES (expression)`](https://www.postgresql.org/docs/current/static/sql-values.html) ? SELECT * FROM ( VALUES (1) ) AS t(x); x --- 1 (1 row) (syntax from [tag:postgresql]).

What does ISO/IEC 9075-1:2023 standard say about COMMIT/ROLLBACK queries? Should (or can) they emit an error/exception when executed outside an active transaction? I am asking because of https://jira.mariadb.org/browse/MDEV-32652 and because the standard is not public/free - https://stackoverflow.com/questions/20121248/where-can-i-get-read-sql-standard-sql2011 . Here is how the main DB vendors currently behave: SQLite - https://dbfiddle.uk/BmwZpE6v - emits an error MSSQL - https://dbfiddle.uk/IK9y_GEK - emits an error PostgreSQL - https://dbfiddle.uk/tC9pxsJK - no error Oracle - https://stackoverflow.com/questions/53410508#53412359 - have no "start transaction" MySQL - https://dbfiddle.uk/UCxKq6nU - no error

I would *assume* this to be the case, since it seems like an easy optimization and to make things more predictable. Given:

SELECT ANY_VALUE(country) AS country
FROM countries
HAVING ANY_VALUE(country) = 'US'

Is there a database where

country
NZ
US

Could return

country
NZ

Because the two ANY_VALUE clauses are executed independently?

The Postgres docs state: > While the standard syntax for specifying string constants is usually convenient, it can be difficult to understand when the desired string contains many single quotes or backslashes, since each of those must be doubled. I understand how single quotes are doubled, since that is how they are escaped according to [section 4.1.2.1](https://www.postgresql.org/docs/15/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS) on string constants. But if backslashes were doubled, then surely this wouldn't be possible:

janus=> \d todo
                            Table "public.todo"
 Column |  Type   | Collation | Nullable |             Default              
--------+---------+-----------+----------+----------------------------------
 id     | integer |           | not null | nextval('todo_id_seq'::regclass)
 descr  | text    |           |          | 
 done   | boolean |           |          | 

janus=> truncate table todo;
TRUNCATE TABLE
janus=> insert into todo (descr,done) values ('\''', false);
INSERT 0 1
janus=> select length(descr), descr from todo;
 length | descr 
--------+-------
      2 | \'
(1 row)

As you can see, the backslash before the two single quotes (that are interpreted as one single quote), has no effect, and it is inserted literally. So how is the "standard" syntax referenced in the quote interacting with backslashes? There does not seem to be any special treatment of them, they are like any other character.

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 ODBC driver and Microsoft Jet engine - not sure if this is exactly the same thing, shouldn't it also relate to T-SQL?

I am looking at list of SQL keywords here https://www.postgresql.org/docs/current/sql-keywords-appendix.html There are some single letter keywords there. I cannot find any information about them. Where can I find the information and what are these for? Examples, A, C, F, G, K, M, P, T.

Section 5.6 of the SQL-92 standard contains rules 10...13 per which unquoted identifiers should be upper-cased, so foo becomes FOO but "foo" remains foo. These rules are respected by Oracle , IBM DB2, Snowflake , and ksqlDB but not by Postgres , MySQL or SQLite, for example. The question is, why? In my understanding, the optional quoting of identifiers in a language with a lot of keywords makes sense. Consistent case sensitivity or insensitivity of identifiers would also make sense. But making it dependent on the identifier being quoted doesn't look rational. What am I missing?

In PostgreSQL, strings can be quoted very flexibly using a pair of dollar signs ($) with a tag in between. For example, SELECT $f$ Hi $f$; *Is this quoting mechanism a part of any SQL standard, or is it postgres specific?*

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 keyword? lists the various ways to handle this behavior in different languages: DB2 -- select * from table fetch first 10 rows only Informix -- select first 10 * from table Microsoft SQL Server and Access -- select top 10 * from table MySQL and PostgreSQL -- select * from table limit 10 Oracle -- select * from (select * from table) where rownum <= 10 I don't play in DBs that often so I'm speaking from ignorance here, but it seems like this is a pretty important functionality - at least important enough that I scratch my head when I see it has been left up to vendors.

I just found out that some specific patterns when used with LIKE text operator uses BTREE indexes to speed up queries, such as: - 'abc123' (text with no wildcard) - 'abc%' (text with only one wildcard in the end) What is the equivalent of LIKE text operator using a pattern with only one wildcard '%' present in the pattern's end (such as "a_column" LIKE 'abc%') using just greater/less/equal than operators? My interest is achieving this in PostgreSQL, but this may be an SQL standard question.

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' Inside the WHERE clause: SELECT practice.name, doctor.name FROM doctor JOIN practice ON (doctor_code = code) WHERE doctor.name LIKE '%son' AND (doctor.type != 'surgeon'); or inside the JOIN clause: SELECT practice.name, doctor.name FROM doctor JOIN practice ON (doctor_code = code AND doctor.type != 'surgeon') WHERE doctor.name LIKE '%son';