How to perform query with some null values?

  

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

That is:

  

If the data_emissao field is not null OU where the data_emissao field is null E 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

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

• data_emissao is null and cliente is null. - It should not come in the result.
• data_emissao is null and cliente is not null. - It should come in the result.
• data_emissao is not null and cliente is null. - It should come in the result.
• data_emissao is not null and cliente is not null. - It should come in the result.

The only case that does not matter is when the two are null.

However, there are two more restrictions too:

The rule number 1 (which brings up the non-null emission dates that matter) looks like this:

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

Now rule number 2 (which brings the non-null clients 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))

Just use UNION:

  

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

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

I changed your dataset to include two records for the same client, 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 further qualify, you can test the client name parameter before assembling the SQL statement to execute and execute only the indicated 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 asked to use OR, so sorry. If you want I'll take it back.

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

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

The most direct and obvious way I could think of your problem was by using IF . If data_emissao is not null and data_emissao is less than the given date or if data_emissao is null.

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)