1
I have a problem in doing a query where I have 3 fields and just one of them is filled that no longer shows this result.
Someone can help me do that?
select * from tabelas where campo1 and campo2 and campo3
1
I have a problem in doing a query where I have 3 fields and just one of them is filled that no longer shows this result.
Someone can help me do that?
select * from tabelas where campo1 and campo2 and campo3
3
If you want everyone to be shown unless one is filled do so:
SELECT *
FROM tabelas
WHERE campo1 IS NULL
AND campo2 IS NULL
AND campo3 IS NULL
If you want where all are filled use IS NOT NULL
:
SELECT *
FROM tabelas
WHERE campo1 IS NOT NULL
AND campo2 IS NOT NULL
AND campo3 IS NOT NULL
1
You can use the function COALESCE
to identify whether all fields are null (combined with is null
), or if any of them are filled (is not null
):
select * from tabelas where coalesce(campo1, campo2, campo3) is null;
select * from tabelas where coalesce(campo1, campo2, campo3) is not null;
Is there any performance gain, comparing to Andrey’s response?
I found nothing about it, but it depends on the implementation of COALESCE
in the database used. In SQL Server, for example, the COALESCE
is converted into case when
internally, so there may be gain depending on the data model. For example, if the campo1
has an index and the other fields do not, in cases where campo1
is filled in the SQL Server engine and would not need to check the other fields, since the case
would be interrupted in the first field.
Browser other questions tagged sql query
You are not signed in. Login or sign up in order to post.
And php? could put the true text.
– rray
In part
where campo1 and campo2 and campo3
you have to put some comparison, for example,campo1 == 10
, if in fact you just wanted to select these fields, then they enter theselect
and not in thewhere
. For example,select campo1, campo2, campo3 from tabelas
.– Carlos Cinelli
You mean "just one of them is NOT filled" right? Missed to say what is the result you want.
– Bacco
As Carlos said, you are probably confusing the fields... try this way:
select campo1, campo2, campo3 from tabelas
– Leonardo Bosquett