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*/
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*/
You can filter out rows that contain a NULL value in a specific column:
SELECT 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:
SELECT 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?
SELECT * 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.
Need some knowledge on NULL and NOT NULL
SQL Newbie question about NOT NULL
How to check for Is not Null And Is not Empty string in SQL server? - Stack Overflow
What are pros and cons of using NULLS vs NOTNULLs in database?
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
Hi! Me and my sibling-in-law are just beggining to learn SQL and are about to get in a boot camp that gives you an introductory "exam". We failed it the first time, but weren't told why. This Exam willl change, so we're not looking to have our homework done so to say, we just want to understand what we did wrong in the first try.
And after watching a lot of videos and trying different solutions, we're a bit confused about this schema:
What we can't get a grasp on is what's the use of NOT NULL here? Like, how should we add that to our querys?
We're also a bit lost when it comes to item 10, how should we use "join" here?
Thank you in advance, we're doing our best!
I'll translate all the questions so that there's some context:
The first point was:
"Write an SQL query to show all the products in the table "Productos" with a price higher to $50."
Our answer was:
Select * from productos where Price > 50
Second point was:
"Write an SQL query to obtain the total amount of orders (pedidos) made by an specific client according to his ID"
Our answer was:
Select cliente_ID, count(*) as Pedidos_count
from Pedidos
where cliente_ID= ‘NOT NULL’
group by cliente_ID
Third point was:
"Write an SQL query to update the price of a product on the table "Productos""
Our answer was:
Update productos set price = ‘Float’
where nombre = ‘Varchar’
Fourth point was:
"Write an SQL query to show the names of the products together with their corresponding categories."
Our answer was:
Select nombre_varchar, categoria_varchar from productos
Fifth point was:
"Write an SQL query to delete all the orders that have an amount lesser than 5."
Our answer was:
Delete from pedidos where quantity < 5
Sixth point was:
"Write an SQL query to calculate the total price of the orders made."
Our answer was:
Select SUM (total_precio) as "total_pedidos_precio"
From Pedidos
Seventh point was:
"Write an SQL query to show the names of the products in ascendant alphabetical order."
Our answer was:
select * from productos
Order by nombre asc
Eighth point was:
"Write an SQL query to show the orders made in a specific date." (fecha means date).
Our answer was:
select * from Pedidos where date (fecha_pedido) = NOT NULL
Ninth point was:
"Write an SQL query to obtain the average of the prices of all the products."
Our answer was:
Select AVG (precio) from Productos
Tenth point was:
"Write an SQL query to show the products together with the total amount of orders made for each one."
We weren't sure about this one, we think we have to use the join clause, but we couldn't agree on how to.
Eleventh point was:
"What's the correct syntax to insert a new record in the table "Usuarios" (Users)"
a) INSERT INTO Usuarios (Nombre, Apellido) VALUES ('John', 'Doe'); (Picked this one)
b) INSERT Usuarios (Nombre, Apellido) VALUES ('John', 'Doe');
c) INSERT VALUES ('John', 'Doe') INTO Usuarios;
d) INSERT INTO Usuarios VALUES ('John', 'Doe');
Twelfth point was:
"What's the function used to obtain the total amount of records in a table?"
a) COUNT() (Picked this one)
b) SUM()
c) AVG()
d) MAX()
Thirteenth point was:
"What's the clause used to filter results in a SELECT query?"
a) WHERE (Picked this one)
b) FROM
c) ORDER BY
d) GROUP BY
Fourteenth point was:
"What's the operator used to combine conditions in a WHERE clause?"
a) OR
b) AND (Picked this one)
c) NOT
d) XOR
Fifteenth point was:
"What's the SQL query to delete an existing table?"
a) DELETE TABLE name_table; (Picked this one)
b) DROP name_table;
c) REMOVE name_table;
d) ERASE name_table;
If you only want to match "" as an empty string
WHERE DATALENGTH(COLUMN) > 0
If you want to count any string consisting entirely of spaces as empty
WHERE COLUMN <> ''
Both of these will not return NULL values when used in a WHERE clause. As NULL will evaluate as UNKNOWN for these rather than TRUE.
CREATE TABLE T
(
C VARCHAR(10)
);
INSERT INTO T
VALUES ('A'),
(''),
(' '),
(NULL);
SELECT *
FROM T
WHERE C <> ''
Returns just the single row A. I.e. The rows with NULL or an empty string or a string consisting entirely of spaces are all excluded by this query.
SQL Fiddle
WHERE NULLIF(your_column, '') IS NOT NULL
Nowadays (4.5 years on), to make it easier for a human to read, I would just use
WHERE your_column <> ''
While there is a temptation to make the null check explicit...
WHERE your_column <> ''
AND your_column IS NOT NULL
...as @Martin Smith demonstrates in the accepted answer, it doesn't really add anything (and I personally shun SQL nulls entirely nowadays, so it wouldn't apply to me anyway!).