To help understand why a complex query with many joins comes back empty, I like to provide helper debug functions that builds the query step by step. Each block checks one prerequisite and tells the user what’s missing:

```sql FUNCTION debug_user(user_id):

  IF NOT (SELECT FROM user WHERE user.user_id = user_id) THEN
    -- user doesn’t exist
  END IF;

  IF NOT (SELECT FROM user
           JOIN user_addr ON user_addr.user_id = user.user_id
           WHERE user.user_id = user_id) THEN
    -- user has no address
  END IF;

  -- keep adding joins until you find the break

```

because when you have a query that involves 6 relations, and you don't get results, it is nice to know why.

▲ FrancoisBosun 4 days ago | parent | next [-]

I do the "same thing", but using PostgreSQL `EXPLAIN ANALYZE`. EXPLAIN ANALYZE has information about the number of rows returned, which means I know exactly which node in the query plan failed to return expected values.

	▲	ericHosick 4 days ago \| parent \| next [-]
		don't mean to push the goalpost (didn't mention the following in the above post) these functions can also be exposed in admin ui's making it easier for the general admin users/support users. another very useful use case is when row level security (RLS) is enabled.
	▲	polishdude20 4 days ago \| parent \| prev [-]
		Not for function though unless you specify it as a debug flag inside the function?

▲ iTokio 4 days ago | parent | prev [-]

You can can also run a detailed explain plan and with a suitable visualizer it’s trivial to see where the query fell apart. You get to see the actual data distribution this way.