Stack Overflow
Stack Overflow

Questions tagged [ansi-sql]

ANSI SQL is the American National Standards Institute adopted Structured Query Language standard, developed by ISO/IEC JTC 1. It is the base for the different SQL dialects used by different DBMS vendors.

ANSI SQL is the American National Standards Institute adopted Structured Query Language standard, developed by ISO/IEC JTC 1. It is the base for the different SQL dialects used by different DBMS vendors.

SQL/PSM (SQL/Persistent Stored Modules) is the ISO standard defining an extension of SQL with a procedural language for use in stored procedures.

In Oracle PL/SQL (for stored procedures) and SQL (for queries) are two completely different languages (and are internally executed by different engines). DB2, PostgreSQL and Firebird also make clear distinction between the query language (called SQL) and the procedural language (for which every vendor uses a different name).

Microsoft SQL Server doesn't make this distinction: only T-SQL is used which covers the query language and the procedural language.

355 questions
113
votes
16 answers

Why isn't SQL ANSI-92 standard better adopted over ANSI-89?

At every company I have worked at, I have found that people are still writing their SQL queries in the ANSI-89 standard: select a.id, b.id, b.address_1 from person a, address b where a.id = b.id rather than the ANSI-92 standard: select a.id, b.id,…
Patrick Harrington
  • 47,416
  • 5
  • 23
  • 20
72
votes
6 answers

Why does MySQL allow "group by" queries WITHOUT aggregate functions?

Surprise -- this is a perfectly valid query in MySQL: select X, Y from someTable group by X If you tried this query in Oracle or SQL Server, you’d get the natural error message: Column 'Y' is invalid in the select list because it is not contained…
Aaron Fi
  • 10,116
  • 13
  • 66
  • 91
69
votes
6 answers

What does SQL Select symbol || mean?

What does || do in SQL? SELECT 'a' || ',' || 'b' AS letter
user3586553
66
votes
10 answers

How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?

How do you rewrite expressions containing the standard IS DISTINCT FROM and IS NOT DISTINCT FROM operators in the SQL implementation in Microsoft SQL Server 2008R2 that does not support them?
Jason Kresowaty
  • 16,105
  • 9
  • 57
  • 84
58
votes
8 answers

In MySQL, should I quote numbers or not?

For example - I create database and a table from cli and insert some data: CREATE DATABASE testdb CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'; USE testdb; CREATE TABLE test (id INT, str VARCHAR(100)) TYPE=innodb CHARACTER SET 'utf8' COLLATE…
Stann
  • 13,518
  • 19
  • 65
  • 73
55
votes
10 answers

ANSI SQL Manual

Can anyone recommend a good ANSI SQL reference manual? I don't necessary mean a tutorial but a proper reference document to lookup when you need either a basic or more in-depth explanation or example. Currently I am using W3Schools SQL Tutorial and…
Adrian
  • 6,013
  • 10
  • 47
  • 68
53
votes
1 answer

Difference between CURRENT_TIMESTAMP and GETDATE()

What is the difference between CURRENT_TIMESTAMP and GETDATE() in SQL Server? SELECT CURRENT_TIMESTAMP, GETDATE()
mehdi lotfi
  • 11,194
  • 18
  • 82
  • 128
48
votes
12 answers

Why does no database fully support ANSI or ISO SQL standards?

If I were designing a oil refinery, I wouldn't expect that materials from different vendors would not comply with published standards in subtle yet important ways. Pipework, valves and other components from one supplier would come with flanges and…
Lunatik
  • 3,838
  • 6
  • 37
  • 52
39
votes
7 answers

Is there an ANSI SQL alternative to the MYSQL LIMIT keyword?

Is there an ANSI SQL alternative to the MYSQL LIMIT keyword? The LIMIT keyword limits the number of rows returned by a SELECT e.g: SELECT * FROM People WHERE Age > 18 LIMIT 2; returns 2 rows. SELECT * FROM People WHERE Age > 18 LIMIT 10,…
Gary Willoughby
  • 50,926
  • 41
  • 133
  • 199
32
votes
2 answers

Standard SQL boolean operator IS vs. equals (=) operator

On the Wikipedia page for SQL there are some truth tables about boolean logic in SQL. [1] The Wikipedia page seems to source the SQL:2003 standard. The truth table for the equals operator (=) is different from the IS operator from the SQL:2003…
Janus Troelsen
  • 20,267
  • 14
  • 135
  • 196
31
votes
2 answers

List of differences between SQL databases

Most SQL databases follow the ANSI SQL standards to a degree, but The standard is ambiguous, leaving some areas open to interpretation (eg: how different operations with NULLs should be handled is ambiguous) Some vendors contradict the standard…
NullUserException
  • 83,810
  • 28
  • 209
  • 234
30
votes
3 answers

efficient way to compare two tables in bigquery

I am interested in comparing, whether two tables contain the same data. I could do it like this: #standardSQL SELECT key1, key2 FROM ( SELECT table1.key1, table1.key2, table1.column1 - table2.column1 as col1, table1.col2 -…
Nico Albers
  • 1,556
  • 1
  • 15
  • 32
27
votes
5 answers

Database Engines and ANSI SQL Compliance

I've been searching for half an hour and can't find any resources stating what level of the SQL ANSI standard is supported on various database engines. It looks like some level of support is provided by most engines, but I'd like to know exactly…
bitblit
  • 413
  • 1
  • 4
  • 7
22
votes
4 answers

SQL query with distinct and sum

I have the following medleys table that combines colors, fruits and ratings: [medleys] medley_id | color | fruit | rating ============================================== 1 red apple 25 2 blue …
user1768830
21
votes
2 answers

decimal(s,p) or number(s,p)?

recently, while working on a db2 -> oracle migration project, we came across this situation. the developers were inadvertently creating new table structures using decimal(s,p) columns. I didn't remember Oracle supporting this, but then some digging…
Raghav
  • 2,128
  • 5
  • 27
  • 46
1
2 3
…
23 24