The tag has no wiki summary.

learn more… | top users | synonyms

1
vote
2answers
56 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 != ...
0
votes
0answers
50 views

Emulate regex matching in ANSI SQL

I'm working in an ANSI SQL environment (no regex) on a script that is supposed to take the first 8-10 consecutive digits out of a text field that could: have alphanumeric characters, and some other ...
0
votes
1answer
30 views

Drastic change in query speed when changing from Oracle 9i to ANSI outer join syntax

I am working on simplifying a query originally written for Oracle 9i. In the process I changed the join syntax from the Oracle 9i syntax to ANSI. The names of the tables and columns have been changed ...
1
vote
1answer
69 views

What relevance does the table-level ANSI_NULL setting have?

Apparently, SQL Server stores the current ANSI_NULL setting when creating a table (see related questions here and here). However, when I execute SELECT * FROM myVeryOldTable WHERE ...
0
votes
1answer
194 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 ...
0
votes
1answer
27 views

Does SQL/MED (ISO/IEC 9075-9) defines a writable foreign data wrapper interface

I just can't find anything about it. I don't have the paper or draft of the SQL:2011, and have almost only drafts from SQL:2008, 2006, 2003. Does anyone know if "SQL/MED (ISO/IEC 9075-9)" defines a ...
4
votes
2answers
1k views

Converting Tables to Ansi Padding On

I am currently working with a MSSQL 2008R2 Server and a database which uses a lot of tables that are a mixed bag of ANSI_PADDED Settings. 90% of the tables habe Columns with Ansi Padding on AND ...
0
votes
1answer
536 views

Get max date for each year from list of dates

Need to get max date for each year from list of arbitrary dates: date year ================= 1/10/2013 2013 3/26/2013 2013 4/20/2012 2012 9/10/2012 2012 5/6/2011 2011 So ...
5
votes
1answer
9k 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 ...
1
vote
2answers
2k 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 ...
13
votes
3answers
352 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? ...
4
votes
1answer
730 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 ...
2
votes
1answer
213 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 ...
7
votes
1answer
546 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 ...
17
votes
3answers
3k 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(...) = ...