opposite of above result required but condition is DISPLAY =YES and REMARK =ITEM

Question

Opposite result required in mysql like in Microsoft SQL using except. Pls do it in mysql

CREATE TABLE `table1` (
  `SERIAL` bigint(20) UNSIGNED NOT NULL,
  `DISPLAY` varchar(20) DEFAULT NULL,
  `REMARK` varchar(20) DEFAULT NULL,
  `A` varchar(20) DEFAULT NULL,
  `B` varchar(20) DEFAULT NULL,
  `C` varchar(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

ALTER TABLE `table1`
  ADD PRIMARY KEY (`SERIAL`),
  ADD UNIQUE KEY `SERIAL` (`SERIAL`);

ALTER TABLE `table1`
  MODIFY `SERIAL` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

INSERT INTO `table1` (`SERIAL`, `DISPLAY`, `REMARK`, `A`, `B`, `C`) VALUES
(1, 'YES', 'ITEM', 'APPLE', 'BATTLE', 'CAT'),
(2, 'YES', 'ITEM', 'APPLE', 'BALLON', 'CATTLE'),
(3, 'YES', 'ITEM', 'AROPLANE', 'BALL', 'CANDLE'),
(4, 'NO', 'ITEM', 'APPLE', 'BALL', 'CAT'),
(5, 'YES', 'COLOR', 'APPLE', 'BALL', 'CAT'),
(6, 'YES', 'ITEM', NULL, NULL, NULL),
(7, 'YES', 'ITEM', 'APPLE', 'BALL', 'CAT');

SELECT SERIAL,DISPLAY, REMARK,A,B,C 
FROM table1 
WHERE DISPLAY="YES" 
  AND REMARK ="ITEM" AND (
    A in('APPLE')
    AND C in('CAT')
    OR  B in('BALL') 
  )

result:

(1, 'YES', 'ITEM', 'APPLE', 'BATTLE', 'CAT')
(3, 'YES', 'ITEM', 'AROPLANE', 'BALL', 'CANDLE'),
(7, 'YES', 'ITEM', 'APPLE', 'BALL', 'CAT');

Now opposite of above result required but condition is DISPLAY =YES and REMARK =ITEM Using minus except

required output in my sql

(2, 'YES', 'ITEM', 'APPLE', 'BALLON', 'CATTLE'),
(6, 'YES', 'ITEM', NULL, NULL, NULL)

Answer 1

Just reverse the logic* in the WHERE-clause

SELECT SERIAL,DISPLAY, REMARK,A,B,C 
FROM table1 
WHERE DISPLAY="YES" 
  AND REMARK ="ITEM" AND NOT((A in('APPLE') AND A IS NOT NULL)
                             AND (C in('CAT') AND C IS NOT NULL)
                              OR  (B in('BALL') AND B IS NOT NULL) )

see: DBFIDDLE

A IN ('APPLE') can also be written as (A IN('APPLE') AND A IS NOT NULL), because the NULL values will be filtered out. So when reversing this NULL values should become present in the output that's why the piece like: A IN ('APPLE') is replaced by (A IN ('APPLE') AND A IS NOT NULL) .

More info on Reversing boolean logic, or algebra, can be found here: Boolean algebra

But when your have A OR B and you want to reverse it, the following steps apply:

The easiest way is to use NOT() around the complete expression:

• NOT(A OR B)

But this can be simplified/changed, to NOT(A) AND NOT(B). How to do this can be found on many pages on the internet.

Another way to get the same result is:

SELECT SERIAL,DISPLAY, REMARK,A,B,C 
FROM table1 
WHERE DISPLAY="YES" 
  AND REMARK ="ITEM" AND ( A NOT in('APPLE') OR A IS NULL
                             OR C NOT in('CAT') OR C IS NULL)
                              AND (B NOT in('BALL') OR B IS NULL) 

see final SQL here: DBFIDDLE