When working on complex SQL queries, whether they were written by myself or I am debugging someone else’s code, I often need to see if the joins are behaving as expected. To get counts of the query, I can let it run and check the row count in the results window. But if there are a lot of rows, the results could take a long time to be written.
In these cases, I put a wrapper around the query. Simply surround the query with another Select count(*) FROM parenthesis
SELECT COUNT(*) FROM ( Select …. )
Note that you need to alias the query inside the parenthesis.
I often will go the next step and do a GROUP BY, HAVING query to get counts of certain columns that are duplicates on columns that I expect to be unique. In the following query snippet that I modified from a real situation that needed to be debugged,
This GROUP BY query indicated there was one duplicate record. Since I could provide the values for table t1.col2 and t1.col3, further investigation proved that there was a data issue that needed to be corrected.
I put a “wrapper” around complex queries often. If you give it a try you might find many scenarios that can help you diagnose issues