SQL how to deal with no values

Question

When I run the query below

SELECT 
    COUNT(COALESCE(CASE PARTPPHY.TITRE 
                      WHEN 1 THEN 'Monsieur ' 
                      WHEN 2 THEN 'Madame ' 
                      WHEN 3 THEN 'Madame ' 
                      ELSE '' 
                    END + PARTPPHY.IDENTITE, PARTPPHY.IDENTITE)) AS ident,
    RESPONSABLES.DIR_LOC,
    PARTPPHY.IU_PART_PP,
    COALESCE(CASE PARTPPHY.TITRE 
                WHEN 1 THEN 'Monsieur ' 
                WHEN 2 THEN 'Madame ' 
                WHEN 3 THEN 'Madame ' 
                ELSE ''  
             END + PARTPPHY.IDENTITE,PARTPPHY.IDENTITE) AS IDENTITE,
    FONCTIONS_ECO.LIBEL AS LIBEL_FONCTION
FROM
    RESPONSABLES
INNER JOIN 
    ETABLISSEMENTS ON ETABLISSEMENTS.IU_ETS = RESPONSABLES.IU_ETS
LEFT OUTER JOIN 
    PARTPPHY ON RESPONSABLES.IU_PART_PP = PARTPPHY.IU_PART_PP
LEFT OUTER JOIN 
    FONCTIONS_ECO ON FONCTIONS_ECO.IU_FONC_ECO = RESPONSABLES.IU_FONC
WHERE 
    ETABLISSEMENTS.IU_ETS = 14783
    AND RESPONSABLES.GESTDEL = 1
GROUP BY 
    RESPONSABLES.DIR_LOC,
    PARTPPHY.IU_PART_PP,
    COALESCE(CASE PARTPPHY.TITRE WHEN 1 THEN 'Monsieur ' WHEN 2 THEN 'Madame ' WHEN 3 THEN 'Madame ' ELSE '' END + PARTPPHY.IDENTITE,PARTPPHY.IDENTITE),
    FONCTIONS_ECO.LIBEL

This is my result

Yet when I'm using this query

SELECT COUNT(COALESCE(CASE PARTPPHY.TITRE 
WHEN 1 THEN 'Monsieur ' 
WHEN 2 THEN 'Madame ' 
WHEN 3 THEN 'Madame ' 
ELSE '' END + PARTPPHY.IDENTITE,PARTPPHY.IDENTITE)) AS ident,
       RESPONSABLES.DIR_LOC,
       PARTPPHY.IU_PART_PP,
       COALESCE(CASE PARTPPHY.TITRE WHEN 1 THEN 'Monsieur ' WHEN 2 THEN 'Madame ' WHEN 3 THEN 'Madame ' ELSE '' END + PARTPPHY.IDENTITE,PARTPPHY.IDENTITE) AS IDENTITE,
       FONCTIONS_ECO.LIBEL AS LIBEL_FONCTION
FROM RESPONSABLES
INNER JOIN ETABLISSEMENTS ON ETABLISSEMENTS.IU_ETS = RESPONSABLES.IU_ETS
LEFT OUTER JOIN PARTPPHY ON RESPONSABLES.IU_PART_PP = PARTPPHY.IU_PART_PP
LEFT OUTER JOIN FONCTIONS_ECO ON FONCTIONS_ECO.IU_FONC_ECO = RESPONSABLES.IU_FONC
WHERE ETABLISSEMENTS.IU_ETS = 1
  AND RESPONSABLES.GESTDEL = 1
GROUP BY RESPONSABLES.DIR_LOC,
         PARTPPHY.IU_PART_PP,
         COALESCE(CASE PARTPPHY.TITRE WHEN 1 THEN 'Monsieur ' WHEN 2 THEN 'Madame ' WHEN 3 THEN 'Madame ' ELSE '' END + PARTPPHY.IDENTITE,PARTPPHY.IDENTITE),
         FONCTIONS_ECO.LIBEL

I have this

My questions are the following:

• It does not seem to be a null nor an empty space: What is it?
• I want to have a value in my count: What should I do to have this?

Thanks

Answer 1

There is one more valid option. In both cases you are using the INNER JOIN, which is exclusive join meaning, if the join condition is not satisfied then no data is return, or more precise your result is an empty set.

Your INNER JOIN condition is on ETABLISSEMENTS.IU_ETS = RESPONSABLES.IU_ETS

In the first query your where statement says

WHERE ETABLISSEMENTS.IU_ETS = 14783 AND RESPONSABLES.GESTDEL = 1

This is where you have an empty data set

And in the second query your where statement is

WHERE ETABLISSEMENTS.IU_ETS = 1 AND RESPONSABLES.GESTDEL = 1

So to answer your first query: Possibly there is no matching record, which has the ETABLISSEMENTS.IU_ETS = 14783 AND RESPONSABLES.GESTDEL = 1 therefore we are getting an empty set

In the second case the where statement is satisfied and there is a corresponding entry in both tables therefore we have a result.

Depending which side of the Join you are interested in, i.e. in which table yo will expect the row to be present you can switch the

JOIN to OUTER JOIN (LEFT or RIGHT)

The outer join is inclusive, meaning that it will return all rows from the table left or right to the join even if there is no record in corresponding table. Then apply count on the corresponding table, i.e. id column. If there is no record then you will get 0

Example to illustrate

CREATE TABLE #Left
(
 id INT
,col1 CHAR(3)
)

CREATE TABLE #Right 
(
 id INT 
,col2 CHAR(3)
)


INSERT INTO #Right 
( id, col2) 
VALUES 
(1,'c1')
,(2,'c2')
,(3,'c4')
,(4,'c5')

INSERT INTO #Left 
( id, col1) 
VALUES 
(1,'c1')
,(2,'c2')
,(3,'c3')
,(4,'c5')

-- empty set 
SELECT 
*
FROM #Left L 
 INNER JOIN #Right R 
  ON L.col1 = R.col2 
WHERE col1 = 'c3'

-- matching rows
SELECT 
*
FROM #Left L 
 INNER JOIN #Right R 
  ON L.col1 = R.col2 
WHERE col1 = 'c2'

-- Left Join 
SELECT 
L.*
,COUNT(R.col2) 
FROM #Left L 
 LEFT JOIN #Right R 
  ON L.col1 = R.col2 
WHERE col1 = 'c3'
GROUP BY L.id, L.col1

Answer 2

While using a COUNT without a GROUP BY clause you will always return a row, even if the WHERE clause is not equal to any of the records in your table. But while using a GROUP BY clause , it will return the records only if the WHERE clause condition is true.

Please check the following test scripts

DECLARE @TEST TABLE(ID INT)
INSERT INTO @TEST VALUES(10)

--1.
SELECT COUNT(*) FROM @TEST WHERE ID = 11    --Result is 0
--2.
SELECT COUNT(*),ID FROM @TEST WHERE ID = 11 GROUP BY ID --No Result

Here the first query always return a result

(No column name)
0

But second query not.

You can modify your second query something like the below script to get a '0' result

SELECT ISNULL(( SELECT COUNT(*) FROM @TEST WHERE ID = 11 GROUP BY ID ),0)