You should use IS NOT NULL. (The comparison operators = and <> both give UNKNOWN with NULL on either side of the expression.)
CopySELECT *
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.
CopySELECT *
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...
CopySELECT 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.
CopySELECT 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*/
Answer from Martin Smith on Stack OverflowNeed some knowledge on NULL and NOT NULL
sql - MySQL SELECT only not null values - Stack Overflow
if statement - MySQL IF NOT NULL, then display 1, else display 0 - Stack Overflow
how works "not in" with nulls value?
Videos
-
Where and why exactly a null is used?
-
What is exactly null and not null? To my understanding Not null we use when its mandatory to insert some value in that field, also when we give check constraint so by default the column will be not null right?
-
By adding new column through alter method default values are null, so how would I be able to insert values in it and is it right to give not null constraint to that new column while adding through alter method, basically when null and when not null to be used?...
god this is so confusing please help me, ik im asking alot but im really confused
You should use IS NOT NULL. (The comparison operators = and <> both give UNKNOWN with NULL on either side of the expression.)
CopySELECT *
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.
CopySELECT *
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...
CopySELECT 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.
CopySELECT 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*/
You can filter out rows that contain a NULL value in a specific column:
CopySELECT col1, col2, ..., coln
FROM yourtable
WHERE somecolumn IS NOT NULL
If you want to filter out rows that contain a null in any column then try this:
CopySELECT col1, col2, ..., coln
FROM yourtable
WHERE col1 IS NOT NULL
AND col2 IS NOT NULL
-- ...
AND coln IS NOT NULL
Update: Based on your comments, perhaps you want this?
CopySELECT * FROM
(
SELECT col1 AS col FROM yourtable
UNION
SELECT col2 AS col FROM yourtable
UNION
-- ...
UNION
SELECT coln AS col FROM yourtable
) T1
WHERE col IS NOT NULL
And I agre with Martin that if you need to do this then you should probably change your database design.
Instead of COALESCE(a.addressid,0) AS addressexists, use CASE:
CASE WHEN a.addressid IS NOT NULL
THEN 1
ELSE 0
END AS addressexists
or the simpler:
(a.addressid IS NOT NULL) AS addressexists
This works because TRUE is displayed as 1 in MySQL and FALSE as 0.
SELECT c.name, IF(a.addressid IS NULL,0,1) AS addressexists
FROM customers c
LEFT JOIN addresses a ON c.customerid = a.customerid
WHERE customerid = 123
Here is a more long-winded explanation which takes it step by step. We have:
select * from tab1 where col1 not in (select col2 from tab2)
We can replace the subquery with the values in tab2. This gives us:
select * from tab1 where col1 not in (1, null, 6 ,4, 7)
Let's move the NOT:
select * from tab1 where not (col1 in (1, null, 6 ,4, 7))
IN is just a shortcut for OR, so what we really have is:
select * from tab1 where NOT (col1 = 1 OR col1 = null OR col1 = 6 OR col = 4 OR col1 = 7)
Let's nu study this for some of the values for tab1.col1. We can start with 1. This gives us
NOT (1 = 1 OR ...)
and we can stop there. The list of OR will return TRUE and with NOT in front we get FALSE.
What if we try 5?
NOT (5= 1 OR 5 = null OR 5 = 6 OR 5 = 4 OR 5 = 7)
so that's
NOT (FALSE OR 5 = NULL OR FALSE OR FALSE OR FALSE)
What about 5 = NULL? Well NULL is an unknown value. It could be different from 5, but by chance it could be 5. So the outcome is not TRUE, not FALSE, but UNKNOWN. So we have:
NOT (FALSE OR UNKNOWN OR FALSE OR FALSE OR FALSE)
The result of all the OR is UNKNOWN and with NOT in front, the value is still UNKNOWN by the three-valued logic of SQL. But for WHERE to include a row, the condition for the row must be TRUE, and thus the row with 5 is filtered out as well.
Using NOT EXISTS as Dan showed is a very good solution. Not the least since [NOT] EXISTS is more powerful than IN. However, there is a simpler fix to the original query:
select * from tab1 where col1 not in (select col2 from tab2 WHERE col2 IS NOT NULL)
It's SQL specification or bug?
The unintuitive behavior of NOT IN is part of the SQL standard specification. The SQL Server documentation includes this caution:
Any null values returned by subquery or expression that are compared to test_expression using IN or NOT IN return UNKNOWN. Using null values in together with IN or NOT IN can produce unexpected results.
The gotcha is that UNKNOWN is neither TRUE nor FALSE when NULL values are returned so the NOT IN predicate will never evaluate to either true or false . You may find NOT EXISTS is more intuitive because it will return only TRUE or FALSE.
SELECT *
FROM tab1
WHERE NOT EXISTS (
SELECT 1
FROM tab2
WHERE tab2.col2 = tab1.col1
);