CODEWITHSUNDEEP
mysqlsql
Viktor Joras
Viktor Joras

Reputation: 783

SQL SELECT clause column adding row?

I cannot belive what I am witnessing. I have to reconsider all my knowing and 10 years experince in SQL after having to do with this simple query:

SELECT CASE 
        WHEN SUM(COALESCE(lids_sids.counter, 0)) - SUM(COALESCE(lids_sids.leaved, 0)) > 0 
            THEN '1' 
            ELSE '2' 
        END as connected
FROM books
JOIN `lines` as l ON books.lid = l.id
LEFT JOIN lids_sids ON l.id = lids_sids.lid

I get 1 row even if books.lid does not contain lines.id. The result is '2'

If I do:

SELECT books.id
FROM books
JOIN `lines` as l ON books.lid = l.id
LEFT JOIN lids_sids ON l.id = lids_sids.lid

I get 0 rows as I was expecting also in the first query.

I was convinced that rows could be added and removed only by setting JOIN, WHERE, GROUP BY clauses. Now I learn that changing the content of SELECT clause can alter the rows number. Crazy ! I'm using mysql.

Table books.lid contains the values: 6, 207, 220, 285, 307, 326
while lines.id: 68, 69, 70, 71, 72, 73, 74
lids_sids.lid: 68

books.lid never matches lines.id and table lids_sids should never been considered.

Upvotes: 1

Views: 141

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

There are a couple of things going on. First, an aggregation query with no GROUP BY returns exactly one row. Always. Your query is an aggregation query because of the SUM() in the SELECT.

Your query (simplified a bit and using table aliases):

SELECT (CASE WHEN SUM(COALESCE(ls.counter, 0)) > SUM(COALESCE(ls.leaved, 0)) 
             THEN '1' 
             ELSE '2' 
        END) as connected
FROM books b JOIN
     lines l
     ON b.lid = l.id LEFT JOIN
     lids_sids ls
     ON l.id = ls.lid;

The row is going to contain either 1 or 2. Which will it contain? Well, the COALESCE() doesn't do very much. SUM() ignores NULL values (treats them as 0 anyway). But, if no rows match, you'll still get a NULL value. Hence, with no rows, "2" is returned because the condition is not true.

If you want 1 in this case, then move the COALESCE()*outside* theSUM()`:

SELECT (CASE WHEN COALESCE(SUM(ls.counter), 0) > COALESCE(SUM(ls.leaved), 0))
             THEN '1' 
             ELSE '2' 
        END) as connected
FROM books b JOIN
     lines l
     ON b.lid = l.id LEFT JOIN
     lids_sids ls
     ON l.id = ls.lid;

Upvotes: 0

Ron Deijkers
Ron Deijkers

Reputation: 3091

The difference is the aggregation function SUM. It will always return a single value / row.

if you would do something like:

SELECT SUM(books.id)
FROM books
JOIN `lines` as l ON books.lid = l.id
LEFT JOIN lids_sids ON l.id = lids_sids.lid

you'd also get a single value even though there aren't any records in the tables that get joined.

Upvotes: 3

Related Questions