MySQL Select Only Not Null Values

MySQL SELECT only not null values

You should use IS NOT NULL. (The comparison operators = and <> both give UNKNOWN with NULL on either side of the expression.)

SELECT * 
FROM table 
WHERE YourColumn IS NOT NULL;

Just for completeness I'll mention that in MySQL you can also negate the null safe equality operator but this is not standard SQL.

SELECT *
FROM table 
WHERE NOT (YourColumn <=> NULL);

Edited to reflect comments. It sounds like your table may not be in first normal form in which case changing the structure may make your task easier. A couple of other ways of doing it though...

SELECT val1 AS val
FROM  your_table
WHERE val1 IS NOT NULL
UNION ALL
SELECT val2 
FROM  your_table
WHERE val2 IS NOT NULL
/*And so on for all your columns*/

The disadvantage of the above is that it scans the table multiple times once for each column. That may possibly be avoided by the below but I haven't tested this in MySQL.

SELECT CASE idx
         WHEN 1 THEN val1
         WHEN 2 THEN val2
       END AS val
FROM   your_table
        /*CROSS JOIN*/
       JOIN (SELECT 1 AS idx
                   UNION ALL
                   SELECT 2) t
HAVING val IS NOT NULL  /*Can reference alias in Having in MySQL*/

Select only data whos not null

where col_1 = 'value' or col_2 = ''

As it is, this OR expression allows null values in col2, as long as col_1 is equal to 'value'.

You want :

where col_2 is not null and (col_1 = 'value' or col_2 = '')

MySQL Select Column if Not Null only

Certainly you can't do that; rather you can use COALESCE() or IFNULL() function to provide a default value in case of NULL like

SELECT firstname, 
COALESCE(lastname,'N/A'),
age,
gender from persons WHERE id = 1;

(OR) if you want to remove that record completely then use a WHERE condition like

SELECT firstname, lastname, age, gender from persons 
WHERE id = 1 AND lastname IS NOT NULL;

how to select rows with no null values (in any column) in SQL?

You need to explicitly list each column. I would recommend:

select t.*
from t
where col1 is not null and col2 is not null and . . . 

Some people might prefer a more concise (but slower) method such as:

where concat(col1, col2, col3, . . . ) is not null

This is not actually a simple way to express this, although you can construct the query using metadata table or a spreadsheet.

How to select only columns with not null values sql

A SQL query is fixed in the columns that are returned. So, you cannot do what you want with a simple select query. You could use dynamic SQL.

However, you can only return the values that you want as a single string:

select name, concat_ws(',', column1, column2, column3, column4, column5, column6)
from t
where . . . ;

That said, the real issue is probably your data structure. in general, you do not want to store such column values in a pivoted form. Instead, include a single value per row. In other words, you want a junction/association table with one row per value and per name.

Then the query would be:

select name, group_concat(value)
from association_table 
group by name;

Select only not null column values and display only that values using sql query

You are looking for coalesce(), if you want the first non-NULL value:

select t.*, coalesce(col1, col2, col3, col4) as col5
from t;

Select for every column the first value that is NOT NULL

Tested on MySQL 8.0.29:

select
  first_value(col1) over (order by case when col1 is null then 1 else 0 end, id) as col1,
  first_value(col2) over (order by case when col2 is null then 1 else 0 end, id) as col2,
  first_value(col3) over (order by case when col3 is null then 1 else 0 end, id) as col3,
  first_value(col4) over (order by case when col4 is null then 1 else 0 end, id) as col4
from mytable
limit 1;

Result:

+------+------+------+------+
| col1 | col2 | col3 | col4 |
+------+------+------+------+
| bar  | foo  | NULL | baz  |
+------+------+------+------+

MySQL - Select row only if at least one of values not null or empty

You were pretty close, just need to use and in some places instead of or:

SELECT * 
FROM table 
WHERE datestamp>='$startdate' 
    AND datestamp<='$finishdate' 
    AND ((h1col !='' AND h1col !='0') OR 
         (h2col !='' AND h2col !='0') OR 
         (h3col !='' AND h3col !='0'))

MYSQL select sum by months including with null values

You don't need the GROUP BY clause in the subquery. Your query should be:

SELECT 
    SUM(IFNULL(t1.sub_total, 0)) AS amount,
    am.a_month AS date
FROM 
    (SELECT 
         IFNULL(vn.sub_total, 0) AS sub_total,
         CAST(DATE_FORMAT(order_date, '%M') AS char) AS mdate
     FROM 
         orders_new vn
     WHERE 
         order_status = 1
         AND order_date BETWEEN '2022-01-01' AND '2022-12-31') t1 
RIGHT OUTER JOIN 
    all_months am ON t1.mdate = am.a_month
GROUP BY 
    am.a_month
ORDER BY 
    a_month_id ASC;

Sql select with Null and Not Null values

I think we can actually simplify this by using ROW_NUMBER:

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY spec_id ORDER BY language) rn
    FROM temp_value t
)

SELECT language, value, value_id, spec_id, language_id, nac_id
FROM cte
WHERE rn = 1;

This should work because Oracle by default sorts NULL last. This means that, for each group of spec_id records, the non NULL language value would float to the top, if it exists. Only if a given spec_id group have no non NULL language records would a NULL record be selected.

Edit:

To cater to the problem where there might be two or more non NULL language records, with the previous logic for retaining a NULL only should there be no non NULL records, we can try:

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY spec_id ORDER BY language) rn
    FROM temp_value t
)

SELECT language, value, value_id, spec_id, language_id, nac_id
FROM cte
WHERE language IS NOT NULL OR (rn = 1 AND language IS NULL);

---

Related Topics