sqlingres

SQL Subquery > 1 meaning

I've been looking over some old Ingress SQL code and I'm slightly curious as to the ' > 1' at the end of a subquery, such as :

SELECT count(*)
FROM table1 t
WHERE t.col1 = 'TEST' 
AND t.col2 = 'TEST123' 
AND
(SELECT count(*)
FROM table2 ta
JOIN table3 tb ON tb.id = ta.id
WHERE t.col1 = ta.col1
AND tb.col3 IS NULL) > 1 ;

I'm confused as to the function of the greater than 1 - does that mean only to include in the AND statement if the return value of the subquery is greater than one ? Or am I totally wrong ? Googled but don't find many results for Ingress!

Cheers!

Solution

  • You are correct. This is a scalar subquery. Such a subquery returns one column and at most one row. The value is treated as a scalar value in the query.

    In this case, it is an aggregation query counting matching rows. So, this logic requires at least two matches.