List records that are in a table and those that are not in the same table

I will assume that a user who has entry01 or entry02 on the selected date has chopped the point.

DECLARE @DATA DATE;
SET @DATA = '2016-05-09';



select
    nome,
    CASE WHEN Picou > 0 then 'Sim' else 'Não' end 'Picou'
from (
    select
        u.nome,
        SUM(CASE WHEN DATE(d.entrada01) = @DATA OR
        DATE(d.entrada02) THEN 1 ELSE 0 END) 'Picou'
    from user u
    left join dados d on
        u.id = d.usuarioid
    group by
        u.nome
) t

EDIT:

DECLARE @DATA DATE;
SET @DATA = '2016-05-09';

select
    u.nome,
    d.*
from user u
left join (
    select
        d.*
    from dados d on
    where
        DATE(d.entrada01) = @DATA OR
        DATE(d.entrada02) = @DATA
) d on
    u.id = d.usuarioid

In the Caffé solution, we only needed to include a clause in where data is null:

    select 
    user.id, user.nome, ponto.data, ponto.entrada01, 
    ponto.saida01, ponto.entrada02, ponto.saida02
    (case when ponto.data is not null then 'sim' else 'não') as bateu_ponto
from user left join ponto on ponto.usuarioId = user.id
where (data is null or (data between data1 and data2))
group by user.id, user.nome, ponto.data, ponto.entrada01, 
ponto.saida01, ponto.entrada02, ponto.saida02, bateu_ponto 

Users who did not hit the dot do not have the date, so you should also consider the date to be null or in the range that you tell point users.

================ Correct solution =================================== p>

The above solution does not answer. If the user has a date point different from the one in the where, that is, it does not meet the where clauses since it is not null and is different from the filter date.

The correct solution was to create a sub query only with the points of the day that you want to consult, with this table I made a left join with user, now all users are being returned even if there are points for other dates. Here is the new query:

select user.id, user.name, ponto.dataPonto, 
       ponto.entrada01, ponto.saida01, 
       ponto.entrada02, ponto.saida02,
    case when (ponto.dataPonto is not null) then 'sim' else 'não' end as bateu_ponto

from user left join

     (select * 

      from pontospordia p 

      where p.dataPonto between '2016-05-07' and '2016-05-10 23:59:59') ponto on ponto.usuarioId = user.id
group by user.id, 
         user.name, 
         ponto.dataPonto, 
         ponto.entrada01, 
         ponto.saida01, 
         ponto.entrada02, 
         ponto.saida02 

I hope I have helped.

This works:

select 
    user.id, user.name, ponto.dataPonto, ponto.entrada01, 
    ponto.saida01, ponto.entrada02, ponto.saida02,
    case when ponto.dataPonto is not null then 'sim' else 'não' end bateu_ponto
from 
    user left join pontospordia ponto on ponto.usuarioId = user.id
where 
    (dataPonto between '2016-05-07' and '2016-05-10')
    or dataPonto is null
group by 
    user.id, user.name, ponto.dataPonto, ponto.entrada01, 
    ponto.saida01, ponto.entrada02, ponto.saida02, bateu_ponto

Above is my original query plus the OR of @Developer.

See working in your fiddle sql: link .

The only thing is that searching for more than one date and displaying the date of the query does not help much to know when a user has not hit a point, because if he did not hit it, he will not have a corresponding record in the hit table, so it will not show the date to it.

The solution is to query for a single date and display in the application the date used for the query instead of trying to get this date from the query result.