Efficient SQL Test Query or Validation Query That Will Work Across All (Or Most) Databases

Efficient SQL test query or validation query that will work across all (or most) databases

The jOOQ manual's section about the DUAL table lists the following for jOOQ's select(inline(1)) query:

-- Access
SELECT 1 FROM (SELECT count(*) dual FROM MSysResources) AS dual

-- BigQuery, CockroachDB, Exasol, H2, Ignite, MariaDB, MySQL, PostgreSQL, 
-- Redshift, Snowflake, SQLite, SQL Server, Sybase ASE, Vertica
SELECT 1

-- MemSQL, Oracle
SELECT 1 FROM DUAL

-- CUBRID
SELECT 1 FROM db_root

-- Db2
SELECT 1 FROM SYSIBM.DUAL

-- Derby
SELECT 1 FROM SYSIBM.SYSDUMMY1

-- Firebird
SELECT 1 FROM RDB$DATABASE

-- HANA, Sybase SQL Anywhere
SELECT 1 FROM SYS.DUMMY

-- HSQLDB
SELECT 1 FROM (VALUES(1)) AS dual(dual)

-- Informix
SELECT 1 FROM (SELECT 1 AS dual FROM systables WHERE (tabid = 1)) AS dual

-- Ingres, Teradata
SELECT 1 FROM (SELECT 1 AS "dual") AS "dual"

HikariCP Acceptable Test Query for Sybase 16

In Sybase ASE select can be without where or from clause

A simple select statement contains only the select clause; the from clause is almost always included, but is necessary only in select statements that retrieve data from tables. All other clauses, including the where clause, are optional.

So you can just use Select 1 removing the double quotes as in example

connection-test-query: SELECT 1

DBCP - validationQuery for different Databases

There is not only one validationQuery for all databases. On each database you have to use different validationQuery.

After few hours of googling and testing I have collected this table:

Database validationQuery notes

• hsqldb - select 1 from INFORMATION_SCHEMA.SYSTEM_USERS
• Oracle - select 1 from dual
• DB2 - select 1 from sysibm.sysdummy1
• mysql - select 1
• microsoft SQL Server - select 1 (tested on SQL-Server 9.0, 10.5 [2008])
• postgresql - select 1
• ingres - select 1
• derby - values 1
• H2 - select 1
• Firebird - select 1 from rdb$database
• MariaDb - select 1
• Informix - select 1 from systables
• Hive - select 1
• Impala - select 1

I wrote about it on my blog - validation query for various databases.

In advance there is an example of class, which return validationQuery according to JDBC driver.

Or does anybody have better solution?

SQL query that will return rows that don't have a corresponding status, across ANY rows that have the same id?

You can use aggregation:

SELECT order_id
FROM my_table
GROUP BY order_id
HAVING SUM(CASE WHEN status = 'COMPLETE' THEN 1 ELSE 0 END) = 0;

This counts the number of "COMPLETE" statuses for each order_id. The = 0 says that there are none.

SQL query that will show all the codes where the average number of transfer among users in that code is less than two

First left join exchange to user and use aggregation to get the number of matches per user. Then aggegate again to get the average number of matches per country.

SELECT x.country
       FROM (SELECT u.uid,
                    u.country,
                    count(e.eid) matches
                    FROM user u
                         LEFT JOIN exchange e
                                   ON e.match_id = u.match_id
                    GROUP BY u.uid,
                             u.country) x
       GROUP BY x.country
       HAVING avg(x.matches) < 2;

---

Related Topics