Is There a Command to Test an SQL Query Without Executing It ( MySQL or Ansi SQL )

Is there a command to test an SQL query without executing it? ( MySQL or ANSI SQL )

I realise this is a bit of an old question but for completeness...

If the intention is to find the query processing time without returning any rows (I need this quite often, I want to know how long a piece of code I am using will take without having it return a couple of million rows I am not interested in seeing) then the BLACKHOLE engine can be very useful:

https://dev.mysql.com/doc/refman/8.0/en/blackhole-storage-engine.html

For instance say I have 2 tables, t1 & t2, with millions of rows, that I am joining together. I want to check how long this is likely to take, in a GUI (SQLYog or mysql workbench or somesuch) without returning millions of rows that will eat up memory and presumably take time for the GUI to process and display. I use the blackhole engine to 'dump' the rows to nowhere.
EG:

CREATE TABLE tBH (a TINYINT) ENGINE = BLACKHOLE;
SELECT NOW(); -- Show start time
INSERT tBH 
SELECT 1 FROM t1
LEFT JOIN t2 ON t1.key1 = t2.key1;
SELECT NOW(); -- Show end time

Note that as I am just looking for execution time I do not bother returning all the columns (IE with "*") but just a placeholder ("1" in this case).

How to test your query first before running it sql server

First assume you will make a mistake when updating a db so never do it unless you know how to recover, if you don't don't run the code until you do,

The most important idea is it is a dev database expect it to be messed up - so make sure you have a quick way to reload it.

The do a select first is always a good idea to see which rows are affected.

However for a quicker way back to a good state of the database which I would do anyway is

For a simple update etc

Use transactions

Do a begin transaction and then do all the updates etc and then select to check the data

The database will not be affected as far as others can see until you do a last commit which you only do when you are sure all is correct or a rollback to get to the state that was at the beginning

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"

How to check if MySQL query is valid without executing it?

Not without knowledge of the schema (for example, is 'x' a table?) and writing a SQL parser. Your MySQL query tool should be able to do that kind of validation (intellisense if you like) but I know from first hand experience, most of the (free) MySQL tools are abysmal.

'Preparing' the query would do what you want, but is a runtime check, not a compile time check - you seem to be looking for a compile time/offline solution.

Difference between these two SQL queries

Both queries get you the same data.

The second query is the straight-forward way to the problem; get all persons that have no entry in person_log. You can do the same with a NOT EXISTS clause instead of a NOT IN clause. (NOT IN is a bit leaner, but the values you select in the subquery must not be null, for otherwise you see no data at all. I usually perfer IN / NOT IN over EXISTS / NOT EXISTS for their simplicity, but that's a matter of personal preference.

The first query is called an anti join. It is a trick to achieve the same as a NOT EXISTS or NOT IN query on weak database systems that don't implement these methods well. (The reason is that when a new database system is written, the programmers usually put all their effort in joins for they are so important and neglect EXISTS and IN for some time.)

It depends on the DBMS which gets executed fastest, NOT IN, NOT EXISTS or the anti join. The ideal DBMS would get to the same execution plan, no matter which syntax you choose.

The anti join can produce large intermediate results. With a mature DBMS you shouldn't use anti joins for this reason and for mere readability.

How can I check the SQL syntax in a .sql file?

You could paste it into a query browser like the MySQL Query Browser (part of the GUI Tools package) and visually inspect how the keywords and string literals are colored to more easily see if you've made any syntax errors.

http://dev.mysql.com/downloads/gui-tools/5.0.html

ANSI Sql query to force return 0 records

What about adding your own SELECT around the user's SELECT?

SELECT * FROM (
select
  c.lastname,
  sum(cs.amount)
from customersales cs
join customers c on c.idcustomer=cs.idcustomer
/* where 1=0 */
group by c.idcustomer, c.lastname
) x
WHERE 0=1

EDIT: ORDER BY would not work with that solution, but since you get no rows, you could try to remove that from the query when necessary.

---

Related Topics