How to apply a where condition on everything I selected?

https://www.codecademy.com/paths/data-science/tracks/sql-intermediate/modules/dspath-sql-multiple-tables/lessons/sql-multiple-tables-code-challenge/exercises/cross-join

I was working on this module and just was wondering.

Hi,
I’m not sure what you’re referring to. Can you please post your code?

The WHERE clause only extracts records with a specific condition.
ex:
WHERE population > 1000000

Sorry, should have explained better. I was wondering in the context of this example:

select * from table_name
where (everything) is not null;

So far I’ve only seen it used if you want to select non null values from a column.

WHERE needs to be applied to a column name to pull out the records(rows).

SELECT * FROM TABLE WHERE address IS NOT NULL;

or,
SELECT * FROM TABLE WHERE address IS NULL;

If you want to remove NULLS from the rows you have to specify the col, or cols I think. 
[https://stackoverflow.com/questions/46362774/remove-all-null-valued-rows-from-table](https://stackoverflow.com/questions/46362774/remove-all-null-valued-rows-from-table)

Is it not enough to just write

select * from table_name
where * is not null;

?
Or where does not work with anything but column names?

WHERE needs a condition. * (ALL) doesn’t meet that requirement. You have to explicitly state the condition on each column.
https://stackoverflow.com/questions/3108262/where-all-is-not-null

Do you have a table that you can try this on? Try it & see what happens.

1 Like

Got an error. Hm. Honestly, I was just trying to find an efficient way of filtering out all null values. Wouldn’t be very practical to write each column name for a table that has like 30 columns.

Sometimes when ppl create tables they specify that the entries cannot be NULL values.
(Look at the schema)

Maybe there’s something that I’m overlooking and it is possible. Let me try it with postgres on my server & get back to you. :slight_smile:

Are you working on a specific CC lesson in a SQL path?

I am working on Multiple Tables in the Data Science Path.

1 Like

I mean, it’s not as easy as it would be in python where you can drop null values…

I thought you just had to select a col or columns.

Did you try:
DELETE FROM your_table WHERE COALESCE (your_column1, your_column2, your_column3 ) IS NULL;

No I didn’t try that, but I mean, I don’t see the need in deleting the null values? I just need to filter them out when selecting stuff from a table. Especially if you need the null values for later queries.
Can I do something with .schema?

select * from table_name where .schema is not null;

You can’t select records from the schema. the schema tells you what data types are allowed in the db. if you want to alter that, that’s something else entirely.

https://www.informit.com/articles/article.aspx?p=1216889&seqNum=2

https://www.w3resource.com/sql/sql-basic/create-schema.php

So, from this conversation, I guess I have to default to copy and paste if I want to get non null values.
ESPECIALLY if I didn’t know where my columns with non null values are at.

I think you mean NULL values(?). (empty, no values).
NON NULL means just that–not empty or with a value.

Are you doing the “Multiple Tables with REBU” project?

I’m going to go ahead and guess that there aren’t NULL values in the table.

Yeah I am.

No, there’s a few. From what I can see in the referred column. But my question again, what if there is more null values that I haven’t seen. I want to remove all of them when I’m selecting (sometimes).

You have to specify the column(s) where you want to filter out the null values.

This topic was automatically closed 41 days after the last reply. New replies are no longer allowed.