What is the difference between these 3 queries? WHERE & HAVING - mysql version? -

what difference between 3 queries have here?

first version works on localhost xampp mysql database (10.1.9-mariadb). retrieves 0 results online test server mysql database (5.7.9-log mysql community server gpl).

2nd version retrieves expected results both servers. on localhost query 2 retrieves same result set albeit in different order, i'm guessing that's because don't have order by.

issue different version of mysql? , how, if @ queries different?

query 1

select      s.eid, e.fname, e.lname, s.id, s.starttime, s.finishtime     employees e         right join     scheduling_shifts s using (eid) group s.id having count(s.date = '2016-01-15' or null) > 0     , count(s.eid = '1' or null) = 0; 

query2

select      s.eid, e.fname, e.lname, s.id, s.starttime, s.finishtime     employees e         right join     scheduling_shifts s using (eid)     s.date = '2016-01-15' group s.id having count(s.eid = '1' or null) = 0; 

edit:: query3

this query retrieves correct result set minus null s.eid's

    select      s.eid, e.fname, e.lname, s.id, s.starttime, s.finishtime     employees e         right join     scheduling_shifts s using (eid)     s.date = '2016-01-15' , s.eid <> '1' 

edit:: here test case specific own query on sqlfiddle

the objective fetch list of possible swaps selected employee on selected date. our selected date 15th jan 2016, , selected employee want swap shift employee 1. list cannot fetch own shift, because cannot swap shifts himself. should bring unassigned shifts. (shifts exist eid null). option bring list of other employees , shifts, or unassigned shifts on day can swap ours with.

notice query 1 - does produce correct results on this, did produce correct result on xampp (mariadb 10), didn't on online test server.

query 2 fetches correct result, , did both xampp , online test server.

query 3 fetches shifts assigned, not unassigned ones.

hope explains question bit better.

the where clause considers conditions per row, having clause considers aggregations, not believe cause of differences.

your use of "or null" misleading , @ point (if not now) produce unexpected or incorrect results, , please note count() only increments when expression non-null.

below, using mysql 5.6 @ sql fiddle, attempt display effect:

create table table1     (`id` int, `other` varchar(4)) ;  insert table1     (`id`, `other`) values     (1, 'abc'),     (2, 'def'),     (3, null) ; 

query 1:

select count(`other` > '0' or null) table1 

results:

| count(other > '0' or null) | |----------------------------| |                          2 | 

query 2:

select count(`other` > '0' or `other` null) table1 

results:

| count(`other` > '0' or `other` null) | |-----------------------------------------| |                                       3 | 

in query1 above can see "or null" not being counted in query2 expression returns true rows hence rows counted.

"or null" constant null , count() not increment nulls

[edit] suggest use count(*) appears want count every row, that's count(*) does not access every column, cares 'does row exist'.

[edit 2] way...

comparison of null via <> (not equal) not work. null cannot equal anything, impossible know if not equal (because must know if is equal before is not equal)

look in articular @ columns y & z in following result:

   select           count(other > '0' or null) orig         , count(`other` > '0' or `other` null) x         , count(case when `other` <> 'abc' null else 1 end) y         , count(case when `other` <> 'abc' or `other` null null else 1 end) z     table1 

results:

| orig | x | y | z | |------|---|---|---| |    2 | 3 | 2 | 1 | 

---

as sqlfiddle test case available, how structure query. lists 8 of 9 rows in provided table scheduling_shifts:

select          s.eid, e.fname, e.lname, s.id, s.date, s.starttime, s.finishtime scheduling_shifts s       left join employees e on s.eid = e.eid (s.eid <> 1 or s.eid null)       , s.date = '2016-01-15'; 

as cannot reproduce specific issue (different results different platforms) regret use of "or null" strikes me being unusual , produce unpredictable results. secondary feature of queries using having clause when there no particular reason so, avoid.