Tool to find out why an SQL select does not return any data.

Question

Is there a tool which tells you (or gives you a hint) why a particular select statement dose not return any rows given the current data in your database.

eg if you had the following 4 table join

select * 
from a, b, c, d
where a.b_id = b.id
and b.c_id = c.id
and c.d_id = d.id

If there were rows which satisfied the conditions a.b_id = b.id also rows which satisfied b.c_id = c.id but no rows which satisfied the condition c.d_id = d.id it would highlight c.d_id = d.id as the problem.

Ie it would brake up the where clause and find out which of the sub conditions returned true and highlight those which do not return true.

It would not work well for complex querys but many select statements are simple joins over lots of tables.

This would be useful when creating test data to exercise a peace of application code or debugging a problem with a live system.

Graphical explain tools (that show the plan of the actual exiection path) come close but they show too much info and do not highlight the missing link in the select stament.

I am using postgres, sqllight and mysql but would be interested in how tools for other databases/platforms work.

Im also interested in any manula techniques.

Does anybody else have this problem?

Would anybody be interested if I wrote such a tool?

Answer 1

I never have this problem, but I also use explicit joins so it's usually as simple as running parts of the query until I find out which one is restricting my results incorrectly.

In your case

SELECT *
FROM a -- First run just to here, are there records?
INNER JOIN b
    ON a.b_id = b.id -- Then run just to here, is it OK?
INNER JOIN c
    ON b.c_id = c.id -- Then run just to here, is it OK?
INNER JOIN d
    ON c.d_id = d.id -- Then run just to here, is it OK?

Answer 2

This will get you started...

select count(1) from a, b where a.b_id = b.id

select count(1) from b, c where b.c_id = c.id

select count(1) from c, d where c.d_id = d.id

Note that you are using AND so the overlap of the above queries may not be what you expect.

OR

Using MS-SQL Server Management Studio... Display the "Execution Plan" and mouse over the nodes for "Actual Number of Rows".

Answer 3

There isn't a tool to do this per se, because as other posters have pointed out, it's simple enough to just highlight successively larger portions of the query (if you have it formatted well, with one ANDed clause per line) and see how many records are returned. Making the joins explicit is critical here, because most likely, THAT is where you're losing rows, not with your WHERE clauses.

I can see it being an interesting mini-tool, though, to take a SQL query with n predicates as input and return a graph of the count result after appending each separate AND clause to the trunk of the statement ... something like:

part           count
-------------- -------------
TRUNK          100
AND clause 1   90
AND clause 2   85
...            ...
AND clause n   20

SQL Management Studio might offer ways to plug a little tool like this in as a macro, it might be worth exploring. Post it here if you do! :)

Answer 4

You should be able to study the execution plan (if you are using SQL Server, otherwise a similar feature in your database) to see where the joins produce an empty set.

Answer 5

I would normally use the approach described by Cade Roux

But theoretically you could use the following query:

SELECT a.id, b.id, c.id, d.id
FROM a 
LEFT JOIN b
    ON a.b_id = b.id
LEFT JOIN c
    ON b.c_id = c.id
LEFT JOIN d
    ON c.d_id = d.id

The rows that return all NULL values would point you to the table without matching data. E.g. if all c.id were null that would mean that there was no matching records in c table.

Of course all other tables that depend on the records in table c would have no matching records as well.

So the first column with all nulls will be the one to start with.

Answer 6

I do this so often that I started looking for a tool to parse the query, and I wound up here. It wouldn't be too hard to convert any given SELECT statement into the form suggested by kristof, or to produce output as suggested by Ian Varley. Please post your work of art here if you get around to it!