How to perform query with some null values?

Question

The SQL below returns the values correctly only if the field data_emissao be as nonzero in my BD table (independent if the parameter cliente was passed or not), but I wish to return the values where the fields data_emissao are also null (if the parameter cliente has been passed).

How should I use the logical operator OR without failing to obey the condition in case the field data_emissao is different from null?

$sql  = " SELECT * FROM nfe WHERE (cliente LIKE :cliente OR :cliente_ IS NULL)";

$sql. = " AND (data_emissao IS NOT NULL AND data_emissao <= :dt_final 
OR :dt_final_ IS NULL)";

Problem simulation:

------------------------------
-- Tabela `nfe`
------------------------------

1 | Aarco A | NULL
2 | Barco B | 13-01-2017
3 | Carco C | 14-01-2017
4 | Darco D | 14-01-2017
5 | Earco E | 15-01-2017
6 | Farco F | NULL
7 | Garco G | 16-01-2017

SQL:

SELECT * FROM nfe WHERE (cliente LIKE '%Aarco%')
AND (data_emissao IS NOT NULL AND data_emissao <= '15-01-2017')

Result obtained:

NULL | NULL | NULL  

Expected result:

1 | Aarco A | NULL

Here’s how the table was created:

-- ----------------------------
-- Table structure for nfe
-- ----------------------------
DROP TABLE IF EXISTS `nfe`;
CREATE TABLE `nfe` (
  `id` int(9) NOT NULL AUTO_INCREMENT,
  `cliente` varchar(255) DEFAULT NULL,
  `data_emissao` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of nfe_teste
-- ----------------------------
INSERT INTO `nfe` VALUES ('1', 'Aarco A', null);
INSERT INTO `nfe` VALUES ('2', 'Barco B', '13-01-2017');
INSERT INTO `nfe` VALUES ('3', 'Carco C', '14-01-2017');
INSERT INTO `nfe` VALUES ('4', 'Darco D', '14-01-2017');
INSERT INTO `nfe` VALUES ('5', 'Earco E', '15-01-2017');
INSERT INTO `nfe` VALUES ('6', 'Farco F', null);
INSERT INTO `nfe` VALUES ('7', 'Garco G', '16-01-2017');

SQL Fiddle from BD: sqlfiddle.com/#! 9/790cb5/1

Answer 1

The SQL below returns the values correctly only if the field data_emissao be as nonzero in my BD table [independent if the parameter cliente was passed or not], but I want to return the values where the fields data_emissao are also null [if the parameter cliente has been passed].

That is to say:

If the field data_emissao is not null OR where the countryside data_emissao is void And the cliente has been passed.

And therefore:

data_emissao IS NOT NULL OR (data_emissao IS NULL AND cliente IS NOT NULL)

This can be simplified as:

data_emissao IS NOT NULL OR cliente IS NOT NULL

One proof of this is if we look at the cases you have:

• data_emissao is void and cliente is null. - Should not come in the result.
• data_emissao is void and cliente is not null. - Must come in result.
• data_emissao is not void and cliente is null. - Must come in.
• data_emissao is not void and cliente is not null. - It must come in.

The only case that doesn’t matter is when the two are null and void.

However, there are two other restrictions as well:

1. The non-null dates that matter are those that are less than the informed date (which may be null).

2. The non-null customers that matter are those who are in accordance with the LIKE, if this has been informed.

Rule number 1 (which brings the non-null issuance dates that matter) looks like this:

data_emissao IS NOT NULL AND (data_emissao <= :data_final OR :data_final_ IS NULL)

Already rule number 2 (which brings non-null accounts that matter) looks like this:

cliente IS NOT NULL AND (cliente LIKE :cliente OR :cliente_ IS NULL)

And so your SQL looks like this:

SELECT * FROM nfe
WHERE (data_emissao IS NOT NULL AND (data_emissao <= :data_final OR :data_final_ IS NULL))
OR (cliente IS NOT NULL AND (cliente LIKE :cliente OR :cliente_ IS NULL))

Answer 2

Just use UNION:

UNION is used to combine the result from Multiple SELECT statements into a single result set. Taken from the Manual (link)

This command combines two resultsets into one, but does not perform Distinct, i.e., if there are repeated lines, they will appear.

I changed your dataset to include two records for the same account, only one contains the date and the other does not.

-- ----------------------------
-- Table structure for nfe
-- ----------------------------
DROP TABLE IF EXISTS `nfe`;
CREATE TABLE `nfe` (
  `id` int(9) NOT NULL AUTO_INCREMENT,
  `cliente` varchar(255) DEFAULT NULL,
  `data_emissao` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of nfe_teste
-- ----------------------------
INSERT INTO `nfe` VALUES ('1', 'Aarco A', null);
INSERT INTO `nfe` VALUES ('2', 'Barco B', '13-01-2017');
INSERT INTO `nfe` VALUES ('3', 'Carco C', '14-01-2017');
INSERT INTO `nfe` VALUES ('4', 'Darco D', '14-01-2017');
INSERT INTO `nfe` VALUES ('5', 'Earco E', '15-01-2017');
INSERT INTO `nfe` VALUES ('6', 'Farco F', null);
INSERT INTO `nfe` VALUES ('7', 'Garco G', '16-01-2017');
INSERT INTO `nfe` VALUES ('8', 'Aarco A', '13-01-2017');

The final query:

SELECT * FROM nfe WHERE (cliente LIKE '%Aarco%')
AND (data_emissao IS NOT NULL AND data_emissao <= '15-01-2017')
UNION 
SELECT * FROM nfe WHERE (cliente LIKE '%Aarco%')
AND (data_emissao IS NULL)

The result:

8 | Aarco A | 13-01-2017
1 | Aarco A | NULL

To suit further, you can test the client name parameter before mounting the SQL statement for execution and run only the specified SQL. Something like:

if(cliente != null)
{
   .sql = "SELECT * FROM nfe WHERE (cliente LIKE '%Aarco%')
AND (data_emissao IS NOT NULL AND data_emissao <= '15-01-2017')
UNION 
SELECT * FROM nfe WHERE (cliente LIKE '%Aarco%')
AND (data_emissao IS NULL)";
}
else
{
   .sql = "SELECT * FROM nfe WHERE (data_emissao IS NOT NULL AND data_emissao <= '15-01-2017')
UNION 
SELECT * FROM nfe WHERE (data_emissao IS NULL)";
}

Edit: I only saw after you ask to use OR, so sorry there. If you want I withdraw.

Answer 3

I changed the name of the columns to test in my environment, the query below returned what you need:

SELECT * FROM #usuario WHERE (nome LIKE '%Aarco%')AND (data IS NULL or data <= '01-15-2017')

Answer 4

The most direct and obvious way I could think of to your problem was by using a IF. If data_emissao is not void and data_emissao is less than the date provided or data_emissao is void.

SELECT 
  *
FROM
  nfe
WHERE
  cliente
  LIKE '%Aarco%'
  AND IF(data_emissao<>NULL, (data_emissao<='15-01-2017'), TRUE)

In case, as you want with an OR, we can use

SELECT 
  *
FROM
  nfe
WHERE
  cliente
  LIKE '%Aarco%'
  AND ((data_emissao IS NOT NULL AND data_emissao<='15-01-2017') OR data_emissao IS NULL)