Menu
I have a table with records that have blank/null data in certain columns and I want to find records where ALL columns have a value other than blank/null without creating a really long SQL statement.
EG:
SELECT * FROM table
WHERE col1 IS NOT NULL AND col2 IS NOT NULL AND col3 IS NOT NULL AND...
Is there any way to shorten this? Or is there any way to do this differently (with an SQL procedure maybe?)
354
Below is the syntax to filter the rows without a null value in a specified column. Syntax: SELECT * FROM <table_name> WHERE <column_name> IS NOT NULL; Example: SELECT * FROM demo_orders WHERE ORDER_DATE IS NOT NULL; --Will output the rows consisting of non null order_date values.
To exclude the null values from the table we need to use IS NOT NULL operator with the WHERE clause. WHERE Clause: The WHERE clause is used to filter the records. It will extract those records that fulfill the condition.
The only thing I would do to shorten it would be to do something like:
SELECT * FROM table1 WHERE (val1 AND val2 AND val3 AND val4) IS NOT NULL
128
If you sometimes want to look only at the rows that contains data in all columns I would suggest creating a view based on the query you posted above. That way you can interact with it in a more elegant and shorter way.
A view is sort of a "virtual table" that is based off a query. If you regularly want to do some kind of complex joining or filtering then using a view can greatly simplify the queries you need to write elsewhere.
31
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
